SQL Server. Отслеживание взаимных блокировок с помощью Extended Events

Из данной статьи вы узнаете, как:

1. Создать сессию XEvents с помощью мастера в SQL Server Management Studio
2. Создать сессию XEvents на T-SQL

Начиная с версии 2008 в SQL Server существует система обработки расширенных событий Extended Events (XEvents). С её помощью можно собирать информацию о событиях взаимной блокировки (deadlock) и их графы для дальнейшего анализа. 
По умолчанию взаимные блокировки уже отслеживаются сессией system_health. Направляются они в кольцевой буфер - структуру в памяти, где данные хранятся по принципу FIFO (first in, first out). Это значит, что при большом потоке событий старые данные затираются.
Для того, чтобы не терять данные, можно собрать информацию о взаимных блокировках в файл.

Создайте сессию XEevents в которой будут определены:

• События, которые необходимо сохранить (sqlserver.xml_deadlock_report)
• Информация из событий, которая нужна в конкретном случае (deadlock_graph)
• Место хранения файла на диске.

 Создание сессии XEvents с помощью мастера в SQL Server Management Studio

1. Запустите мастер создания сессии в Object Explorer Management > Extended Events > Sessions:

2. Укажите имя сессии. Выберите опцию Start the event session at server startup, если хотите запускать ее при старте сервера:

3. Найдите "xml_deadlock_report" в разделе с выбором событий

4. В разделе Specify Session Data Storage укажите путь к файлу, в котором будет храниться результат работы сессии. Enable file rollover включает автоматическую ротацию в указанной директории.
5. Результат работы мастера может быть открыт и сохранен в разделе Summary в виде кода на T-SQL.

Создание сессии XEvents на T-SQL

Код для создания/пересоздания сессии "deadlocks":

-- Удаление сессии, если существует
if exists (select 1 from sys.server_event_sessions where name = 'deadlocks')
    drop event session deadlocks on server;
go
-- Создание сессии расширенных событий
create event session deadlocks on server
add event sqlserver.xml_deadlock_report            -- событие
    add target package0.event_file(                -- собираем события в файл
        set filename=N'D:\MSSQL\XE\deadlocks\deadlocks.xel', -- путь и имя выходного файла (к которому будет добавлен постфикс со временем запуска сессии)
            max_file_size=(100),                   -- максимальный размер в Мб (по умолчанию 1024)
            max_rollover_files=30)                 -- максимальное количество хранимых сессий в каталоге (далее будет автоматическая ротация)
with (startup_state=on,                            -- запускать сессию при старте сервера
      max_dispatch_latency=30 seconds);            -- задержка сброса данных из буфера в файл (по умолчанию 30 секунд)
go
 -- Запуск сесии
alter event session deadlocks on server
    state = start;
go

Внимание. Папка в параметре filename должна существовать до начала создания/пересоздания сессии "deadlocks".

В результате создастся сессия XEvents deadlocks, которая будет запускаться автоматически при запуске сервера.

На каждый запуск сессии создаётся свой *.xel файл c автоматически генерируемым постфиксом.