Deadlock Detection using Extended Events

Andrew Smith Posted on

In this post I demonstrate deadlock detection for SQL Server using Extended Events, retrieving event data via both SSMS and T-SQL.

Whilst Extended Events have been in the product since SQL Server 2008, the introduction of the SSMS-based GUI in SQL Server 2012 for managing and viewing them has made them far more accessible.

There’s plenty of good information available that shows how to set up Extended Events (e.g. see here) which I won’t repeat here – instead I’ll just be focusing on setting up deadlock detection.

This is my test script:

DeadlockSQL

where the UPDATE statements are split across 2 T-SQL sessions and are executed one-by-one, alternating between the two sessions.

I’ve created a new Extended Events session called “Deadlock Detection”:

DeadlockSSMS

which just includes the xml_deadlock_report event, and which has an event file target. This target logs events to disk, where the maximum size of the target log file can be specified, as well as whether it should be rolled over once full, and how many log files to keep when rollover is active. (One of the alternative targets for Extended Events – an in-memory structure called the ring buffer – is sometimes mentioned in examples, but which has the problems mentioned here.)

As an aside, the xml_deadlock_report event is included in the set of events defined for the system generated “system_health” extended events session that can be seen in the above screenshot, so that can be used if desired as opposed to creating a new session.

If the deadlock detection session event file (see above screenshot) is right-clicked and “View Target Data…” is selected, we can see the instances of the deadlock event that have occurred:

DeadlockEventFile

When a particular event is selected, its deadlock graph can be viewed via a tabbed results window:

DeadlockGraph

So this is exactly the same as what we would see from the SQL Server Profiler when selecting the “Deadlock graph” event type.

As well as retrieving event information via the SSMS GUI, it can also be retrieved from event files via T-SQL:

SELECT CONVERT(xml, event_data).query(‘/event/data/value/child::*’),
       CONVERT(xml, event_data).value(‘(event[@name=”xml_deadlock_report”]/@timestamp)[1]’,’datetime’) as Execution_Time
FROM sys.fn_xe_file_target_read_file(‘C:\XEvents Logs\Deadlocks*.xel’, null, null, null)
WHERE object_name like ‘xml_deadlock_report’

So this query uses the sys.fn_xe_file_target_read_file system function to retrieve event data, and then XQuery functions to extract the required information. Sample results are:

DeadlockEventFileQuery

where the first column contains the XML for deadlock graph instances.

Prior to extended events becoming available, methods for deadlock detection consisted of:

  • Trace flag 1222
  • SQL Server Profiler via the “Deadlock graph” event class
  • Performance Monitor via the “SQLServer:Locks – Number of Deadlocks/sec” counter

Extended Events is preferred over all of these, since it’s a generic, lightweight and highly configurable infrastructure that Microsoft are committed to supporting into the future (see here for more details). Regarding the alternatives listed above: (i) there’s no reason to reconfigure the server using a trace flag when there’s a better alternative (and the traceflag 1222 output is more difficult to read than a deadlock graph), (ii) SQL Server Profiler has been deprecated now and so is likely to disappear from the product at some time in the future, and (iii) the Perfmon counter has very low granularity and cannot support any detailed level of troubleshooting.

So as can be seen above, setting up an extended events session to detect and log deadlock occurrences is very simple now that SSMS has GUI support for Extended Events, and for SQL Server 2012 installations and later it is likely to be the preferred option.