Capturing SQL Server Deadlock Graphs from system_health: A Real Diagnostic Walkthrough

You already know what a deadlock is. Here’s how to actually retrieve the deadlock graph after the fact — without having set up a trace in advance — because system_health is running by default on every SQL Server instance.

Pulling Deadlock Graphs You Never Explicitly Captured

SELECT CAST(event_data.value('(event/data/value)[1]', 'VARCHAR(MAX)') AS XML) AS deadlock_graph,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time
FROM (
    SELECT XEventData.query('.') AS event_data
    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 s.name = 'system_health' AND st.target_name = 'ring_buffer'
    ) AS Data
    CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEventData)
) AS tab(event_data);

Because system_health runs continuously by default, this query can retrieve deadlocks that happened before you even knew there was a problem — no advance trace setup required.

Reading the Graph

The victim-list element tells you which process SQL Server killed. Cross-reference the surviving process’s SQL text against the killed one’s — this is exactly how you confirm whether inconsistent access order (the classic cause) is really what happened.

Beyond Theory: A Deadlock Involving a Table Scan

Not every deadlock is the classic “two transactions, opposite order” case from Course 2. A single transaction doing a large table scan can deadlock against a small, targeted UPDATE if the scan acquires and holds shared locks across a wide range while the update needs an exclusive lock inside that range. The fix here isn’t reordering — it’s often reducing the scan’s lock footprint with a better index (tying directly back to Module 2 and 3).