Cannot run query to get deadlock graph in a timely fashion

Question

I am trying to get deadlock information from a sql-server by using this query

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'

The query however takes forever and returns an empty result.

Why does it take that long time and is the deadlock information you get from this views retroactive so I will be able to pinpoint a deadlock that occured some time ago?

Answer 1

Why Does It Take A Long Time?

You have to be quite careful when querying XML. Your query is a bit of a disaster in that regard, specifically this part: where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Which results in a whole bunch of XML being dragged around, joined, and much later filtered:

You're much better off using the exists method like so:

SELECT
    DeadlockGraph = 
        XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM
(
    SELECT
        TargetData = CONVERT(xml, st.target_data)
    FROM sys.dm_xe_session_targets AS st
    JOIN sys.dm_xe_sessions AS s
        ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
WHERE XEventData.XEvent.exist('/event/@name[ .= "xml_deadlock_report"]') = 1;

The XML filtering is still a bit awkward, but it happens much earlier on in the execution plan.

You may also find it beneficial to drop the converted XML into a #temp table first, so that validation of the XML structure only needs to occur once.

SELECT
    TargetData = 
        CONVERT(xml, st.target_data)
INTO #x
FROM sys.dm_xe_session_targets AS st
JOIN sys.dm_xe_sessions AS s
    ON s.address = st.event_session_address
WHERE s.name = 'system_health';

SELECT
    DeadlockGraph = 
        XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM
(
    SELECT
        TargetData
    FROM #x
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
WHERE XEventData.XEvent.exist('/event/@name[ .= "xml_deadlock_report"]') = 1;

If you'd like an alternative, you might find sp_BlitzLock useful.

Are Views Retroactive?

Only by luck. There are limits to the size of the data, and if there are a lot of events logged they may push useful deadlock data out. You are able to edit the session, as Aaron Betrand notes in this blog post.

If you are specifically interested in deadlocks, your best bet is to set up an extended event session dedicated to that.