Анализируем ожидания с помощью динамического административного представления SQL

Я всегда отмечаю большую пользу метаданных и, в частности, ожиданий в Microsoft SQL Server. В этой статье будет показано, каким образом можно анализировать конкретные ожидания, связанные с блокированием различных экземпляров SQL Server, с помощью запроса к динамическому административному представлению sys.dm_os_wait_stats. Рассмотрим этапы этого процесса.

Краткие сведения о sys.dm_os_wait_stats

Динамическое административное представление в SQL Server дает информацию о типах ожидания, собранную со времени последнего перезапуска служб SQL Server в результате перезапуска сервера или перезапуска служб SQL Server либо после ручного сброса статистики ожидания с помощью следующей команды:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Ожидания и статистика ожидания представляют собой метрики, позволяющие выяснить причины, по которым экземпляр SQL Server не может обработать запрос немедленно вследствие нехватки одного или нескольких ресурсов. Таким образом, это объяснение, почему SQL Server не удалось выполнить задачу.

SQL Server не может выполнить запрос из-за ограниченности ресурсов, и вы можете выяснить, где имеет место значимое ограничение. Кроме того, сведения о конкретных ограничениях ресурсов могут привести администратора к решениям по настройке производительности, так как вы можете быстро определить, где источник ваших проблем. Сталкиваясь с обычными жалобами на медлительность базы данных, вы можете просмотреть статистику ожиданий и найти основную причину, по которой SQL Server «ждет», а затем идти определенным путем, не отыскивая подходы к неизвестной неисправности с разных сторон.

Вместо того чтобы совокупно исследовать память, процессор, синтаксис и сетевые параметры, вы можете сразу исключить одну или даже все причины, кроме одной, и решить проблему быстрее, чем было бы возможно в ином случае. Я уже написал несколько статей на тему статистики ожиданий и блокирования. Рекомендую вам ознакомиться с этими статьями, чтобы больше узнать о конкретном динамическом административном представлении и ожиданиях в целом, хотя на данном этапе у вас достаточно знаний, чтобы понять оставшуюся часть статьи, не отыскивая дополнительной информации об ожиданиях и sys.dm_os_wait_stats.

Запрос для идентификации кумулятивных блокирующих ожиданий

Чтобы получить результаты для ожиданий, возникших из-за блокировок, выполняется базовый запрос sys.dm_os_wait_stats, а затем отбираются ожидания, относящиеся к блокировкам. Исключаются любые результаты, где waiting_tasks_count больше 0, чтобы избежать любого потенциального деления на 0 при расчете наиболее важной метрики — среднего времени ожидания. Это не должно вызвать затруднений: записи следует собирать только в базовый источник данных для этого динамического административного представления, когда есть по крайней мере одна задача с определенным типом ожидания (см. листинг).

Кроме того, нужно сортировать результаты, чтобы отделить типы блокировок, которые встречаются часто, от более редких. Я рассматриваю нечастые блокировки как отклонения, которые могут представлять интерес, но определенно не подлежат первоочередному анализу. Я также предполагаю, что отклоняющиеся типы блокировок будут иметь большое среднее время ожидания, измеряемое миллисекундами, так как общее время ожидания распределяется по довольно небольшому числу событий. Это предположение подтверждается приведенными на рисунке результатами, полученными в реальной производственной среде, в которой был применен мой запрос.

Рисунок. Определение типов блокировок

О чем говорят эти результаты? Можно быстро определить, что наиболее частые ожидания, связанные с блокировками, — обновления, они эксклюзивны по природе. Это обычно для среды, в которой происходит много изменений данных. Ожидания LCK_M_U указывают на ожидание с целью получить блокировку обновления на данный ресурс, одновременно ожидая снятия несовместимой блокировки с этого ресурса. Я не могу определить, какой ресурс является источником таких блокировок, но могу выяснить это другим способом с использованием sys.dm_db_index_operational_stats для идентификации индексов и куч с большинством блокировок, рассмотренных по адресу: http://www.itprotoday.com/microsoft-sql-server/using-index-operational-stats-drill-high-latch-and-lock-waits.

В зависимости от заданных порогов задержки пользовательских транзакций, ожидание 15 мс может быть приемлемым или неприемлемым. При анализе результатов конкретной среды необходимо учитывать стандарты компании. Помните, что ожидание, связанное с блокировками, может быть лишь одним из ожиданий ресурсов, с которыми приходится сталкиваться в одном сеансе при выполнении запроса со времени его отправки до завершения.

Наконец, хотя я привожу ссылки на свои статьи на тему блокировок и ожиданий, вам может потребоваться дополнительная информация о конкретных типах блокировок и их совместимости. В документации Microsoft можно найти подробную информацию об отдельных типах блокировок и совместимости. При анализе конкретных типов блокировки я рекомендую обращаться к следующим публикациям:

Locking Modes in Microsoft SQL Server (https://technet.microsoft.com/en-us/library/ms175519 (v=sql.105).aspx);
Lock Compatibility Matrix for Microsoft SQL Server (https://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx);
Lock Granularity in Microsoft SQL Server (https://technet.microsoft.com/en-us/library/ms189849 (v=sql.105).aspx).

Листинг. Запрос для идентификации кумулятивных блокирующих ожиданий

SELECT WS.wait_type
       , WS.waiting_tasks_count
       , WS.wait_time_ms
       , WS.max_wait_time_ms
       , WS.wait_time_ms / WS.waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats AS WS
WHERE WS.waiting_tasks_count > 0
       AND WS.wait_type LIKE 'LCK_%'
ORDER BY WS.waiting_tasks_count DESC;