(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 System Center Standard Core Sngl License/Software Assurance Pack OLP 2Licenses NoLevel CoreLic Qualified
Microsoft Office 365 Бизнес премиум. Подписка на 1 рабочее место на 1 год
Microsoft SQL CAL 2017 Sngl OLP 1License NoLevel DvcCAL
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Microsoft Windows Professional 10, Электронный ключ
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Вопросы и ответы по MS SQL Server
Все о PHP и даже больше
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Excell не печатает :-( (2)
Почему то Excell не печатает страницы в альбомной ориентаций, то есть страница в аольбомной...
 
Как извлечь рисунки из файла Word (41)
Вообще-то есть еще способ - сделать в Word-е Copy рисунка, открыть Microsoft Photo Editor и там:...
 
ODBC: dBase CREATE TABLE... (9)
Есть проблема Из программы на CTD 2.0 PTF3 подключаемся к DBF-файлам Set SqlDatabase='Файлы...
 
Как сменить пароль для схемы SYSTEM? (6)
Как сменить пароль по умолчанию для схемы SYSTEM c 'manager' на свой? То же и для SYS
 
Помощь студентам по программированию (2)
https://xn-d1aqfdigh.xn-p1ai/ Помощь студентам по программированию на языках высокого уровня.
 
 
 



    
rambler's top100 Rambler's Top100