Вырезка и дополнение статьи: Finding Queries that Cause Wait Stats in SQL Server
Для наших поисков нам понадобится изучить способ получения информации о накопившихся ожиданиях вашего SQL Server, который описан здесь.
К сожалению, нет лёгкого ответа на вопрос «Какие именно запросы вызывают те или иные ожидания», но мы можем собирать информацию по частям.
Поиск причин ожиданий (waits) на вашем SQL Server
Ожидания сессий
Кроме описанного выше способа получения общего списка ожиданий вашего SQL Server, нам может понадобится информация о последних ожиданиях сессии. В этом нам поможет поле lastwaittype из SYSPROCESSES:
SELECT * FROM SYSPROCESSES
Так же мы можем очистить представление sys.dm_os_wait_stats и собрать данные за нужный нам промежуток времени, начиная с момента чистки. Очистить sys.dm_os_wait_stats можно с помощью следующего кода:
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
В SQL Server 2016 появилось новое, полезное представление sys.dm_exec_session_wait_stats. Оно содержит суммарную информацию по всем ожиданиям сессии. Обратите внимание, что при отключении сессии информации обнуляется.
Extended Events
Расширенные события позволят исследовать ожидания всех запросов, а не их части, что значительно облегчит наш поиск, но у них есть 2 особенности:
- У Расширенных событий нет графического интерфейса до SQL Server 2012, необходимо будет освоить управление с помощью t-sql команд
- На высоконагруженной системе файл вывода может очень быстро увеличится в размерах, чтобы этого избежать собирайте только нужную информацию и используйте фильтры
Из-за большие затрат на сбор Extended Events, рекомендуется собирать их в определённое временное окно и исследуя определённые типы ожиданий. Крайней не рекомендуется использовать их для регулярного мониторинга.
Наиболее часто встречающиеся виды ожиданий
CXPACKET waits
Этот тип ожиданий создан специально для параллельных запросов, которые часто потребляют дополнительные ресурсы CPU. Стоит начать поиск с дорогих запросов по CPU. С помощью этого запроса можно найти дорогие запросы по CPU
PAGEIOLATCH_XX waits
Ожидания связаны с поднятием страниц с диска в память. Для их анализа нам следует искать запросы, в которых большое количество физических чтений/записи. Пример кода, который поможет вам найти запросы с большим количеством физических чтений/записи
LCK_M_XX waits
Ожидания блокировок. Иногда для решения этих ожиданий вам поможет «blocked process report». Так же вы можете посмотреть бесплатный курс Troubleshooting Blocking and Deadlocks for Beginners (англ).
Более редкие типы ожиданий
RESOURCE_SEMAPHORE waits
Этот тип ожиданий ассоциируется с малым «query workspace memory» и со стороны это может выглядеть как будто SQL Server не работает. Тем не менее вы по прежнему можете использовать выделенное административное подключение для изучения проблемы.
Так же вам поможет счётчик производительности Manager\Memory Grants Pending (количество процессов, которые ожидают выделения памяти). Вы можете настроить Alert на SQL Server Agent для данного счётчика, который будет собирать информацию о текущей активности сервера в таблицу.
THREADPOOL waits
Ожидания связаны с недостаточностью потоков (threads). Это означает, что на сервере много активных процессов, так много, что SQL Server не может больше их выделить без риска нарушения стабильности OS. Это может быть связано с проблемами пула подключений или приложением, которое не корректно завершает подключения или создаёт их чрезмерное количество.
Как и в случае с RESOURCE_SEMAPHORE, вы по прежнему можете воспользоваться выделенным административным подключением для анализа ситуации.
Вы так же можете воспользоваться Alert на SQL Server Agent для следующего счётчика производительности — General Statistics\Processes Blocked и собрать нужную информацию.
Вывод
Для большинства ожиданий, мы должны знать что выполняется в момент ожидания, какой статус сессий, какие ресурсы они используют и тд. Нам нужно намного больше чем просто запросы и ожидания.
Профессиональные системы мониторинга могут существенно облегчить нам поиск проблем, так как они собирают много полезной информации.
Если вы используете SQL Server 2016+, вам сильно поможет механизм Query Store.
