From this article you will learn how to:

1. Create an XEvents session with an SQL Server Management Studio wizard
2. Create an XEvents session in T-SQL

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.

Create an XEevents session that will determine:

• Events to be saved (sqlserver.xml_deadlock_report)
• Specific information from events (deadlock_graph)
• A place on the disk where the file is stored

Creating an XEvents session with an SQL Server Management Studio wizard

1. Launch the New Session Wizard in Object Explorer Management > Extended Events > Sessions:

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

3. Find xml_deadlock_report in the section with events

4. 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.
5. 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 exists
if exists (select 1 from sys.server_event_sessions where name = 'deadlocks')
    drop event session deadlocks on server;
go
-- Creation of an Extended Events session
create event session deadlocks on server
add event sqlserver.xml_deadlock_report            -- event
    add target package0.event_file(                -- collecting events into a file
        set 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 session
alter event session deadlocks on server
    state = start;
go
Important! 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.