From Deadlock Graph to Repeat Offender: A Production Extended Events Pipeline

2026-07-06 · by Hannah Vernon · in extended-events

Detective pin-board linking repeat-offender stored procedures from SQL Server deadlock graphs

A developer messages you at 4:55 PM: “we got a deadlock error around lunchtime, can you tell me what happened?” You open SQL Server Management Studio, expand the system_health session, and start scrolling. The event is gone. system_health is a ring buffer, it rolled over hours ago, and the one graph you needed aged out somewhere around 2 PM. You have nothing to show for the question except a shrug.

This is the gap between knowing that SQL Server captures deadlocks for free and being able to answer a question about one a few hours later. The free capture is real, but it is transient, and it gives you one graph at a time with no way to ask “which procedures deadlock the most often this week?” This post builds the other thing: a small, dedicated Extended Events pipeline that captures every xml_deadlock_report, persists it into a table that survives memory pressure and log rotation, and then shreds the stored XML to rank the repeat offenders. It is the first post in a series on using Extended Events as real production diagnostics rather than a one-off troubleshooting trick.

Why the Free Deadlock Capture Is Not Enough

SQL Server already records deadlocks in two places without any setup from you. The old way was trace flag 1222, which writes the deadlock graph to the error log as text. The modern way is the system_health Extended Events session, which captures the xml_deadlock_report event and keeps it in a ring buffer.^[2] Both are genuinely useful, and for a single recent deadlock the system_health graph is often all you need.

The trouble is retention and aggregation. The system_health ring buffer is small and shared with every other event it collects, so on a busy instance a deadlock graph can age out in hours. Even while it is there, you get one event at a time. There is no natural way to ask the questions that actually drive remediation: which two procedures keep deadlocking against each other, how often, and at what times of day. To answer those you need history you control and a shape you can aggregate. That means your own session, your own table, and a scheduled job that moves events from the first into the second before they disappear.

A Dedicated Deadlock Session

The first piece is an Extended Events session that captures only xml_deadlock_report, with a few useful actions attached so you know which application and host were involved. It writes to a ring_buffer target. The ring buffer has well-known sharp edges, and the canonical write-up of them is Jonathan Kehayias’ “Why I hate the ring_buffer target,” which is worth reading before you lean on it.^[1] The short version is that the ring buffer can silently truncate its XML output and that you should keep it small and harvest it often. Both of those are baked into what follows: the target is capped, and a job drains it every few minutes.

/* Turn on the blocked process threshold while we are here; it costs nothing
   and feeds the blocked-process session in part two of this series. */
IF
(
    SELECT
        [c].[value_in_use]
    FROM
        [sys].[configurations] AS [c]
    WHERE
        [c].[name] = N'show advanced options'
) = 0
BEGIN
    EXEC [sys].[sp_configure] @configname = N'show advanced options', @configvalue = N'1';
    RECONFIGURE;
END;

IF NOT EXISTS
(
    /* Only create the session if it does not already exist, so re-running
       this script never drops a running session and loses its events. */
    SELECT
        1
    FROM
        [sys].[server_event_sessions] AS [ss]
    WHERE
        [ss].[name] = N'deadlocks'
)
BEGIN
    CREATE EVENT SESSION [deadlocks]
    ON SERVER
    ADD EVENT [sqlserver].[xml_deadlock_report]
    (
        ACTION
        (
              [sqlserver].[client_app_name]
            , [sqlserver].[client_hostname]
            , [sqlserver].[database_name]
        )
    )
    ADD TARGET [package0].[ring_buffer]
    (
        SET
              max_memory        = 2048   /* KB.  2048 is the recommended cap to avoid XML truncation. */
            , occurrence_number = 0
            , max_events_limit  = 0      /* 0 = unbounded; we drain it on a schedule instead. */
    )
    WITH
    (
          MAX_MEMORY            = 10 MB
        , EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS
        , MAX_DISPATCH_LATENCY  = 5 SECONDS
        , STARTUP_STATE         = ON
    );
END;

