Capturing Deadlocks With Extended Events

Extended Events is the replacement for Profiler.  There are a number of advantages to XEs:  they are much more lightweight than server-side traces, they can capture more information, and there are more methods for storing this information (including a ring buffer and writing out to disk).  My biggest problem with Extended Events is that even with the GUI, it’s still easier to set up a Profiler trace than futz about trying to set up an XE.

Deadlocks Are Different

With deadlocks, the default SQL Server system_health Extended Event already tracks deadlocks.  You can get to this session pretty easily in SQL Server Management Studio by connecting to your instance and going to Management —> Extended Events —> Sessions —> system_health.  Inside this session, there are two options:  a ring buffer which keeps track of recent events, as well as an event_file which holds a bit more detail on past events.  Depending upon how busy your server is, that event file might go back several days, or maybe just a few hours (or minutes on a very busy server).

Double-click on one of the session targets, depending upon whether you want to watch live data (ring buffer) or view older data (event file).  Once you do that, you’ll see the Extended Events viewer.  In SSMS, you will get a Filters button in the Extended Events menu.  Click the Filters button and you’ll get the ability to enter a filter in.

Select “name” from the dropdown and set its value equal to xml_deadlock_report.

Once you’re done with this, you’ll see only XML deadlock reports.  You can grab the XML and also see the deadlock graph.

Conclusion

Once you have deadlock graphs and you know how to read them, you can use that information to fix your deadlocking issues.

Advertisement