DBA Scripts: Get Deadlock Summary

Part of the DBA-Tools Project.

---

A deadlock is SQL Server’s way of resolving a situation it can’t otherwise escape: two transactions each holding a lock the other one needs, waiting on each other forever unless something intervenes. SQL Server intervenes automatically, picks one transaction as the victim, kills it, and lets the other proceed. The application sees an error and (if it’s written correctly) retries. If it isn’t, the failure just surfaces to a user with no explanation.

By default, nothing captures deadlock detail unless you go looking for it. This script pulls recent deadlock events straight from the system_health Extended Events session that runs on every SQL Server instance out of the box, no extra tracing needed.

---

Why Deadlock Summary Matters

Deadlocks are rarely a one-off; the same pair of statements deadlocking against each other usually means a consistent access pattern (different order of operations across two code paths) that will keep happening until someone changes the code or the indexing:

• Every deadlock victim’s transaction is rolled back entirely, and unless the caller retries, that work is lost
• A deadlock graph shows exactly which two statements were fighting over which resources, the starting point for an actual fix
• Deadlocks that go uninvestigated tend to recur, since nothing about the underlying access pattern changed
• The system_health session captures deadlocks automatically on every instance since SQL Server 2008; the data is usually already there, just unqueried

---

When to Run This Script

• Investigating an application error mentioning “deadlock” or error 1205
• Routine SQL Server health checks, to catch deadlocks nobody reported
• After a code or indexing change intended to reduce lock contention, to confirm it worked
• Reviewing a server you’ve just inherited, to see its recent deadlock history

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-DeadlockSummary
Category    : performance-troubleshooting
Purpose     : Show recent deadlock events from the system_health XEvent ring buffer.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-deadlock-summary/)
Requires    : VIEW SERVER STATE
Notes       : Ring buffer holds recent events only (typically last 30–60 min). For full
              history, query the system_health .xel files directly in SSMS.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

WITH ring_buffer AS (
    SELECT
        CAST(target_data AS XML) AS ring_xml
    FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions   AS s ON t.event_session_address = s.address
    WHERE s.name        = 'system_health'
      AND t.target_name = 'ring_buffer'
),
deadlock_nodes AS (
    SELECT
        e.x.value('@timestamp', 'datetime2')             AS event_timestamp,
        e.x.query('data[@name="xml_report"]/value')      AS deadlock_graph
    FROM ring_buffer
    CROSS APPLY ring_xml.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS e(x)
)
SELECT TOP 50
    event_timestamp,
    deadlock_graph
FROM deadlock_nodes
ORDER BY event_timestamp DESC;

The script shreds the system_health session’s ring buffer XML for xml_deadlock_report events and returns each one’s timestamp and full deadlock graph.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check for recent deadlocks:
.\run.ps1 Get-DeadlockSummary

# To run against a remote sql server:
.\run.ps1 Get-DeadlockSummary -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/performance/blocking-locking/Get-DeadlockSummary.sql
• powershell/wrappers/performance/blocking-locking/Get-DeadlockSummary.ps1

---

Example Output

To confirm this script against something real, two sessions were deliberately set up to update the same two rows in reverse order, a textbook deadlock trigger. SQL Server chose one as the victim and returned this, verbatim, from the client:

Transaction (Process ID 99) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

That’s the error the calling application sees. This script’s job is to turn that into the full picture: which two statements, on which resources, and which one lost. Each row it returns pairs an event_timestamp with the complete deadlock_graph XML, which includes both processes’ statements, wait times, and the resource they were fighting over.

A note from testing this on a busy instance: shredding the ring buffer can take a while to run if the instance has been up a long time or the system_health session has accumulated a lot of events; it’s XML parsing over however much the ring buffer currently holds. If it’s slow, that’s normal, not a sign anything is wrong.

---

Understanding the Results

• event_timestamp — when the deadlock happened. Cluster of timestamps close together often means a single burst of contention, not many independent problems.
• deadlock_graph — the full XML deadlock report. Open it in SSMS (which renders it as a graphical deadlock diagram) or read it directly for the process and resource-list nodes: each process shows the statement it was running and what it was waiting on.
• The ring buffer only holds recent events (roughly the last 30-60 minutes of activity, not a fixed count). For deadlock history further back, the system_health .xel files on disk hold more, queryable directly in SSMS.

---

Best Practices

• Treat a recurring deadlock on the same two statements as a design problem (access order, indexing, transaction scope), not something to just keep retrying past.
• Access tables in a consistent order across all code paths where possible; it’s the single most reliable way to prevent deadlocks by design.
• Keep transactions short and avoid user interaction or slow calls inside an open transaction, since longer transactions hold locks longer and raise deadlock odds.
• Confirm the application actually retries on error 1205. A deadlock without a retry is a failed operation, not just a delayed one.

---

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)

• Blocking Chains

• Blocking Chains with Plan
• Blocking Sessions
• Blocking Summary
• Contention Analysis
• Lock Escalation Stats
• Open Transactions

---

Frequently Asked Questions

Does SQL Server always pick the same session as the deadlock victim?

No. By default it picks based on rollback cost (the cheaper transaction to undo loses), unless SET DEADLOCK_PRIORITY has been explicitly set on one of the sessions to influence the choice.

Is a deadlock the same as blocking?

No, though they’re related. Blocking is one session waiting for another to release a lock, and it resolves on its own once the blocker finishes or times out. A deadlock is two sessions blocking each other in a cycle, which never resolves on its own; SQL Server has to intervene and kill one.

---

Summary

Deadlocks are one of the few SQL Server errors that come with a built-in explanation, if you know where to look for it. The system_health session has almost certainly already captured the detail; most of the work is just querying it.

Run this script whenever an application reports a deadlock error, and periodically as part of routine health checks to catch the ones nobody reported.