/* A running session shows up in sys.dm_xe_sessions; start it if it is not already. */
IF NOT EXISTS
(
    SELECT
        1
    FROM
        [sys].[dm_xe_sessions] AS [xs]
    WHERE
        [xs].[name] = N'deadlocks'
)
BEGIN
    ALTER EVENT SESSION [deadlocks]
    ON SERVER
    STATE = START;
END;

/* Turn on the blocked process threshold while we are here; it costs nothing

and feeds the blocked-process session in part two of this series. */

(

SELECT

[c].[value_in_use]

FROM

[sys].[configurations] AS [c]

WHERE

[c].[name] = N'show advanced options'

) = 0

BEGIN

EXEC [sys].[sp_configure] @configname = N'show advanced options', @configvalue = N'1';

RECONFIGURE;

END;

IF NOT EXISTS

(

/* Only create the session if it does not already exist, so re-running

this script never drops a running session and loses its events. */

SELECT

FROM

[sys].[server_event_sessions] AS [ss]

WHERE

[ss].[name] = N'deadlocks'

)

BEGIN

CREATE EVENT SESSION [deadlocks]

ON SERVER

ADD EVENT [sqlserver].[xml_deadlock_report]

(

ACTION

(

[sqlserver].[client_app_name]

, [sqlserver].[client_hostname]

, [sqlserver].[database_name]

)

ADD TARGET [package0].[ring_buffer]

(

SET

max_memory = 2048 /* KB. 2048 is the recommended cap to avoid XML truncation. */

, occurrence_number = 0

, max_events_limit = 0 /* 0 = unbounded; we drain it on a schedule instead. */

)

WITH

(

MAX_MEMORY = 10 MB

, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 5 SECONDS

, STARTUP_STATE = ON

);

END;

/* A running session shows up in sys.dm_xe_sessions; start it if it is not already. */

IF NOT EXISTS

(

SELECT

FROM

[sys].[dm_xe_sessions] AS [xs]

WHERE

[xs].[name] = N'deadlocks'

)

BEGIN

ALTER EVENT SESSION [deadlocks]

ON SERVER

STATE = START;

END;

STARTUP_STATE = ON is the part people forget. Without it, the session stops collecting the moment the SQL Server service restarts and you discover the gap only when you go looking for an event that was never captured. With it on, the session restarts with the instance.^[3]

Persisting Past the Ring Buffer

The ring buffer holds events in memory. To keep them, we shred the target XML into a permanent table on a schedule and only insert events we have not already stored. I put the table and the harvesting procedure in a dedicated DBA utility database so they are not tangled up with any application schema.

The harvesting procedure pulls the ring buffer’s target_data, breaks it into one row per event, extracts the timestamp and the victim process id as a natural key, and inserts only the new rows. A detail worth calling out: the procedure must read from the same session it is draining. If you adapt this from another script, check the session name in the WHERE clause, it is an easy copy-and-paste error to leave it pointing at a different session.

IF DB_ID(N'DBA') IS NULL
BEGIN
    CREATE DATABASE [DBA];
    ALTER DATABASE [DBA] SET RECOVERY SIMPLE;
END;
GO

USE [DBA];
GO

CREATE OR ALTER PROCEDURE [dbo].[GatherDeadlockEvents]
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    IF OBJECT_ID(N'dbo.deadlock_xml_events', N'U') IS NULL
    BEGIN
        CREATE TABLE [dbo].[deadlock_xml_events]
        (
              [xeTimeStamp] datetimeoffset(7) NOT NULL
            , [xeProcessID] varchar(20)       NOT NULL
            , [xeXML]       xml               NOT NULL
            , CONSTRAINT [deadlock_xml_events_pk]
                PRIMARY KEY CLUSTERED ([xeTimeStamp], [xeProcessID])
        );
    END;

    DECLARE @target_data xml;

    SELECT
        @target_data = CONVERT(xml, [t].[target_data])
    FROM
        [sys].[dm_xe_sessions] AS [s]
        INNER JOIN [sys].[dm_xe_session_targets] AS [t]
            ON [t].[event_session_address] = [s].[address]
    WHERE
        [s].[name] = N'deadlocks'        /* read the SAME session we are draining */
        AND [t].[target_name] = N'ring_buffer';

    ;WITH [src] AS
    (
        SELECT
            [xeXML] = [xm].[s].query('.')
        FROM
            @target_data.nodes('/RingBufferTarget/event') AS [xm]([s])
    )
    INSERT INTO [dbo].[deadlock_xml_events]
    (
          [xeProcessID]
        , [xeTimeStamp]
        , [xeXML]
    )
    SELECT
          [xeProcessID] = [src].[xeXML].value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)')
        , [xeTimeStamp] = [src].[xeXML].value('(/event/@timestamp)[1]', 'datetimeoffset(7)')
        , [src].[xeXML]
    FROM
        [src]
    WHERE
        [src].[xeXML].value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)') IS NOT NULL
        AND NOT EXISTS
        (
            SELECT
                1
            FROM
                [dbo].[deadlock_xml_events] AS [dxe]
            WHERE
                [dxe].[xeTimeStamp] = [src].[xeXML].value('(/event/@timestamp)[1]', 'datetimeoffset(7)')
                AND [dxe].[xeProcessID] = [src].[xeXML].value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)')
        );
