Ищем медленные запросы SQL Server через Extended Events

Периодически медленный запрос трудно исследовать после завершения. План другого запуска может не объяснить, ушло ли время на процессор, чтения или ожидания. Узкая сессия Extended Events сохраняет полезные свидетельства без попытки записывать все подробности каждой инструкции сервера.

Вопрос примера конкретен: какие завершённые запросы одной базы выполняются не менее секунды? Такой сбор ограничен и позволяет выбрать случаи для углубления. Пример относится к обычному экземпляру SQL Server; облачные сессии уровня базы и их хранилища требуют других настроек.

До запуска определяем границы сбора

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

SELECT object_name, name, type_name, description
FROM sys.dm_xe_object_columns
WHERE object_name IN (N'rpc_completed', N'sql_batch_completed')
  AND name IN (N'duration', N'cpu_time');

У используемых событий завершения RPC и batch duration выражается в микросекундах, поэтому 1 000 000 означает секунду. Другие временные поля других событий могут иметь иные единицы. Сохраняйте эту информацию при экспорте.

Сессия фильтрует базу с ID 5. Замените его результатом DB_ID нужной базы, а Windows-путь существующим каталогом, доступным для записи учётной записи службы SQL Server. Имя сессии не должно совпадать с существующим.

-- SQL Server instance example: replace database_id 5 and the file path.
CREATE EVENT SESSION AxialSlowRequestsDemo ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XEvents\AxialSlowRequests.xel',
        max_file_size = (20), max_rollover_files = (4)
)
WITH (MAX_MEMORY = 4096 KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 5 SECONDS,
      STARTUP_STATE = OFF);
ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = START;

RPC охватывает, например, вызов процедуры через драйвер; batch относится к отправленному SQL-пакету. Совместный сбор учитывает разные способы работы приложений. Но события не становятся взаимозаменяемыми, и их длительности нельзя произвольно складывать без понимания области выполнения.

Размер и ротация файлов ограничены. Старые файлы могут удаляться при переходе к новым, поэтому это диагностическое окно, а не постоянный архив. STARTUP_STATE OFF не позволяет временной сессии автоматически возобновиться после перезапуска.

ALLOW_SINGLE_EVENT_LOSS допускает потерю событий. Такой сбор нельзя объявлять точным аудитом. Если полнота важна для вывода, наблюдайте состояние сессии и потерянные события.

Читаем данные для следующей проверки

Файлы читаются по тому же целевому пути.

SELECT object_name, CAST(event_data AS xml) AS EventXml
FROM sys.fn_xe_file_target_read_file(
    N'D:\XEvents\AxialSlowRequests*.xel', NULL, NULL, NULL
);

XML содержит время, специфичные поля и выбранные actions. Интерпретируйте время согласованно, обычно как UTC, при сопоставлении с журналом приложения. Длительность, CPU, логические чтения и текст нужно читать согласно схеме конкретного события.

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

Завершённые события не объясняют запрос, который продолжает выполняться бесконечно. Во время активной проблемы отдельно изучайте текущие requests и ожидания. Сетевое время клиента и отрисовка интерфейса также не входят в длительность SQL этого события.

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

Останавливаем сбор и фиксируем вывод

После воспроизведения или исчерпания времени остановите и удалите временную сессию.

ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = STOP;
DROP EVENT SESSION AxialSlowRequestsDemo ON SERVER;

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

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

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

Техническая документация: Microsoft Learn: Extended Events quick start · Microsoft Learn: CREATE EVENT SESSION · Microsoft Learn: Read event files.