Tracing Deadlock Graphs: Extended Events or Server Side Trace

deadlock-manager-sql-serverDeadlock graphs are incredibly helpful for figuring out why queries are getting automatically killed off by SQL Server.

But it can be tricky to trace deadlocks in SQL Server. You might be tempted to use Profiler, but the application has lots of baggage. You’re better off using either Extended Events (which may be confusing), or a Server Side Trace (which you can script from Profiler– if you know exactly which buttons to push in the right order). You might look in the System Health trace on recent versions of SQL Server, but if the deadlock didn’t happen recently, then it may have rolled off the trace.

To make this all easier, I’ve created a gist on GitHub sharing TSQL to save you a bunch of time.

Choose the script that works for you. You can:

  1. Use a simple Extended Events trace to get deadlock graphs via the sqlserver.xml_deadlock_report event
  2. Use a Server Side SQL Trace to get deadlock graphs (for older versions of SQL Server, or people who like SQL Trace)
  3. Use a (much more verbose) Extended Events trace to get errors, completed statements, and deadlock graphs. You only need something like this if the input buffer showing in the deadlock graph isn’t enough, and you need to collect the other statements involved in the transactions. You do this by matching the transaction id for statements to the xactid for each item in the Blocked Process Report. Warning, this can generate a lot of events and slow performance.

View or download the code from GitHub, or get it below.

When testing out these deadlock traces, you might want code to reproduce a deadlock in SQL Server.

https://gist.github.com/LitKnd/19a5942e2527af3e90692c5145c3a059

Previous Post
Collecting the Blocked Process Report (XEvents and Server Side Trace)
Next Post
Columnstore Indexes and Computed Columns in SQL Server 2016

Related Posts

4 Comments. Leave new

I tend to lean on system_health for this…one thing to make sure of is that your script you are using to draw deadlocks from the session draws from the file target and not the ring buffer target (yep, I did that for a while before I realized my error). You can also change the file target size, I’m pretty sure (say that because I appear to have done it on a few servers, but who knows), but I’d have to look that up again! Worth allotting maybe a few hundred MB on an important server to get a longer retention for your system_health xevents.

Reply
Peter Andrews
August 13, 2018 5:26 pm

Hi Kendra,

I truly enjoy your posts!

I have a question on using XEvents to capture an XML_Deadlock_Report. When I add the session and create a deadlock I get the XEvent data just fine but is there a way to get the profiler style graph? Or perhaps turn the report xml output into a profiler style graph. The XML_Deadlock_Report has all the information I need but I’m just looking for a quick and dirty visual (graph) to save a little time.

Thanks.

Reply

    Yes! When you open the extended events trace in SSMS and highlight one of the events, there should be a little tab to click on in the Details pane, right next to the word ‘Details’. It will show a graphic image of the deadlock.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu