SQL Server. Tracking deadlocks with Extended Events

Starting with SQL Server 2008, the program includes an Extended Events (XEvents) system. It enables you to collect data on deadlocks and provides their graphs for further analysis. By default, deadlocks are tracked by the system_health session. They are sent to the circular buffer which is a special data structure in the memory that stores data on a FIFO (first in, first out) basis. It means that the system erases old data if the event flow is too large. To save data, you can collect all the information about deadlocks in one file.

Specify the name of the session. Select Start the event session at server startup if you want to launch it together with the server:

Find xml_deadlock_report in the section with events

In the Specify Session Data Storage section specify the path to the file that stores the deadlock report. Enable file rollover to activate automatic rollover in the specified directory.

Results of the wizard’s operation may be opened and saved in the Summary section as a code in T-SQL.

Creating an XEvents session in T-SQL

A code to create/recreate a deadlocks session:

-- Delete session if it existsif exists (select 1 from sys.server_event_sessions where name = 'deadlocks')drop event session deadlocks on server;go-- Creation of an Extended Events sessioncreate event session deadlocks on serveradd event sqlserver.xml_deadlock_report -- eventadd target package0.event_file( -- collecting events into a fileset filename=N'D:\MSSQL\XE\deadlocks\deadlocks.xel', -- path and name of an output file (a postfix with the session’s start time will be appended to it) max_file_size=(100), -- Max size in MB (1024 by default) max_rollover_files=30) -- Max number of sessions stored in the directory (with further automatic rollover)with (startup_state=on, -- Launch session at server startup max_dispatch_latency=30 seconds); -- Latency of data dispatch from clipboard to a file (30 seconds by default)go-- Starting the sessionalter event session deadlocks on server state = start;goImportant! Before starting to create/recreate the deadlocks session make sure that the folder in the filename parameter exists.

As a result, the XEvents deadlocks session will be created. It will be launched automatically at server startup.

Each time the session is started, an *.xel file with an automatically generated postfix is created.