MSSQL — ловим тяжёлые запросы

Олег

28 января 2026

Ненавижу 1С. Здравствуйте.

MSSQL сервер стал проседать по CPU. Сначала немного, затем ещё немного, и CPU в час пик упёрся в полку.

К гадалке не ходи, виноват 1С. Что-то жрёт CPU, но я не силён в 1С. А потом ещё и tempdb в RAM моментально заполнился каким-то долгим запросом.

Сколько можно терпеть?! Отловить бяку сложно, но можно.

Ловить долгие запросы будем с помощью расширенных событий. Будем анализировать тяжелые запросы по CPU (времени выполнения). Сначала отловим самые долгие запросы, которые выполняются более 50 секунд. Напишем скрипт, который создает расширенное событие (Extended Event) в Microsoft SQL Server для мониторинга и сбора информации о тяжелых запросах, которые выполняются долгое время и потребляют значительные ресурсы CPU.

Применимо для:

Выявления проблем производительности — поиск самых долгих запросов
Оптимизации запросов — анализ SQL-текста медленных запросов
Диагностики проблем — определение, какие приложения/пользователи создают нагрузку
Capacity planning — понимания паттернов использования ресурсов

-- Анализ тяжелых запросов по CPU (времени выполнения)
CREATE EVENT SESSION [HeavyQueryByCPU50] ON SERVER
-- Класс событий RPC:Completed указывает, что удаленный вызов процедуры завершен.
-- https://docs.microsoft.com/ru-ru/sql/relational-databases/event-classes/rpc-completed-event-class?view=sql-server-2017
ADD EVENT sqlserver.rpc_completed(
   ACTION(
       sqlserver.client_app_name,
       sqlserver.client_hostname,
       sqlserver.client_pid,
       sqlserver.database_id,
       sqlserver.nt_username,
       sqlserver.server_principal_name,
       sqlserver.session_id,
       sqlserver.sql_text,
       sqlserver.transaction_id,
       sqlserver.username)
   -- Фильтр: запросы, выполняющиеся дольше 50 000 000 микросекунд (50 секунд)
   WHERE ([duration]>(50000000))),
-- Класс событий SQL:BatchCompleted указывает на завершение выполнения пакета языка Transact-SQL.
-- https://docs.microsoft.com/ru-ru/sql/relational-databases/event-classes/sql-batchcompleted-event-class?view=sql-server-2017
ADD EVENT sqlserver.sql_batch_completed(
   ACTION (
       sqlserver.client_app_name,
       sqlserver.client_hostname,
       sqlserver.client_pid,
       sqlserver.database_id,
       sqlserver.nt_username,
       sqlserver.server_principal_name,
       sqlserver.session_id,
       sqlserver.sql_text,
       sqlserver.transaction_id,
       sqlserver.username)
   -- Фильтр: запросы, выполняющиеся дольше 5 000 000 микросекунд (50 секунд)
   WHERE ([duration]>(50000000)))
ADD TARGET package0.event_file(SET 
   -- РЕКОМЕНДУЕТСЯ: Указать полный путь к файлу
   filename=N'HeavyQueryByCPU50.xel',
   -- Максимальный размер файла в мегабайтах
   max_file_size=(1024),
   -- Максимальное количество файлов, после чего начнется перезапись логов в более старых файлах.
   max_rollover_files=(5)
   )
WITH (
   MAX_MEMORY=4096 KB,
   EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
   MAX_DISPATCH_LATENCY=15 SECONDS,
   MAX_EVENT_SIZE=0 KB,
   MEMORY_PARTITION_MODE=NONE,
   TRACK_CAUSALITY=OFF,
   STARTUP_STATE=OFF -- Установите в ON, если нужно запускать сессию при старте сервера
);

Основное назначение скрипта

1. Мониторинг медленных запросов

Скрипт отслеживает два типа событий (50000000 микросекунд = 50 секунд):

RPC-запросы (удаленные вызовы процедур) - дольше 50 секунд
SQL Batch запросы (пакеты Transact-SQL) - дольше 50 секунд

2. Собираемая информация

Для каждого медленного запроса собираются:

Клиентское приложение (client_app_name)
Имя хоста клиента (client_hostname)
Идентификатор процесса клиента (client_pid)
Идентификатор базы данных (database_id)
Имя пользователя Windows (nt_username)
Имя входа SQL Server (server_principal_name)
Идентификатор сессии (session_id)
Текст SQL-запроса (sql_text)
Идентификатор транзакции (transaction_id)
Имя пользователя (username)

3. Хранение результатов

Данные сохраняются в файлы:

Формат: XEL (расширение для файлов Extended Events)
Максимальный размер файла: 1 ГБ
Максимальное количество файлов: 5 (после чего старые перезаписываются)
Имя файла: HeavyQueryByCPU50.xel

4. Конфигурация сессии

Название сессии: HeavyQueryByCPU50
Автозапуск: отключен (STARTUP_STATE=OFF)
Максимальная задержка обработки: 15 секунд
Режим хранения событий: допускается потеря одиночных событий

Выполняем в БД master.

Создаётся расширенное событие. Для запуска сеанса после создания нужно выполнить:

ALTER EVENT SESSION [HeavyQueryByCPU50] ON SERVER STATE = START;

Или ткнуть правой кнопкой в наше расширенные событие и выбрать "Запустить сеанс".

Теперь можно сутки погулять или подождать когда снова всё упадёт. Возвращаемся через продолжительное время и останавливаем сеанс:

ALTER EVENT SESSION [HeavyQueryByCPU50] ON SERVER STATE = STOP;

Или правой кнопкой — Остановить сеанс.

При остановке:

Прекращается сбор новых событий
Существующие данные в файлах сохраняются
Можно анализировать уже собранные данные
Сессия остается в системе и можно перезапустить

Правой кнопкой на package0.event_file, Посмотреть целевые данные. И анализируем то, что у нас сорбралось. Сразу видно, что добра навалило некоторое количество, и в этом нужно разбираться.

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

Если всё-таки нужно собрать данные через TSQL, то после остановки сеанса:

-- Чтение данных из файлов сессии
SELECT 
   event_data = CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file(
   'HeavyQueryByCPU50*.xel', -- путь к вашим файлам
   NULL, NULL, NULL
);

-- Более детальный анализ
SELECT 
   n.value('(@timestamp)[1]', 'datetime2') as timestamp,
   n.value('(data[@name="duration"]/value)[1]', 'bigint')/1000000 as duration_sec,
   n.value('(data[@name="cpu_time"]/value)[1]', 'bigint')/1000000 as cpu_time_sec,
   n.value('(action[@name="database_id"]/value)[1]', 'int') as database_id,
   n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as sql_text,
   n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(500)') as client_app_name,
   n.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(500)') as client_hostname,
   n.value('(action[@name="username"]/value)[1]', 'nvarchar(500)') as username
FROM (
   SELECT CAST(event_data AS XML) as event_data
   FROM sys.fn_xe_file_target_read_file(
       'HeavyQueryByCPU50*.xel',
       NULL, NULL, NULL
   )
) as ed
CROSS APPLY ed.event_data.nodes('//event') as q(n);

Примечания

Остановка сессии не удаляет файлы логов - они остаются на диске.
Удаление сессии (DROP) удаляет только конфигурацию, файлы нужно удалять вручную.
Если STARTUP_STATE=ON, сессия автоматически запустится при перезагрузке SQL Server.
Рекомендуется сначала останавливать, анализировать данные, и только потом удалять сессию при необходимости.