Измерять ожидания SQL Server за период инцидента

Самое большое ожидание за все время работы сервера не обязательно объясняет, почему приложение замедлилось в 10:15. Сумма может включать недели обслуживания, фоновые задачи и уже изменившуюся нагрузку. Для короткого инцидента нужно измерить изменения в соответствующем интервале и связать их с затронутыми запросами.

Вычитать снимки без очистки статистики

Скрипт делает два снимка с интервалом десять секунд и вычитает накопительные счетчики. Он не сбрасывает общую статистику. Используйте подходящие права диагностики: обычно VIEW SERVER STATE до SQL Server 2022 и VIEW SERVER PERFORMANCE STATE начиная с этой версии.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #WaitBefore
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:00:10';

SELECT
    w.wait_type,
    w.waiting_tasks_count - b.waiting_tasks_count AS waits_started,
    w.wait_time_ms - b.wait_time_ms AS wait_ms,
    w.signal_wait_time_ms - b.signal_wait_time_ms AS signal_ms,
    (w.wait_time_ms - b.wait_time_ms)
      - (w.signal_wait_time_ms - b.signal_wait_time_ms) AS resource_ms
INTO #WaitDelta
FROM sys.dm_os_wait_stats AS w
JOIN #WaitBefore AS b ON b.wait_type = w.wait_type;

IF EXISTS
(
    SELECT 1 FROM #WaitDelta
    WHERE waits_started < 0 OR wait_ms < 0 OR signal_ms < 0
)
    THROW 50001, 'Counters changed incompatibly; discard this sample.', 1;

SELECT TOP (20) *
FROM #WaitDelta
WHERE wait_ms > 0
ORDER BY wait_ms DESC;

DROP TABLE #WaitDelta;
DROP TABLE #WaitBefore;

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

Отрицательная разница показывает несовместимость снимков, часто после сброса счетчиков. Перезапуск обычно разрывает и эту сессию с временными таблицами. Сборщик с постоянным хранением обязан отдельно распознавать новое время запуска сервера.

Простая проверка обнаруживает не все сбросы: обнуленный счетчик может успеть вырасти выше прошлого значения. Поэтому согласуйте операции очистки и сохраняйте контекст измерения. Не вычитайте max_wait_time_ms для получения максимума за интервал. Если исторический максимум остается 20 секунд, новое ожидание в 19 секунд не изменит его, и такая разница ничего не покажет.

Правильно понимать единицы измерения

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

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

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

Некоторые категории описывают нормальную фоновую координацию. Фильтр делает панель удобнее, но исходные данные и правила фильтрации стоит сохранять. Исключенная сегодня категория может понадобиться завтра. Кроме того, удаление одной категории меняет проценты остальных без изменения реального объема работы.

Найти ответственную нагрузку

Рост ожиданий блокировок ведет к поиску блокирующих сессий, возраста транзакций и объектов. PAGEIOLATCH направляет к чтению страниц и запрошенному объему ввода-вывода. PAGELATCH относится к синхронизации в памяти и не означает автоматически необходимость новых дисков. ASYNC_NETWORK_IO может отражать медленное получение результатов клиентом.

Во время активной остановки смотрите sys.dm_os_waiting_tasks и текущие запросы. Долгое незавершенное ожидание еще может не попасть полностью в суммы завершенных длительностей. Сохраните цепочку блокировок и идентификаторы, пока соответствующие транзакции существуют.

Проверяйте исправление на сопоставимом окне нагрузки. Вместе с разницами фиксируйте число запросов, виды бизнес-операций и задержку пользователя. Меньше ожиданий на менее загруженном сервере не доказывает эффект оптимизации.

Для отчета о результате полезно указать не только ведущую категорию, но и конкретную установленную причину: например, какая операция удерживала блокировку и сколько полезных запросов завершалось за минуту до и после изменения. Тогда наблюдение становится проверяемым объяснением, а не списком названий ожиданий, который трудно применить при следующем инциденте.

Техническая документация: Microsoft Learn: Wait statistics · Microsoft Learn: Waiting tasks.