Мониторинг производительности SQL Server

Мониторинг производительности SQL Server строится вокруг нескольких ключевых метрик: использование CPU, ожидания (wait stats), потребление памяти, дисковая активность и статистика запросов. Без их отслеживания вы просто не поймёте, почему сервер тормозит и где именно узкое место, а бизнес теряет деньги каждую минуту простоя.

Почему вообще нужен мониторинг

Короче, ситуация такая. SQL Server может работать годами без проблем, а потом бац (и база ложится в самый неподходящий момент). У моего знакомого Андрея, сисадмина из Новосибирска, так и было: сервер на Dell PowerEdge R740 с 64 гигами оперативки работал полтора года как часы, а потом в один прекрасный понедельник утром всё встало колом.

Проблема была банальная. Tempdb разросся на весь диск. Если бы стоял нормальный мониторинг, Андрей бы увидел тренд за неделю до катастрофы и спокойно всё почистил.

А знаете что? Большинство компаний начинают думать про мониторинг только после первого серьёзного инцидента. Не надо так.

CPU: первая метрика, на которую смотрим

Загрузка процессора в SQL Server делится на два типа: signal waits и resource waits. По факту, если signal waits превышают 20-25% от общего времени ожидания, процессор не справляется.

Вот базовый запрос для проверки:

-- Смотрим общую загрузку CPU сервером SQL
-- Если больше 70% постоянно, пора разбираться
SELECT
    record_id,
    SQLProcessUtilization,
    SystemIdle,
    100 - SystemIdle - SQLProcessUtilization AS OtherProcesses
FROM (
    SELECT
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
    FROM (
        SELECT CAST(record AS XML) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    ) AS x
) AS y
ORDER BY record_id DESC;

Тут такое дело: сам по себе высокий CPU не всегда проблема. Если сервер обрабатывает кучу запросов и все выполняются быстро, то 80% загрузки это нормально. Проблема начинается, когда CPU высокий И запросы тормозят.

На самом деле, чаще всего высокий CPU вызывают плохие запросы, а не нехватка железа. Один кривой запрос с full table scan может загрузить все ядра. Не спешите апгрейдить сервер, сначала найдите этот запрос.

Wait Stats: самая недооценённая метрика

Wait statistics это самый мощный инструмент диагностики. SQL Server сам записывает, на что именно тратит время каждый поток.

Основные типы ожиданий, за которыми стоит следить:

-- Топ ожиданий за всё время работы сервера
-- Фильтруем системные, они не интересны
SELECT TOP 10
    wait_type,
    wait_time_ms / 1000.0 AS wait_time_sec,
    signal_wait_time_ms / 1000.0 AS signal_wait_sec,
    waiting_tasks_count,
    wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS pct
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',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'XE_DISPATCHER_WAIT','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION'
)
ORDER BY wait_time_ms DESC;

Кстати, после перезагрузки сервера эти счётчики сбрасываются. Поэтому важно снимать снапшоты регулярно и смотреть дельту, а не абсолютные значения. Грубо говоря, абсолютные цифры говорят «за всё время», а дельта за последний час/день. Дельта полезнее.

Память: Buffer Pool и Page Life Expectancy

Page Life Expectancy (PLE) показывает, сколько секунд страница данных живёт в памяти, прежде чем будет вытеснена. Старое правило «PLE должен быть больше 300» давно устарело.

Вот в чём прикол: для сервера с 128 ГБ оперативки нормальный PLE будет в районе нескольких тысяч. Формула простая: (объём памяти в ГБ / 4) * 300. Для 64 ГБ это 4800. Если PLE падает ниже этого значения, памяти не хватает.

-- PLE по NUMA нодам
-- На больших серверах смотрим каждую ноду отдельно
SELECT
    [object_name],
    instance_name,
    cntr_value AS page_life_expectancy
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Node%'
    AND counter_name = 'Page life expectancy';

-- Buffer cache hit ratio: сколько % страниц читается из памяти
-- Должно быть 99%+, если меньше 95% - мало памяти
SELECT
    cntr_value AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
    AND [object_name] LIKE '%Buffer Manager%';

Если честно, я видел серверы где PLE скакал от 50000 до 200 за минуту. Обычно это значит, что какой-то запрос делает massive scan по огромной таблице и вымывает весь кеш. Надо ловить такие запросы через Query Store или Extended Events.

Дисковая подсистема

Диски это часто самое слабое звено. Даже с SSD.