END;
GO

IF DB_ID(N'DBA') IS NULL

BEGIN

CREATE DATABASE [DBA];

ALTER DATABASE [DBA] SET RECOVERY SIMPLE;

END;

USE [DBA];

CREATE OR ALTER PROCEDURE [dbo].[GatherDeadlockEvents]

BEGIN

SET XACT_ABORT, NOCOUNT ON;

IF OBJECT_ID(N'dbo.deadlock_xml_events', N'U') IS NULL

BEGIN

CREATE TABLE [dbo].[deadlock_xml_events]

(

[xeTimeStamp] datetimeoffset(7) NOT NULL

, [xeProcessID] varchar(20) NOT NULL

, [xeXML] xml NOT NULL

, CONSTRAINT [deadlock_xml_events_pk]

PRIMARY KEY CLUSTERED ([xeTimeStamp], [xeProcessID])

);

END;

DECLARE @target_data xml;

SELECT

@target_data = CONVERT(xml, [t].[target_data])

FROM

[sys].[dm_xe_sessions] AS [s]

INNER JOIN [sys].[dm_xe_session_targets] AS [t]

ON [t].[event_session_address] = [s].[address]

WHERE

[s].[name] = N'deadlocks' /* read the SAME session we are draining */

AND [t].[target_name] = N'ring_buffer';

;WITH [src] AS

(

SELECT

[xeXML] = [xm].[s].query('.')

FROM

@target_data.nodes('/RingBufferTarget/event') AS [xm]([s])

)

INSERT INTO [dbo].[deadlock_xml_events]

(

[xeProcessID]

, [xeTimeStamp]

, [xeXML]

)

SELECT

[xeProcessID] = [src].[xeXML].value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)')

, [xeTimeStamp] = [src].[xeXML].value('(/event/@timestamp)[1]', 'datetimeoffset(7)')

, [src].[xeXML]

FROM

[src]

WHERE

[src].[xeXML].value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)') IS NOT NULL

AND NOT EXISTS

(

SELECT

FROM

[dbo].[deadlock_xml_events] AS [dxe]

WHERE

[dxe].[xeTimeStamp] = [src].[xeXML].value('(/event/@timestamp)[1]', 'datetimeoffset(7)')

AND [dxe].[xeProcessID] = [src].[xeXML].value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)')

);

END;

The NOT EXISTS check on the timestamp plus victim id is what makes the job safe to run as often as you like. Re-harvesting the same ring buffer never produces duplicate rows, so there is no need to stop and restart the session to “reset” it between runs. The natural key also keeps the table honest if two deadlocks land in the same second.

Running It on a Schedule

A capture you have to remember to run is a capture you will not have when you need it. Point a SQL Server Agent job at the procedure and let it run every few minutes. Five minutes is a reasonable default: frequent enough that a small, capped ring buffer never overflows between runs, infrequent enough to be invisible on the instance.

