Capturing Deadlocks

So, deadlocks.

“A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.”

http://msdn.microsoft.com/en-us/library/ms178104.aspx

How to capture deadlocks?

How many ways can you capture a deadlock? Lots! Luckily I do not see a lot of deadlocks in my environment, but that does not mean that I am not still looking for them. Remember it is always important to understand why they are happening and try to eliminate the root cause(s).

1. Trace flags

If we enable Trace Flags 1204 and/or 1222 we can get the output from the deadlock in our sql log. I prefer a nice image of the deadlock so I do not tend to use these trace flags, but they are there if you ever need to troubleshoot a new environment in a hurry. Although I tend to have to remind myself to read bottom to top when looking at them in the log.

DBCC TRACEON (1204,1222,-1)
GO
DBCC TRACESTATUS(-1);--Show global trace flags
GO

2a. Profiler Trace
A Profiler Trace can easily be set up using the the gui to capture the deadlocks and write them out to files, and as long as we are quite selective then a trace can be a good way to capture them.
2b. Manual Profiler Trace with t-sql
Not really different to the above, just that we can set up a trace programmatically as I have shown previously:
--http://jongurgul.com/blog/capturing-deadlocks/
DECLARE @on BIT
SET @on=1
DECLARE @maxsize BIGINT,@tracefile NVARCHAR(256);
SET @maxsize = 20
SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\DeadLock' FROM sys.traces WHERE [is_default] = 1) -- We shall use the path of the default trace
SELECT @tracefile
DECLARE @trace_id INT
EXEC sp_trace_create @trace_id output,2,@tracefile ,@maxsize --The 2 means the file will roll over

EXEC sp_trace_setevent @trace_id,148,1,@on --Deadlock graph: TextData
EXEC sp_trace_setevent @trace_id,148,4,@on --Deadlock graph: TransactionID
EXEC sp_trace_setevent @trace_id,148,11,@on --Deadlock graph: LoginName
EXEC sp_trace_setevent @trace_id,148,12,@on --Deadlock graph: SPID
EXEC sp_trace_setevent @trace_id,148,14,@on --Deadlock graph: StartTime
EXEC sp_trace_setevent @trace_id,148,26,@on --Deadlock graph: ServerName
EXEC sp_trace_setevent @trace_id,148,41,@on --Deadlock graph: LoginSid
EXEC sp_trace_setevent @trace_id,148,51,@on --Deadlock graph: EventSequence
EXEC sp_trace_setevent @trace_id,148,60,@on --Deadlock graph: IsSystem
EXEC sp_trace_setevent @trace_id,148,64,@on --Deadlock graph: SessionLoginName

EXEC sp_trace_setstatus  @trace_id,1 --Start the Trace

 3a. WMI with a SQL Alert
Easy enough to set this up and Microsoft have given a good example of how to use WMI and SQL Alerts. Worth a mention that this is reliant on the SQL job agent.
3b. WMI with a event watcher (Powershell)
Now I doubt you will ever use this next snippet, but I thought I would write it just to show how easy it is. Note that I am only waiting for the next deadlock, but you can easily change this if you wanted. Also the path needs to reflect the instance you wish to set this watcher up on. e.g. MSSQLSERVER is the default instance.
Depending on OS and your security requirements you may need to tweak permissions or run this as Admin.
See file Deadlock_WMISQLAlert.ps1
4. Using Service Broker and Event notifications
This is a good general solution as you can wait for the event you want and respond exactly how you would like.
One of the first solutions I found using this method even sends you an email with the deadlock as an attachment. Which I have in the past implemented on SQL 2005 instances and it works well, although I did have some problems with truncation of the deadlock xml.
http://www.resquel.com/ssb/2009/03/02/DeadlockAlertsThroughEventNotification.aspx
5. Using the default extended event system_health on SQL2008 onwards
My preferred solution with SQL 2008 onwards; and one which provides the detail without the extra overhead of wmi/trace is the default extended event “system health”, which can be queried via:
--http://jongurgul.com/blog/capturing-deadlocks/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] as XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('./data/value/deadlock') [Deadlock_SaveAs_File.xdl]
,x.y.value('(@timestamp)[1]', 'datetime') [DateTime]
FROM (SELECT @target_data) [deadlock]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="xml_deadlock_report"]') = 1
ORDER BY x.y.value('(@timestamp)[1]', 'datetime')

All I have to do is copy the xml in to a filename.xdl file and ssms will open it up for me. It is worth a mention that the target for this default extended event is the ring_buffer which will cycle out the older events, as new ones come in.
If you want to know more about extended events, or how to easily set new ones up then go and have a read of Jonathan Kehayias @sqlpoolboy article(s) here XEvent.
Also go and have a look at his great tool while you are at it  SQL Server 2008 Extended Events SSMS Addin
Which way will you choose?
Well if you are on a new SQL2008+ environment, then extended events is the way to go as it solves the problem and does so with practically no effort on your part.
If extended events are not available then you have to make a choice, which is basically do I want to run a trace or spend time writing something to intercept them. A trace is obviously easy to set up and if you have older environments it works no matter which version of SQL you are using.
Writing something to intercept them will require that you add dependencies to either service broker and event notifications, or sql alerts which relies on sql job agent and WMI. Plus WMI carries with it additional performance implications.