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 проработал существенное время, и вы занесли в него значимые конфигурации, к примеру, добавили новый индекс, тогда стоит очистить старенькую статистику ожидания. В неприятном случае, древняя накопительная статистика будет маскировать воздействие ваших конфигураций на время ожидания.

Во 2-м сценарии употребляется динамическое представление 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%, то наверное в системе происходит что-то не так.

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.