USE [msdb];
GO

IF NOT EXISTS
(
    SELECT
        1
    FROM
        [msdb].[dbo].[sysjobs] AS [sj]
    WHERE
        [sj].[name] = N'DBA : Gather Deadlock Events'
)
BEGIN
    DECLARE @JobID      uniqueidentifier;
    DECLARE @ScheduleID int;

    EXEC [msdb].[dbo].[sp_add_job]
          @job_name    = N'DBA : Gather Deadlock Events'
        , @enabled     = 1
        , @description  = N'Drains the deadlocks Extended Events session into DBA.dbo.deadlock_xml_events.'
        , @job_id      = @JobID OUTPUT;

    EXEC [msdb].[dbo].[sp_add_jobstep]
          @job_id           = @JobID
        , @step_id          = 1
        , @step_name        = N'Gather Deadlocks'
        , @subsystem        = N'TSQL'
        , @command          = N'EXEC [dbo].[GatherDeadlockEvents];'
        , @database_name    = N'DBA'
        , @on_success_action = 1
        , @on_fail_action    = 2;

    EXEC [msdb].[dbo].[sp_add_schedule]
          @schedule_name        = N'DBA : Gather Deadlock Events Schedule'
        , @enabled              = 1
        , @freq_type            = 4      /* daily */
        , @freq_interval        = 1
        , @freq_subday_type     = 0x04   /* minutes */
        , @freq_subday_interval = 5
        , @schedule_id          = @ScheduleID OUTPUT;

    EXEC [msdb].[dbo].[sp_attach_schedule] @job_id = @JobID, @schedule_id = @ScheduleID;
    EXEC [msdb].[dbo].[sp_add_jobserver]  @job_id = @JobID, @server_name = N'(local)';
END;
GO

USE [msdb];

IF NOT EXISTS

(

SELECT

FROM

[msdb].[dbo].[sysjobs] AS [sj]

WHERE

[sj].[name] = N'DBA : Gather Deadlock Events'

)

BEGIN

DECLARE @JobID uniqueidentifier;

DECLARE @ScheduleID int;

EXEC [msdb].[dbo].[sp_add_job]

@job_name = N'DBA : Gather Deadlock Events'

, @enabled = 1

, @description = N'Drains the deadlocks Extended Events session into DBA.dbo.deadlock_xml_events.'

, @job_id = @JobID OUTPUT;

EXEC [msdb].[dbo].[sp_add_jobstep]

@job_id = @JobID

, @step_id = 1

, @step_name = N'Gather Deadlocks'

, @subsystem = N'TSQL'

, @command = N'EXEC [dbo].[GatherDeadlockEvents];'

, @database_name = N'DBA'

, @on_success_action = 1

, @on_fail_action = 2;

EXEC [msdb].[dbo].[sp_add_schedule]

@schedule_name = N'DBA : Gather Deadlock Events Schedule'

, @enabled = 1

, @freq_type = 4 /* daily */

, @freq_interval = 1

, @freq_subday_type = 0x04 /* minutes */

, @freq_subday_interval = 5

, @schedule_id = @ScheduleID OUTPUT;

EXEC [msdb].[dbo].[sp_attach_schedule] @job_id = @JobID, @schedule_id = @ScheduleID;

EXEC [msdb].[dbo].[sp_add_jobserver] @job_id = @JobID, @server_name = N'(local)';

END;

From here the table fills itself. Every deadlock the instance throws lands in deadlock_xml_events within five minutes and stays there until you decide to clean it out, which is the foundation everything else stands on.

Finding the Repeat Offenders

Storing the graphs is only half the value. The reason to have history in a table is to aggregate it, and the most useful aggregate for deadlocks is a frequency ranking of the procedures involved. A single deadlock graph tells you which two statements collided once. A count over a week tells you where to spend your time.

The deadlock XML records each participant’s execution stack, and the procname attribute on each frame names the procedure running at that point. Pulling the procedure for each side of the deadlock and grouping gives you the pairs that collide most often:

USE [DBA];
GO

DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -168, SYSUTCDATETIME());  /* last 7 days */
DECLARE @EndTime   datetimeoffset = SYSUTCDATETIME();
DECLARE @timezone  sysname        = N'Central Standard Time';

;WITH [src] AS
(
    SELECT
          [side_1] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
        , [side_2] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
    FROM
        [dbo].[deadlock_xml_events] AS [x]
    WHERE
        [x].[xeXML].exist('/event[@name="xml_deadlock_report"]') = 1
        AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
        AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1
)
SELECT
      [src].[side_1]
    , [src].[side_2]
    , [Number of Deadlocks] = COUNT(1)
FROM
    [src]
GROUP BY
      [src].[side_1]
    , [src].[side_2]
ORDER BY
    [Number of Deadlocks] DESC;

USE [DBA];

DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -168, SYSUTCDATETIME()); /* last 7 days */

DECLARE @EndTime datetimeoffset = SYSUTCDATETIME();

DECLARE @timezone sysname = N'Central Standard Time';

;WITH [src] AS

(

SELECT

[side_1] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')

, [side_2] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')

FROM

[dbo].[deadlock_xml_events] AS [x]

WHERE

[x].[xeXML].exist('/event[@name="xml_deadlock_report"]') = 1

AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1

AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1

)

SELECT

[src].[side_1]

, [src].[side_2]

, [Number of Deadlocks] = COUNT(1)

FROM

[src]

GROUP BY

[src].[side_1]

, [src].[side_2]

ORDER BY

[Number of Deadlocks] DESC;

That answers “which pairs,” but a deadlock can involve more than two processes, and a procedure that shows up across many different pairings is its own kind of problem. To rank individual procedures regardless of who they collided with, pull every frame’s procname and unpivot them into a single column, then count. The deadlock graph can carry a deep execution stack, so the real script reaches across many frames; this trimmed version reads the first five to show the shape:

;WITH [src] AS
(
    SELECT
          [1] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
        , [2] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
        , [3] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'nvarchar(128)')
        , [4] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'nvarchar(128)')
        , [5] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[5]', 'nvarchar(128)')
    FROM
        [dbo].[deadlock_xml_events] AS [x]
    WHERE
        [x].[xeXML].exist('/event[@name="xml_deadlock_report"]') = 1
        AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
        AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1
)
SELECT
      [ProcName] = [t].[x]
    , [Count]    = COUNT(1)
FROM
    [src]
    UNPIVOT
    (
        [x] FOR [ProcName] IN ([1], [2], [3], [4], [5])
    ) AS [t]
GROUP BY
    [t].[x]
ORDER BY
    [Count] DESC;

;WITH [src] AS

(

SELECT

[1] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')

, [2] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')

, [3] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'nvarchar(128)')

, [4] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'nvarchar(128)')

, [5] = [x].[xeXML].value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[5]', 'nvarchar(128)')

FROM

[dbo].[deadlock_xml_events] AS [x]

WHERE

[x].[xeXML].exist('/event[@name="xml_deadlock_report"]') = 1

AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1

AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1

)

SELECT

[ProcName] = [t].[x]

, [Count] = COUNT(1)

FROM

[src]

UNPIVOT

(

[x] FOR [ProcName] IN ([1], [2], [3], [4], [5])

) AS [t]

GROUP BY

[t].[x]

ORDER BY

[Count] DESC;

I keep both of these inside a dbo.AnalyzeDeadlockEvents procedure that takes a “hours back” parameter and a time zone, so reviewing the last shift or the last week is a single call. The time filtering uses the XML exist() method with sql:variable, which lets SQL Server evaluate the timestamp predicate inside the XML rather than shredding every row first.

Reading the Output

Run against a table with a week of history, the repeat-offender ranking comes back as a plain frequency list. These procedure names are illustrative:

ProcName                     Count
---------------------------- -----
dbo.ApplyInventoryAdjustment    41
dbo.PostInvoice                 38
dbo.ReserveStock                33
dbo.UpdateOrderStatus            7
dbo.RecalculateAccountBalance    2

ProcName Count

---------------------------- -----

