Повний гайд з моніторингу MS SQL Server: від DMV до Grafana

-- Поточні активні запити з часом виконання та 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% — це в 10 разів ефективніше за Profiler. Дев'яносто відсотків функціональності 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 (https://github.com/free/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, кількість баз). Вартість аудиту стартує від 2000 €, повне налаштування — від 4000 €. Економія на простоях та оптимізації запитів може досягати 5000 € на місяць. Наші інженери мають сертифікати Microsoft та понад 10 років досвіду в адмініструванні SQL Server. Ми реалізували моніторинг для десятків проєктів, включаючи системи з тисячами транзакцій на секунду. Гарантований overhead не перевищує 5%.

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