(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

Источник: TechNet Magazine
Гленн Берри, Луи Дэвидсон и Тим Форд

Каждый раз, когда сеансу 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%, то наверняка в системе происходит что-то не так.

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft »
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 19.09.2013 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft Visual Studio Professional 2017 Sngl OLP 1License NoLevel
Microsoft Visual Studio Professional w/MSDN AllLng Software Assurance OLP 1 License No Level Qualified
Microsoft System Center Standard Core Sngl License/Software Assurance Pack OLP 2Licenses NoLevel CoreLic Qualified
Microsoft Visual Studio Professional w/MSDN AllLng License/Software Assurance Pack OLP 1 License No Level Qualified
Microsoft SQL CAL 2017 Sngl OLP 1License NoLevel UsrCAL
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Компьютерные книги. Рецензии и отзывы
Работа в Windows и новости компании Microsoft
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Отличается ли ДрифтКазино от беттинга? (9)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
Windows 10 загружен процессор (2)
Добрый день! На рабочем ПК Windows 10, компьютер тормозит, в диспетчере задач выдает что диск...
 
ErWin to Access Relation Error (2)
Всем привет! ErWin при попытке генерации в Ассеss выдаёт: ERwinDatabase.Relations.Append...
 
Помощь по MS Access (331)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Смена типа уровня модели (1)
Здравствуйте. При запуске программы выбрал уровень "Логический" вместо "Логический и...
 
 
 



    
rambler's top100 Rambler's Top100