-- Латенси чтения и записи по файлам БД
-- Больше 20мс на чтение = проблема
-- Больше 5мс на запись лога = тоже проблема
SELECT
    DB_NAME(vfs.database_id) AS db_name,
    mf.physical_name,
    mf.type_desc,
    io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_ms,
    io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_ms,
    num_of_reads,
    num_of_writes,
    -- Размер прочитанных данных в ГБ
    num_of_bytes_read / 1073741824.0 AS read_gb,
    num_of_bytes_written / 1073741824.0 AS write_gb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY (io_stall_read_ms + io_stall_write_ms) DESC;

У Марины, DBA из Казани, был случай: новый сервер HP ProLiant DL380 Gen10, SSD диски, а латенси на записи в лог 50 миллисекунд. Оказалось, RAID-контроллер работал в режиме write-through вместо write-back. Поменяли один параметр в настройках контроллера, латенси упал до 1мс. Кстати, для write-back обязательно нужна BBU (Battery Backup Unit) на RAID-контроллере, иначе при сбое питания данные в кеше контроллера потеряются.

Мониторинг запросов: Query Store

Начиная с SQL Server 2016 есть Query Store. Штука незаменимая. Она записывает историю планов выполнения и статистику по каждому запросу. В SQL Server 2022 Query Store включён по умолчанию.

А ещё Query Store помогает при обновлении: можно сравнить производительность до и после, и если какой-то запрос деградировал, принудительно зафиксировать старый план.

-- Топ запросов по CPU за последний час
-- query_store должен быть включён на базе
SELECT TOP 20
    q.query_id,
    SUBSTRING(qt.query_sql_text, 1, 200) AS query_text,
    SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_reads,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms
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 rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu DESC;

-- Найти регрессировавшие запросы (стали работать хуже)
SELECT TOP 10
    q.query_id,
    SUBSTRING(qt.query_sql_text, 1, 200) AS query_text,
    rs1.avg_duration / 1000.0 AS old_avg_ms,
    rs2.avg_duration / 1000.0 AS new_avg_ms,
    (rs2.avg_duration - rs1.avg_duration) / rs1.avg_duration * 100 AS pct_regression
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 rs1 ON p.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats rs2 ON p.plan_id = rs2.plan_id
WHERE rs2.avg_duration > rs1.avg_duration * 2 -- стало в 2+ раз дольше
ORDER BY (rs2.avg_duration - rs1.avg_duration) DESC;

Инструменты мониторинга

Встроенные средства это хорошо, но для серьёзного мониторинга нужны специализированные инструменты:

• SQL Server Management Studio Activity Monitor, встроенные отчёты. Бесплатно, но только в реальном времени, без истории
• sp_whoisactive скрипт от Adam Machanic, показывает что прямо сейчас выполняется (мастхэв для любого DBA, скачайте с GitHub)
• SolarWinds DPA платный, но визуализация wait stats просто огонь
• Grafana + InfluxDB можно собрать бесплатный мониторинг через telegraf с SQL Server input plugin
• Zabbix есть готовые шаблоны для SQL Server, бесплатный
• Redgate SQL Monitor платный, красивые дашборды, алерты

На самом деле, для начала достаточно sp_whoisactive и набора SQL-скриптов (как в этой статье). Это покрывает 80% потребностей в мониторинге. Платные инструменты нужны, когда серверов много и нужна история с трендами.

Настройка алертов

Мониторинг без алертов это как камера наблюдения без записи. Вот минимальный набор алертов, который стоит настроить:

-- Алерт на высокий CPU (через SQL Agent)
-- Создаём джоб, который проверяет CPU каждые 5 минут
-- и отправляет email если загрузка > 90% более 10 минут

-- Алерт на низкий PLE
-- Порог: (RAM_GB / 4) * 300
-- Если PLE ниже порога 5 минут подряд, алерт

-- Алерт на свободное место на диске
EXEC xp_fixeddrives;
-- Если меньше 10 ГБ на любом диске, алерт

-- Алерт на неудачные бэкапы
SELECT database_name, MAX(backup_finish_date) AS last_backup
FROM msdb.dbo.backupset
WHERE type = 'D' -- полный бэкап
GROUP BY database_name
HAVING MAX(backup_finish_date) < DATEADD(day, -1, GETDATE());
-- Если полный бэкап старше суток, алерт

Блокировки: как ловить и разбирать

