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.

/***********************************************************************
Copyright 2016, Kendra Little - LittleKendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
/***********************************************************************
COLLECT DEADLOCK GRAPHS WITH EXTENDED EVENTS
This script contains TSQL to:
* Create an Extended Events Trace collecting sqlserver.xml_deadlock_report
* Start the trace
* Code to stop and delete the trace is commented out
Notes:
This works with SQL Server 2012 and higher
Change the filename to a relevant location on the server itself
Tweak options in the WITH clause to your preference
Note that there is no automatic stop for this! If you want that, use a
Server Side SQL Trace instead.
THIS CREATES AND STARTS AN EXTENDED EVENTS TRACE
***********************************************************************/
/* Create the Extended Events trace */
CREATE EVENT SESSION [Deadlock Report] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(SET filename=
N'S:\XEvents\deadlock-report.xel', max_file_size=(1024),max_rollover_files=(4))
/* File size is in MB */
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS /* 0 = unlimited */,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON)
GO
/* Start the Extended Events trace */
ALTER EVENT SESSION [Deadlock Report]
ON SERVER
STATE = START;
GO
/***********************************************************************
Test a deadlock with the code here:
https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/
/* Stop the Extended Events trace when you want with a command like this */
--ALTER EVENT SESSION [Deadlock Report]
-- ON SERVER
-- STATE = STOP;
--GO
/* Drop the trace when you're done with a command like this */
--DROP EVENT SESSION [Deadlock Report] ON SERVER;
--GO
/***********************************************************************
THIS OPTION USES A SERVER SIDE SQL TRACE TO PICK UP THE BPR.
YOU ONLY NEED THIS *OR* ONE OF THE XEVENTS TRACES
***********************************************************************/
/* Modified from a script generated from SQL Server Profiler */
/* Pre-requisites and notes:
This works with SQL Server 2005 and higher
Change the filename to a relevant location on the server itself
Tweak options to your preference (including the end date)
Code to stop and delete the trace is commented out at the end
THIS CREATES AND STARTS A SERVER SIDE SQL TRACE
*/
declare @rc int;
declare @TraceID int;
declare @maxfilesizeMB bigint;
declare @TraceEndDateTime datetime;
declare @TraceFilename nvarchar(500);
declare @rolloverfilecount int;
set @TraceEndDateTime = '2020-12-12 00:00:00.000';
set @maxfilesizeMB = 1024;
set @TraceFilename = N'S:\XEvents\Deadlock-Graph';
set @rolloverfilecount = 4;
/* Create the basic server side trace */
exec @rc = sp_trace_create
@TraceID output,
@options = 2 /* trace will use rollover files */,
@tracefile = @TraceFilename,
@maxfilesize = @maxfilesizeMB,
@stoptime = @TraceEndDateTime,
@filecount = @rolloverfilecount;
if (@rc != 0) goto error;
/* Add the deadlock graph event and collect some columns */
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
/* Start the trace */
exec sp_trace_setstatus @TraceID, 1
/* Return list of traces to the caller */
select * from sys.traces;
GOTO finish
error:
select [email protected]
finish:
GO
/***********************************************************************
Test a deadlock with the code here:
https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/
/***********************************************************************
STOP AND DELETE THE Deadlock-Graph Trace (when needed)
***********************************************************************/
--/* Get the trace id */
--DECLARE @id int
--SELECT @id=id
--FROM sys.traces where path like '%Deadlock-Graph%';
--/* Stop the trace by setting its status to 0 */
--EXEC sp_trace_setstatus @traceid = @id , @status = 0;
--/* Delete the trace by setting the status to 2 */
--EXEC sp_trace_setstatus @traceid = @id , @status = 2;
--/* Return remaining traces */
--SELECT * FROM sys.traces;
--GO
/***********************************************************************
COLLECT ERRORS, STATEMENTS, AND DEADLOCK GRAPHS WITH EXTENDED EVENTS
VERY VERBOSE
This script contains TSQL to:
* Create an Extended Events Trace collecting
errors, completed statements, and deadlock report
* Trace collects transaction_id on statement completed events, this
can be matched to xactid for each item in the Blocked Process Report
* Start the trace
* Code to stop and delete the trace is commented out at the end
Notes:
This works with SQL Server 2012 and higher
*** This will generate a LOT of data on most servers and may slow performance
due to collecting statement completed events. If in doubt, start
with the alternate script collecting sqlserver.xml_deadlock_report only. ***
Change the filename to a relevant location on the server itself
Tweak options in the WITH clause to your preference
Note that there is no automatic stop for this! If you want that, use a
Server Side SQL Trace instead.
THIS CREATES AND STARTS AN EXTENDED EVENTS TRACE
***********************************************************************/
/* Create the Extended Events trace */
CREATE EVENT SESSION [Errors + Statements + Deadlock Report] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(SET filename=
N'S:\XEvents\errors-statements-deadlock-report.xel', max_file_size=(1024),max_rollover_files=(4))
/* File size is in MB */
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS /* 0 = unlimited */,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON)
GO
/* Start the Extended Events trace */
ALTER EVENT SESSION [Errors + Statements + Deadlock Report]
ON SERVER
STATE = START;
GO
/***********************************************************************
Test a deadlock with the code here:
https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/
/* Stop the Extended Events trace when you want with a command like this */
--ALTER EVENT SESSION [Errors + Statements + Deadlock Report]
-- ON SERVER
-- STATE = STOP;
--GO
/* Drop the trace when you're done with a command like this */
--DROP EVENT SESSION [Errors + Statements + Deadlock Report] ON SERVER;
--GO

, , , , , , ,

One Response to Tracing Deadlock Graphs: Extended Events or Server Side Trace

  1. Nic Neufeld December 27, 2016 at 9:18 am #

    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.

Leave a Reply