Вольный перевод статьи Тима Форда. Оригинал статьи находится здесь: https://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/
Проблема
Я был сильно разочарован,попытавшись установить причину, когда пользователи обращаются ко мне и говорят "база тормозит!". Запуск PerfMon и Profiler оказался неэффективным. Раньше я всегда начинал поиск проблемы, используя эти инструменты. А у Вас есть какие-нибудь предложения для быстрого определения причины, если видны проблемы на CPU, Памяти или Диске? И эти решения не должны стать поиском иголки в стогу сена, как это бывает с PerfMon и Profiler.
Решение
Есть определенно лучший способ для быстрых разбирательств с "тормозами базы", именно на этот способ и стоит потратить свое экспертное время. Кстати, этот способ связан с моей любимой темой по SQL Server: Dynamic Management Views (DMV). Существует определенная DMV с помощью которой вы можете быстро найти первоисточник проблемы CPU, Памяти или Диска: sys.dm_os_wait_stats.
Каждый раз, когда SQL Server вынужден ждать ресурс, это ожидание протоколируется. Да, подобно всем Вашим бывшим, SQL Server копит обиды в себе :) Вы можете получить список ожиданий экземпляра через DMV sys.dm_os_wait_stats. Список полей (столбцов) для этого DMV представлен ниже:
Каждый раз, когда SQL Server вынужден ждать ресурс, это ожидание протоколируется. Да, подобно всем Вашим бывшим, SQL Server копит обиды в себе :) Вы можете получить список ожиданий экземпляра через DMV sys.dm_os_wait_stats. Список полей (столбцов) для этого DMV представлен ниже:
- wait_type - тип случившегося ожидания, текуший перечень типов ожидания описан на сайте Microsoft здесь: https://msdn.microsoft.com/ru-ru/library/ms179984.aspx . Для SQL Server 2005 существует 201 тип ожидания, а в 2008 R2 SP1 уже 485;
- waiting_tasks_count - накопленное количество задач зарегистрированных с момента последнего рестарта служб SQL Server ;
- wait_time_ms - накопленная сумма времени ожиданий для всех задач, столкнувшихся с данном типа ожидания, с момента последнего рестарта служб SQL Server ;
- max_wait_time_ms - максимальное время ожидания для любой задачи по данному типу ожиданий, начиная с последнего рестарта служб SQL Server ;
- signal_wait_time_ms - cумма времени, в миллисекундах, когда любой запрос ожидает после получения сигнала об освобождении требуемого ресурса до тех пор, пока исполнитель (worker) получит управление запросом. Большое время ожидания в этом поле служит признаком проблем с CPU, в том что поток все ещё ждёт назначение на CPU, даже после того, как ожидаемый ресурс был освобожден.
Выше я уже упомянул, что информация в этом DMV имеет накопительный характер. Эта информация накапливается и хранится в памяти SQL Server. Это значит, что вся информация будет потеряна(сброшена) при перезапуске служб SQL Server. Если Вы хотите иметь историю для этих метаданных, Вам нужно рассмотреть возможность сохранения информации из этого DMV в таблицу. Подробнее, как это сделать, будет рассмотрено ниже в этой статье.
Итак, как же выглядят данные из этого DMV? Давайте рассмотрим некоторый результат, полученный простым запросом (select *...) :
Посмотрите на образец данных, Вы видите, что результаты возвращаются сгруппированными по каждому типу ожиданий, независимо от того были или нет любые накопленные задачи, которые ожидали ресурсы по определенному типу ожиданий. Вы также можете увидеть, что ожидания из-за блокировок тоже включены в это DMV. Такой простой запрос говорит нам совсем немного. Но мы можем получить гораздо больше информации из этого DMV.
Глен Берри в главе по DMV в своей книге MVP Deep Dive Book предлагает большой запрос, который использует sys.dm_os_wait_stats. Я взял этот запрос и слегка изменил его, чтобы получать снимок ожиданий в процентной форме на текущий момент времени. Используя CTE для построения таблицы ожиданий, можно отфильтровать ненужные типы ожиданий, и потом представить результат в виде списка, в котором будут только те ожидания, которые входят в top N% (в данном случае top 95%) всех ожиданий экземпляра 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',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) -- filter out additional irrelevant waits
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;
В данном случае Вы можете видеть, что начиная с последнего рестарта служб SQL Server, я в первую очередь имею дело с ожиданиями ASYNC_NETWORK_IO и OLEDB на моем экземпляре SQL Server. Я знаю, что существуют проблемы с приложением на моем сервере, а причины - возвращение в приложение результирующего набора данных в построчном (row-by-row) режиме. Ожидание SOS_SCHEDULER_YIELD возникает всякий раз, когда задача перемещается в очередь, чтобы разрешить другому процессу выполняться, вместо неё. И это ожидание свидетельствует о проблемах с CPU, которому приходиться переключаться между задачами. Некоторые ожидания будут указывать направление, где нужно будет сфокусировать свое экспертное время, и причины таких ожиданий будут напрямую связаны с CPU или памятью или дисковой подсистемой. Например, ожидания типа PAGEIOLATCH_XX свидетельствуют о проблемах с дисками, так же как и ожидания WRITELOG. Ожидание CXPACKET - это индикатор задержек при параллельном исполнении запросов. Я советую открыть блог Microsoft CSS Team и прочитать все записи, связанные с типами ожиданий, чтобы иметь чёткое понимание при решении проблем с помощью инструмента sys.dm_os_wait_stats (например: http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx ).
Очень важная вещь, которую нужно запомнить - все значения в столбцах имеют накопительный характер. Как и все объекты Динамического управления (Dynamic Management) в SQL Server информация постоянно накапливается (увеличивается) с течением времени и будет очищена при рестарте служб SQL Server. Именно поэтому я упомянул о сохранении данных в статическую таблицу в некоторую административную БД, расположенную на каждом обслуживаемом экземляре SQL Server. Тогда Вы сможете получить информацию по периоду - например, между двумя последними сборами информации. Я использую Job на SQL Server Agent, который выполняется каждый час и сохраняет информацию в статическую таблицу. У вас всегда будет два варианта: либо выполнить сбор когда нужно (запустив код вручную), либо выполнять сбор автоматически по расписанию. Сам запрос легкий и будет практически незаметным даже на слабеньком сервере. Ниже представлен Шаблон запроса. Замечание: оставшиеся скрипты в этой статье составлены в виде Шаблонов T-SQL. В Management Studio откройте скрипт и нажмите CTRL+SHIFT+M. Откроется форма для ввода значений параметров, которые нужно будет заполнить под Ваше окружение.
USE [<database_name,,Foo>]; --Create table to persist wait stats information: CREATE TABLE <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
(
[wait_type] [nvarchar](60) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL,
[capture_time] [datetime] NOT NULL,
[increment_id] [int] NOT NULL
); ALTER TABLE <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
ADD DEFAULT (GETDATE()) FOR [capture_time]; --Insert wait stats info in a datestamped format for later querying: DECLARE @DT DATETIME ; SET @DT = GETDATE() ; DECLARE @increment_id INT; SELECT @increment_id = MAX(increment_id) + 1 FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>; SELECT @increment_id = ISNULL(@increment_id, 1)
INSERT INTO <database_name,,Foo>.<table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],
[signal_wait_time_ms], [capture_time], [increment_id]) SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],
[signal_wait_time_ms], @DT, @increment_id FROM sys.dm_os_wait_stats;
Следующая информация будет сохранена в физическую таблицу (которую вы укажите). Если же использовать значения параметров по умолчанию - то это будет таблица Foo.dbo.dm_os_wait_stats. Обратите внимание, как производиться заполнение increment_id и datastamp для собранных данных:
После сбора данных можно будет выполнить запрос (представлен ниже), и увидеть информацию по ожиданиям между двумя последними сборами данных. [последние столбцы удалены из прикрепленного скриншота с результатами для экономии места]:
--Return persisted information from table USE [<database_name,,Foo>]; DECLARE @max_increment_id INT ------------------------------------------------------------------
--Determine most-recent increment_id
------------------------------------------------------------------ SELECT @max_increment_id = MAX(increment_id) FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
------------------------------------------------------------------
--Present Waits results for period
------------------------------------------------------------------ SELECT DOWS1.wait_type,
(DOWS1.waiting_tasks_count - DOWS2.waiting_tasks_count) AS [waiting_tasks_count],
(DOWS1.wait_time_ms - DOWS2.wait_time_ms) AS [wait_time_ms],
DOWS1.max_wait_time_ms,
(DOWS1.signal_wait_time_ms - DOWS2.signal_wait_time_ms) AS [signal_wait_time_ms],
DATEDIFF(ms, DOWS2.capture_time, DOWS1.capture_time) AS [elapsed_time_ms],
DOWS1.capture_time AS [last_time_stamp], DOWS2.capture_time AS [previous_time_stamp] FROM
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
WHERE increment_id = @max_increment_id
)AS DOWS1
INNER JOIN
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
WHERE increment_id = (@max_increment_id - 1)
)AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0
/*
This can technically be eliminated because we're not persisting these waits:
AND DOWS1.wait_type NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
*/ ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC;
--wait_stats as percentage just for current collection period: USE [<database_name,,Foo>]; DECLARE @max_increment_id INT ------------------------------------------------------------------
--Determine most-recent increment_id
------------------------------------------------------------------ SELECT @max_increment_id = MAX(increment_id) FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>;
------------------------------------------------------------------
--Present Waits results for period
------------------------------------------------------------------ WITH Waits AS
(
SELECT DOWS1.wait_type,
((DOWS1.wait_time_ms - DOWS2.wait_time_ms)/1000) AS [wait_time_s],
100. * (DOWS1.wait_time_ms - DOWS2.wait_time_ms) / SUM(DOWS1.wait_time_ms - DOWS2.wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC) AS rn
FROM
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
WHERE increment_id = @max_increment_id
)AS DOWS1
INNER JOIN
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
WHERE increment_id = (@max_increment_id - 1)
)AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type
WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0
)
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;
Комментариев нет:
Отправить комментарий