dbo.ApplyInventoryAdjustment 41

dbo.PostInvoice 38

dbo.ReserveStock 33

dbo.UpdateOrderStatus 7

dbo.RecalculateAccountBalance 2

That is a different conversation than “we had a deadlock at lunch.” The top three procedures account for the overwhelming majority of the collisions, they all touch inventory and ordering, and they are almost certainly taking the same tables in a different order. That is an actionable finding: a consistent locking order across those three procedures, or a covering index that shortens the window they hold their locks, will move the number more than anything you could learn from a single graph. The point of the pipeline is that it turns an anecdote into a ranked list you can act on.

Caveats Worth Stating

The ring buffer can truncate. This is the heart of Jonathan Kehayias’ objection to the target. Keeping max_memory at 2048 KB and draining every five minutes keeps you well clear of the truncation point for deadlock graphs, but it is the reason the harvest job runs often rather than hourly.^[1]
A service restart empties the ring buffer. Anything not yet harvested when the instance restarts is gone. The five-minute cadence bounds that loss to a few minutes; if that is unacceptable, an event_file target is the more durable choice, at the cost of a little more handling.
AT TIME ZONE needs SQL Server 2016 or later. The analysis procedure converts the stored datetimeoffset timestamps to local time with AT TIME ZONE. On older versions, store and report in UTC instead.^[4]
The victim is not the cause. The pipeline keys on the victim process id because it is a stable identifier, but the procedure SQL Server chose to roll back is whichever was cheapest to kill, not necessarily the one at fault. Read both sides of the graph before assigning blame.
This is not free forever. Add a retention step that deletes rows older than your review window so deadlock_xml_events does not grow without bound.

The Takeaway

The free deadlock capture in system_health is fine for answering “what just happened.” It is no good for “what keeps happening,” because it does not retain history and it does not aggregate. A dedicated session, a harvesting procedure that idempotently shreds the ring buffer into a table, an Agent job that runs it every few minutes, and a couple of analysis queries turn deadlocks from a stream of disappearing anecdotes into a ranked, queryable record of your worst offenders. Build it once and the next time someone asks about a deadlock from earlier in the day, the answer is a query, not a shrug.

Do you keep deadlock history, or chase each one as it happens? If you have a different way of ranking the repeat offenders, I would like to hear it. Find me on Bluesky or LinkedIn.

If you want the background on deadlock detection itself before tackling the pipeline, I covered the basics earlier in Deadlock detection and analysis and the worked example deadlock code. This post is the production-grade version of that idea: capture once, keep the history, rank the offenders.

References

Why I hate the ring_buffer target in Extended Events – Jonathan Kehayias, SQLskills. The definitive write-up of the ring buffer target’s XML truncation behaviour and why you keep it small and harvest it often. ↩
Use the system_health session – Microsoft Learn. What the built-in session captures, including xml_deadlock_report, and the ring buffer it stores it in. ↩
CREATE EVENT SESSION (Transact-SQL) – Microsoft Learn. Session, target, and option syntax, including STARTUP_STATE and the ring_buffer target settings. ↩
AT TIME ZONE (Transact-SQL) – Microsoft Learn. The time zone conversion used in the analysis procedure; available in SQL Server 2016 and later. ↩

Tags: agent jobs, deadlocks, Diagnostics, Extended Events, Ring Buffer, SQL Server, t-sql, troubleshooting

From Deadlock Graph to Repeat Offender: A Production Extended Events Pipeline

Why the Free Deadlock Capture Is Not Enough

A Dedicated Deadlock Session

Persisting Past the Ring Buffer

Running It on a Schedule

Finding the Repeat Offenders

Reading the Output

Caveats Worth Stating

The Takeaway

More in this series

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

From Deadlock Graph to Repeat Offender: A Production Extended Events Pipeline

Why the Free Deadlock Capture Is Not Enough

A Dedicated Deadlock Session

Persisting Past the Ring Buffer

Running It on a Schedule

Finding the Repeat Offenders

Reading the Output

Caveats Worth Stating

The Takeaway

More in this series

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta