• Adam Thurgar

Querying deadlock information XE

One of the problems with dealing with deadlocks is first of all you have to be able to capture them and have the right trace flags turned on (-T1222) and then you would need to read through the SQL Server error log and try to piece together all the node information.

There is another way. By default the system_health extended events has an event for xml_deadlock_report.

This means that SQL Server should be capturing the deadlock graph in XML.

Then you should be able to query the XEvent like this:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph


SELECT XEvent.query('.') AS XEvent


SELECT CAST(target_data AS XML) AS TargetData

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'

) AS Data

CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)

) AS source;


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t