Мониторинг MS SQL Server: DMV, Extended Events и Query Store

Вы запускаете отчёт в 14:32, и он висит 3 минуты. План запроса сменился, статистика устарела — ни error log, ни стандартные счётчики не показывают причину. Без правильно настроенного мониторинга вы остаётесь в неведении. Каждый час простоя приложения может стоить десятков тысяч долларов. С нашим подходом — конкретный запрос, план, блокировка. В статье — стек инструментов и методика настройки.

Какие проблемы решаем?

Блокировки, дедлоки, деградация запросов, нехватка CPU или I/O, неоптимальные планы — всё это диагностируется с помощью DMV, Extended Events и Query Store. Проактивный мониторинг позволяет выявлять проблемы до того, как они повлияют на пользователей. Реальные кейсы: один клиент после настройки снизил среднее время ответа на 40%, другой — сократил количество deadlock'ов с 50 до 2 в день.

Как мы это делаем?

Что такое DMV и как они помогают?

Dynamic Management Views — встроенные представления для диагностики в реальном времени. Не требуют установки агентов. Мы используем их постоянно для поиска «тяжёлых» запросов и узких мест. Для настройки используем SSMS или скрипты.

-- Текущие активные запросы с временем выполнения и wait types
SELECT r.session_id, r.status, r.wait_type, r.wait_time / 1000.0 AS wait_seconds,
       r.cpu_time / 1000.0 AS cpu_seconds,
       r.total_elapsed_time / 1000.0 AS elapsed_seconds,
       r.logical_reads,
       DB_NAME(r.database_id) AS database_name,
       SUBSTRING(t.text, (r.statement_start_offset/2)+1,
                 ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                   ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

-- Топ-10 самых дорогих запросов по CPU за всё время
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu_us,
       qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
                 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
                   ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_us DESC;

-- Накопленные wait statistics с момента запуска SQL Server
SELECT TOP 15 wait_type,
       waiting_tasks_count,
       wait_time_ms / 1000.0 AS wait_time_sec,
       max_wait_time_ms / 1000.0 AS max_wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_wait_sec,
       CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE',
    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
    'WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Wait statistics — первый инструмент диагностики. PAGEIOLATCH_SH/EX — SQL Server ждёт чтения страниц с диска, узкое место I/O. LCK_M_* — блокировки, смотрите sys.dm_exec_requests на blocking_session_id. CXPACKET — параллельные запросы, может быть нормой или признаком плохих планов. Таблица ниже — типичные wait types и их интерпретация:

Почему Extended Events лучше Profiler?

SQL Profiler устарел: он делает большие блокировки и жрёт ресурсы. Extended Events — современная замена с overhead менее 2%. Девяносто процентов функциональности Profiler при 2% overhead. Настраиваем сессию для поимки медленных запросов (дольше 5 секунд) в файл с ротацией 100 МБ:

CREATE EVENT SESSION [SlowQueries] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed ( 
    ACTION ( sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name, sqlserver.plan_handle ) 
    WHERE ( [duration] > 5000000 AND [sqlserver].[database_name] <> N'master' ) 
), 
ADD EVENT sqlserver.rpc_completed ( 
    ACTION (sqlserver.sql_text, sqlserver.database_name) 
    WHERE [duration] > 5000000 
) 
ADD TARGET package0.ring_buffer (SET max_memory = 51200), 
ADD TARGET package0.asynchronous_file_target ( 
    SET filename = N'D:\SQLTrace\SlowQueries.xel', max_file_size = 100, max_rollover_files = 5 
) 
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON ); 
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

Для deadlock'ов используем встроенную system_health — она уже пишет их автоматически. Извлечение графов делаем через sys.dm_xe_session_targets.

Как настроить Query Store для контроля планов?

Query Store — встроенное хранилище истории планов и метрик. Включается на уровне базы данных. Настройка заняла 15 минут у одного клиента, а результат — снижение среднего времени ответа на 40%. Рекомендуемые параметры:

ALTER DATABASE AppDB SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200 );

-- Поиск запросов, у которых план изменился в худшую сторону (например, за последние 24 часа)
SELECT TOP 20 q.query_id, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.execution_count, rs.avg_logical_io_reads, p.plan_id
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval i ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE i.start_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

-- Принудительно зафиксировать хороший план
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Сравнение методов мониторинга

Интеграция с внешними системами

DMV-данные экспортируем в Prometheus через sql_exporter или Telegraf. В Grafana строим дашборды по wait stats, QDS, активностям. Алерты — на критические ситуации: CPU > 90%, LCK_M_X wait > 30 секунд, заполнение лога > 80%.

Процесс работы

1. Аналитика — знакомство с архитектурой, выявление «болевых точек».
2. Проектирование — подбор состава сессий Extended Events, скриптов DMV, параметров Query Store.
3. Реализация — развёртывание скриптов, настройка алертов через SQL Server Agent.
4. Интеграция — подключение к Grafana/Prometheus (по желанию).
5. Тест — проверка корректности срабатывания алертов, замер overhead.
6. Документация — передача runbook и дашбордов.

Что входит в результат

• Скрипты DMV для ежедневной диагностики.
• Готовая сессия Extended Events для медленных запросов и deadlock'ов.
• Включённый и настроенный Query Store с политиками хранения.
• Алерты на критичные wait types и заполнение логов.
• Дашборд Grafana (если выбрана интеграция).
• Обучение команды: как читать wait stats и анализировать Query Store.

Сроки и стоимость

Типовой проект — от 2 до 4 рабочих дней в зависимости от сложности инфраструктуры (кластеризация, Always On, количество баз). Стоимость рассчитывается индивидуально после аудита текущего состояния. Экономия на простоях и оптимизации запросов может быть значительной.

Наши инженеры имеют сертификаты Microsoft и более 10 лет опыта в администрировании SQL Server. Мы реализовали мониторинг для десятков проектов, включая системы с тысячами транзакций в секунду. Гарантированный overhead не превышает 5%.

Свяжитесь с нами для обсуждения вашего проекта. Закажите аудит мониторинга уже сегодня.