SQL Server: Раскрытие тайны задержек SQL Server

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

Динамическое административное представление (DMV) sys.dm_os_wait_stats позволяет увидеть эту статистику задержек, агрегированную по всем сеансам, что дает возможность увидеть, где происходят задержки в конкретном экземпляре. Это же динамическое представление также предоставляет счетчики производительности, которые предоставляют конкретные цифры использования ресурсов (скорость дискового обмена, процессорное время и т. п.).

Сопоставив статистику задержек с показателями загрузки ресурсов можно быстро определить самые «востребованные» ресурсы в системе и выявить возможные узкие места.

Задержки и очереди SQL Server 2005

Об использовании «задержек и очередей» как основы методики настройки производительности рассказывается в статье отличной статье Тома Дэвидсона (Tom Davidson), размещенной по адресу http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx. В конечном итоге, каждый запрос SQL Server инициирует определенное число «рабочих задач».

Планировщик SQL Server назначает каждой задаче рабочий поток. Обычно в ОС SQL имеется один планировщик на процессор, и в каждый момент времени есть лишь один выполняющийся сеанс в расчете на один планировщик. Задача планировщика — равномерно распределить нагрузку среди имеющихся рабочих потоков.

Если рабочий поток сеанса выполняется на процессоре, в столбце Status представления sys.dm_exec_requests его состояние будет отмечено как Running (то есть выполняющийся). Если поток готов к выполнению, но планировщик, которому он назначен, выполняет другой сеанс, тогда поток будет помещен в очередь Runnable готовых к выполнению потоков. То есть он ожидает своей очереди на доступ к процессору. Это называется временем ожидания.

Время ожидания указывается в столбце signal_wait_time_ms column, и это только время ожидания процессора. Если сеанс ожидает освобождения другого ресурса, такого как заблокированная страница, или текущему сеансу нужно выполнить ввод или вывод, тогда он размещается в списке ожидания. Это ожидание ресурса и состояние ожидающего сеанса будет отмечено как «Suspended» (то есть приостановлено).

Причина ожидания регистрируется и указывается в столбце wait_type динамического представления sys.dm_os_wait_stats. Общее время ожидания показано в столбце wait_time_ms, поэтому рассчитать время ожидания ресурса можно следующим образом:

ожидание ресурса = общее ожидание – ожидание освобождения = (wait_time_ms) – (signal_wait_time_ms)

Ожидание освобождения неизбежно в OLTP-системах, где операции состоят из большого числа коротких транзакций. Ключевой показатель возможной перегрузки процессора — процентная доля ожидания освобождения в общем времени ожидания. Высокая доля означает рост нагрузки на процессор. В литературе «большой» считается нагрузка больше 25%, но все зависит от системы.

В наших системах мы считаем тревожными значения больше 10–15%. В целом, использование статистики ожидания является очень эффективным инструментом диагностики времени реакции в системе. Проще говоря, или система работает, или она находится в системе ожидания. Время реакции равно времени обслуживания плюс время ожидания.

Если время реакции большое и обнаружены большие времена ожидания, тогда нужно решать, что делать с процессором. Если же время реакции большое из-за значительного времени, затрачиваемого на ожидание других ресурсов (таких как сеть, подсистема ввода/вывода и т. п.), тогда понятно, где требуется ваше внимание.

Никакого гадания на кофейной гуще

Марио Брудбаккер (Mario Broodbakker) написал отличную серию статей для начинающих об использовании событий ожидания для диагностики проблем с производительностью: http://www.simple-talk.com/author/mario-broodbakker. В нашем первом сценарии в категории операционных систем используется динамическое представление sys.dm_os_wait_stats, которое определяется в электронной документации так: «Возвращает данные обо всех случаях ожидания, обнаруженных выполнявшимися потоками». Это агрегированное представление можно использовать для диагностики проблем с производительностью в SQL Server, а также с конкретными запросами и их пакетами.

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

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;

Этот запрос полезен для подтверждения повышенной нагрузки на процессор. Так как время ожидания является временем ожидания обработки потока процессором, то обнаружив время ожидания, которое чуть больше 10–15%, можно утверждать о повышенной нагрузке на процессор.

Эта статистика накапливается с момента последнего перезапуска SQL Server, поэтому нужно знать базовое значение времени ожидания и следить за этим уровнем со временем. Можно вручную очистить статистику ожидания без перезагрузки сервера, воспользовавшись командой DBCC SQLPERF:

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

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

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

WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_s , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,
ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) SELECT W1.wait_type , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s , CAST(W1.pct AS DECIMAL(12, 2)) AS pct , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn , W1.wait_type , W1.wait_time_s , W1.pct HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold

Этот сценарий позволит обнаружить самое серьезное узкое место на уровне экземпляра. Это позволяет сосредоточиться на устранении проблемы определенного типа. Например, если самое большое накопительное время связано с дисковым вводом/выводом, тогда нужно глубже исследовать эту проблему, используя динамические представления и счетчики производительности для работы с дисками.

Существующие счетчики производительности

Динамическое представление, предоставляющее счетчики производительности, называется sys.dm_os_performance_counters и описано так: «Возвращает по строке на каждый счетчик производительности, хранимый на сервере». Это полезное динамическое представление, но оно может быть сложным в работе. При определенных значениях cntr_type в конкретных строках могут потребоваться дополнительные нетривиальные операции, чтобы получить разумную информацию средствами этого динамического представления. Оно пришло на смену старому представлению sys.sysperfinfo в SQL Server 2000.

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

Следующий сценарий позволяет изучить нестандартные условия, указанные в журнале транзакций. Он возвращает информацию о модели восстановления, ожидании перед повторным использованием журнала, размере журнала транзакций, занятом пространстве журнала в абсолютных цифрах и в процентах, уровне совместимости и параметре проверки страниц в каждой базе данных текущего экземпляра SQL Server:
SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] ,
lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;

Этот запрос позволяет проанализировать незнакомый сервер БД. Он также полезен для мониторинга. Например, если описание ожидания повторного использования журнала содержит что-то необычное и журнал транзакций заполнен на 85%, то наверняка в системе происходит что-то не так.