Background

The days of restarting SQL Service with trace flags 1204 or 1222 (MSDN Detecting and Ending Deadlocks) or being forced to start up a profiler trace are no more.

Let’s face it. There are times when someone comes up to you and says, “hey we got a handful of deadlocks an hour ago can you tell me how it happened?” Now, if you are practicing pro-active DBA methods then you might already have this information but if you are left with the deer in the head-lights look then these queries might be for you.

Extended Events was new with SQL Server 2008 and by default captures xml deadlock reports. For information about Extended Events see Pinal Dave’s post here or you can read the white paper here.

The Code

The comments on the queries below are fairly self explanatory. Running the first query will return a single record with a lot of improperly formatted xml. This query is given because it is very fast. All you need to do then is paste it into notepad and search for xml_deadlock_report to find the deadlock graph xml. I provided the bottom query for use when you come across the sql handles in the xml to identify the queries that were being run.

The middle query is the best of the bunch here but can be very slow. This one will return a row per deadlock graph in properly formatted xml. Must easier to work with.

In both cases the most recent deadlock events will be at the bottom of the result set or the bottom of the xml code block copied out from query one.

[sql]– This query is fast but the xml is not formatted properly so you
— need to paste the result in notepad and search for xml_deadlock_report.
select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = ‘system_health’

— This query gives you a record per deadlock graph with xml corrected
— but this query is VERY slow. There is no server performance impact
— but you should expect this query to run for 30 mins +
select XEventData.XEvent.value(‘(data/value)[1]’, ‘varchar(max)’) as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = ‘system_health’) AS Data
CROSS APPLY TargetData.nodes (‘//RingBufferTarget/event’) AS XEventData (XEvent)
where XEventData.XEvent.value(‘@name’, ‘varchar(4000)’) = ‘xml_deadlock_report’

/*For viewing the sql handles*/
SELECT * FROM sys.dm_exec_sql_text(0x03001a00ea93ed5bed752b0150a100000100000000000000);[/sql]

Credits: (edited – 2013-02-13)

Many thanks to Jonathan Kehayias for authoring these scripts and providing an informative article here on sqlservercentral.com.