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).

SystemHealthXE

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.

XEMenuBar

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

XEFilter

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

DeadlockResults

Conclusion

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s