DBAtlas · Database operations atlas

Read past deadlocks from the system_health session (deadlock graph)

SQL ServerPerformance

Command

SET QUOTED_IDENTIFIER ON;  -- required for XML methods (sqlcmd/Agent default is OFF)
WITH dl AS (
    SELECT CAST(event_data AS XML) AS x
    FROM   sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
    WHERE  object_name = N'xml_deadlock_report'
)
SELECT TOP (20)
       x.value('(event/@timestamp)[1]', 'datetime2')                                          AS utc_time,
       x.value('(event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(50)')  AS victim,
       x.value('count(event/data/value/deadlock/process-list/process)', 'int')                 AS process_count,
       x.value('(event/data/value/deadlock/process-list/process/@currentdbname)[1]', 'sysname') AS db,
       x.query('event/data/value/deadlock')                                                    AS deadlock_graph
FROM   dl
ORDER BY utc_time DESC;

What it does

Reads xml_deadlock_report events from the event_file target of the system_health Extended Events session (on by default); for each deadlock returns time (UTC), victim process, participant count, database and the full deadlock graph XML.

When to use it

When the application reports error 1205, when analysing deadlock frequency and the objects involved; on servers without trace flag 1222.

Caution

system_health files roll over; on a busy server older deadlocks may be gone, so set up a dedicated XE session for a permanent archive. Reading many files costs CPU; keep TOP during business hours. Events are written with a delay, so a new deadlock appears after a few seconds. Read-only; safe.

Open in DBAtlas →

Related