on December 27, 2016
Deadlock 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:
- Use a simple Extended Events trace to get deadlock graphs via the sqlserver.xml_deadlock_report event
- Use a Server Side SQL Trace to get deadlock graphs (for older versions of SQL Server, or people who like SQL Trace)
- 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.