Extract deadlock information

A deadlock happens when two or more processes block each other in a cycle while trying to access the same resources in the database. When that happens, SQL Server picks one of the processes as the victim and kills it, which lets the others get to the resources they were competing for.

When a deadlock occurs on a SQL Server, you need to diagnose what caused it.

SQL Server raises an event whenever a deadlock occurs and a victim is chosen. That event is automatically stored in an event session — a trace, if you prefer. The information it stores is called the deadlock graph, an XML representation of the situation. SQL Server Management Studio (SSMS) can display it as a diagram.

Once you have the deadlock graph, save it to a file with the .xdl extension. Double-clicking that file then opens SSMS with the graphical view of the deadlock.

system_health

The system_health Extended Events session is a built-in SQL Server feature that monitors system events, deadlock events among them. You can extract the deadlock graph from that session as follows.

Run this query to pull the deadlock events out of the system_health session:

(the query can take a while, depending on how much history the system_health session holds. Let it run — it does not block production queries.)

;WITH sh AS (
    SELECT
        timestamp_utc,
        CAST(event_data AS XML) AS eventdata
    FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
    WHERE object_name = 'xml_deadlock_report'
)
SELECT TOP 100
    CAST(timestamp_utc as datetime2(3)) as timestamp_utc,
    eventdata.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM sh
ORDER BY timestamp_utc DESC;

Click an XML value in the DeadlockGraph column to open it in its own window, then save the file with the .xdl extension.