Блокировки (LCK_M_*) это отдельная большая тема. Если пользователи жалуются что "всё тормозит" и "программа зависла", в 50% случаев виноваты именно блокировки, а не нехватка железа.

-- Найти текущие блокировки
-- Кто кого блокирует прямо сейчас
SELECT
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time / 1000.0 AS wait_sec,
    blocker.session_id AS blocker_session,
    DB_NAME(blocked.database_id) AS db_name
FROM sys.dm_exec_requests AS blocked
JOIN sys.dm_exec_sessions AS blocker
    ON blocked.blocking_session_id = blocker.session_id
WHERE blocked.blocking_session_id > 0;

Если честно, самая частая причина блокировок это длинные транзакции. Кто-то открыл транзакцию, сделал UPDATE и не закоммитил. Все остальные ждут. Находите такие сессии и разбирайтесь с разработчиками.

Baseline: зачем и как снимать

Ну и напоследок. Мониторинг это не разовая настройка, а процесс. Снимайте baseline, когда всё работает хорошо, и сравнивайте с ним, когда что-то пойдёт не так. Без baseline вы не поймёте, 500 блокировок в секунду это нормально для вашей нагрузки или нет.

Baseline снимается просто: запускаете все диагностические запросы из этой статьи раз в день и сохраняете результаты в отдельную таблицу. Через месяц у вас будет история, по которой можно строить тренды и ловить аномалии до того, как они станут проблемой.

Мониторинг tempdb: скрытый убийца производительности

Тут такое дело: tempdb это системная база, которую используют все пользовательские базы. Сортировки, временные таблицы, пересборка индексов, версионирование строк. Если tempdb тормозит, тормозит весь сервер.

-- Проверяем использование tempdb
SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM sys.dm_db_file_space_usage;

-- Находим кто больше всех использует tempdb прямо сейчас
SELECT TOP 5
    t.session_id,
    t.database_id,
    t.user_objects_alloc_page_count * 8 / 1024 AS user_alloc_mb,
    t.internal_objects_alloc_page_count * 8 / 1024 AS internal_alloc_mb,
    s.login_name,
    s.program_name
FROM sys.dm_db_task_space_usage AS t
JOIN sys.dm_exec_sessions AS s ON t.session_id = s.session_id
WHERE t.database_id = 2
ORDER BY (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) DESC;

Мой знакомый Алексей, DBA в логистической компании из Санкт-Петербурга (сервер Lenovo ThinkSystem SR650 V2, 128 ГБ RAM, SQL Server 2022 Standard), обнаружил, что tempdb разрастался до 80 ГБ каждый вечер. Причина: отчёт в 1С создавал временную таблицу с миллионами строк. Если честно, разработчики 1С даже не знали об этом. После оптимизации запроса tempdb перестал разрастаться больше 5 ГБ. Производительность всего сервера выросла на 30%.

Короче, мониторьте tempdb отдельно. Это одна из тех метрик, которая может показать проблему задолго до того, как пользователи начнут жаловаться.

Автоматизация мониторинга через Database Mail

Вот в чём прикол: все эти запросы бесполезны, если вы не запускаете их регулярно. Настройте Database Mail и SQL Agent Job, который будет отправлять ключевые метрики на почту каждое утро.

-- Настраиваем Database Mail (один раз)
EXEC msdb.dbo.sysmail_configure_sp 'AccountRetryAttempts', '3';

-- Создаём Job для утреннего отчёта
-- В шаге Job вставляем запрос, который собирает метрики:
-- CPU, PLE, Wait Stats, размер tempdb, свободное место
-- И отправляет через sp_send_dbmail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLMonitoring',
    @recipients = '[email protected]',
    @subject = 'SQL Server Morning Report',
    @query = 'SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = ''Page life expectancy''',
    @attach_query_result_as_file = 1;

По факту, 10 минут на настройку Database Mail + Agent Job, и каждое утро у вас на почте свежий отчёт о здоровье SQL Server. Грубо говоря, это бесплатный мониторинг, который работает без сторонних инструментов.

Если вы работаете с SQL Server и вам нужна лицензия, в keytrust24.store можно подобрать подходящий вариант по адекватной цене. Кстати, если планируете использовать Query Store и другие продвинутые фичи мониторинга, имейте в виду, что часть из них доступна только в Enterprise Edition (Columnstore, online rebuild, resource governor). Standard Edition покрывает базовый мониторинг полностью.