Capture Deadlocks 24/7 with Extended Events

Jon Seigel

Deadlocks can be notoriously difficult to reproduce in our development environments.

Most of the time:

  • There are multiple actual users involved with the problem.
  • The production workload, both in terms of volume and/or composition, can’t be realistically reproduced in development either.
  • The tools we used to need to use to capture deadlocks (server-side trace, SQL Server Profiler) are prohibitively expensive to run all the time, or even for a short time, or we had to mess around with trace flags.

Here’s where Extended Events come to the rescue, and really makes us able to be proactive to deal with deadlocks.

Extended Events allows us to capture deadlock graphs continuously, as they happen, 24 hours/day, 7 days/week, with very little performance overhead. Not only that, but the system we’re going to set up is able to survive an instance restart! This is huge in terms of ease of management, and being able to have this information available when it’s needed can be way more than half the battle.

Here is the meat of the script that sets up our event session for a SQL Server 2012 instance:

CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
	ADD EVENT sqlserver.xml_deadlock_report 
	ADD TARGET package0.event_file
	(
		SET
			FILENAME = N'\\CentralFileShare\MyTargetFile.xel',
			MAX_ROLLOVER_FILES = 0
	)
	WITH
	(
		EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
		MAX_DISPATCH_LATENCY = 15 SECONDS,
		STARTUP_STATE = ON
	);

ALTER EVENT SESSION [DeadlockMonitor] ON SERVER
	STATE = START;

Note that the database engine service account needs to be granted write access to the target location.

For ease of multi-server management, I recommend setting up a centralized file share to store all the output files. The full script was written with this in mind, and uses the server name as the file name automatically.

Security note: Deadlock graphs may contain sensitive information, such as data values in DML statements. The target location should be secured appropriately — consider using subfolders in the target path to separate by server if a single folder isn’t okay for your environment.

 

So, now that we’ve captured some deadlocks (yay?), how do we figure out what happened? Here is a first start. It reads the output and returns the event data (in this case, a deadlock graph) as XML. This script can be used for all types of Extended Event file targets, not just for deadlocks.

SELECT
	object_name,
	CAST(event_data AS xml) AS EventXml
	FROM
		sys.fn_xe_file_target_read_file
		(
			'.xel',
			'.xem', /* Required for 2008, optional for 2012 */
			NULL, NULL
		)

XPath can be used to further break things down, or the event data can be copied as text and saved as a .xdl file, which we can then open up in Management Studio to see the pretty deadlock graph. If you have a centralized management box, this script could be the basis of an Extended Events aggregation and reporting system. (Hopefully, you aren’t dealing with that many deadlocks, but this type of system could be used for other purposes.)

In a future post, we’ll look at how to analyze deadlock graphs.