---
title: "SQL Server Deadlock Analyzer"
description: "SQL Server deadlock analyzer: open .xdl/XML reports or read retained system_health deadlocks from a connection; see victim, locks and owner/waiter graph."
url: "https://jamsql.com/docs/deadlock-analyzer/"
html_url: "https://jamsql.com/docs/deadlock-analyzer/"
generated: "2026-09-20T00:14:45.064Z"
---

Last updated: 2026-09-05

# SQL Server Deadlock Analyzer

Open or paste a SQL Server deadlock report, or read the deadlocks SQL Server already retained in its `system_health` session from a connection, and inspect the victim, the participating processes, the locked resources and the owner/waiter relationships between them.

## What it does

SQL Server records a deadlock as an XML document (an `.xdl` file saved from SSMS, the `xml_deadlock_report` event from the `system_health` Extended Events session, or a `<deadlock-list>` from trace flag 1222 output). The Deadlock Analyzer turns that XML into a graph and a set of tables you can read: which process was killed, what each process was running, which lock each one held and which lock each one waited for.

There are two ways to get a report into the analyzer: import a file or pasted XML (no connection needed), or pick a SQL Server connection and let Jam read the `xml_deadlock_report` events that the built-in `system_health` Extended Events session already retained. The second path is read-only: Jam never creates, alters or starts a session and never grants permissions.

## How to open the analyzer

-   **Main toolbar:** **More > Deadlock Analyzer** (in the Open file group, next to Open Execution Plan). No connection is required.
-   **Command palette:** press Ctrl+Shift+P and run **Deadlock Analyzer**.
-   **Object Explorer:** right-click a SQL Server connection and choose **Deadlock Analyzer**. The tab opens in collector mode for that connection (see [Reading retained deadlocks from a connection](#collector)).
-   **From connection…** in the analyzer toolbar switches any analyzer tab to collector mode for one of your SQL Server connections.

No install needed for a one-off report: the [free browser deadlock viewer](/tools/deadlock-viewer/) uses the same parser and reads .xdl / XML files locally in the browser.

## Importing a report

1.  In the analyzer tab, click **Open XML/XDL…** and pick a `.xdl` or `.xml` file, or click **Paste XML** and paste the report text.
2.  The report is parsed in a background worker. The status line at the right of the toolbar shows the source, the number of incidents, the detected encoding and the size.
3.  Click **Load sample** at any time to see a synthetic two-process key-lock deadlock.
4.  **Clear** removes the report from the tab so you can import another one.

[Screenshot: deadlock-analyzer-graph.png]

## Reading the graph

Processes are drawn on the left, resources on the right. Every edge comes from an `<owner>` or `<waiter>` entry in the report:

-   **Owner: X** (solid, resource → process) — the process holds the lock in the given mode.
-   **Request: U** (dashed, process → resource) — the process is waiting for the lock in the given mode. A waiter with `requestType="convert"` is labelled **Convert**: the process already owns the lock and wants a stronger mode.
-   The **Victim** badge marks the process SQL Server rolled back; its request edge is drawn in red.

In the sample, SPID 62 owns a U lock on `SalesDb.dbo.Inventory` and requests a U lock on `SalesDb.dbo.Orders`, while SPID 71 owns the X lock on `Orders` and requests an X lock on `Inventory`. Neither can proceed, so SQL Server picks SPID 62 — the one with less log written — as the victim.

Graph identities are the report's process ids (for example `process24ab8c8`), not SPIDs. Parallel workers of one query share a SPID but have different ECIDs, so each worker is its own node and the card shows *SPID 70 / ECID 3*.

## Details pane

The pane on the right has four tabs:

-   **Details** — with nothing selected, an overview of the incident: victim, warnings, a table of processes (status, lock mode, wait resource, login) and a table of resources (kind, object, mode, owners, waiters). Click or press Enter on a row to select that node. With a node selected, every raw attribute of the process or resource is listed, including attributes the analyzer does not interpret, plus one table per owner and waiter entry.
-   **Execution stack** — the selected process's `<frame>` entries: procedure name, line, statement offsets and the statement text.
-   **Input buffer** — the batch the client sent (`<inputbuf>`).
-   **Raw XML** — the selected incident's own `<deadlock>` element as text.

[Screenshot: deadlock-analyzer-details.png]

### Derived blocking view

Below the overview tables the analyzer lists a derived *"process A waits on process B"* summary. It is computed only from lock resources whose owner and waiter lists are fully interpreted (key, page, RID, object, HoBt, allocation-unit, extent, file, metadata, database and application locks), is cut at 5,000 owner/waiter pairs (the graph edges themselves are never cut), it skips a process waiting on itself (a lock conversion), and it is labelled as derived because the report itself only proves the process↔resource relationships.

## Multiple incidents

A `<deadlock-list>` or an Extended Events export with several `xml_deadlock_report` events opens with an incident list on the left. Each entry shows the event timestamp when the wrapper carries one, the victim, and the process/resource counts. Selecting an incident switches the graph, the tables and the Raw XML tab. Reports with more than 500 incidents are refused rather than truncated; split the export first.

[Screenshot: deadlock-analyzer-incidents.png]

## Partial interpretation

Resource kinds the analyzer does not model as locks — `exchangeEvent` (parallelism), `threadpool`, and any element it does not recognise — are drawn with a dashed border and a **Partial** badge. Their owner and waiter entries are still shown verbatim and their attributes are listed, but no blocking relationship is derived from them. Missing optional fields (no victim, a resource without an `id`, an owner referencing a process that is not in the process list) never prevent the report from opening; each case is listed under *warnings* in the overview.

## Supported input, encodings and limits

-   **Wrappers:** `<deadlock>`, `<deadlock-list>`, a single `<event name="xml_deadlock_report">`, and `<RingBufferTarget>` / `<events>` lists of such events.
-   **Encodings:** UTF-8 with or without a byte-order mark, UTF-16 LE and UTF-16 BE (detected from the BOM or the leading `<` code unit).
-   **Size:** 10 MiB per file or paste. Larger input is rejected with a message; nothing is silently cut.
-   **Structure:** at most 64 nesting levels, 250,000 XML elements and 500 incidents.
-   **Not supported locally:** `.xel` is the binary Extended Events file format, not XML. Opening one shows an explanation; export the events as XML/XDL first (for example from SSMS's Extended Events viewer).

## Reading retained deadlocks from a connection

SQL Server and Azure SQL Managed Instance ship with the `system_health` Extended Events session, which starts with the engine and records every detected deadlock as an `xml_deadlock_report` event in two targets: a set of `.xel` event files and an in-memory ring buffer. The collector reads those retained events over your existing connection, so the first deadlock you look at needs no extra session, no SSMS and no file transfer.

1.  Right-click the connection in Object Explorer and choose **Deadlock Analyzer**, or click **From connection…** in an open analyzer tab and pick the connection.
2.  The collector panel under the toolbar shows the connection, the effective service (SQL Server, Azure SQL Edge, Azure SQL Managed Instance or Azure SQL Database), the session state and its targets once a read has run. Nothing is read until you click **Refresh**.
3.  Choose the time window (**Last 24 hours**, **Last 7 days** or **Custom**) and optionally a database, then click **Refresh**. Events arrive newest first; each one is parsed by the same parser the import path uses and appears in the incident list with its UTC timestamp.
4.  Select an incident to see the same graph, tables, execution stacks, input buffers and raw XML as for an imported report.

[Screenshot: deadlock-analyzer-collector.png]

### Supported services

Service

Collector

Notes

SQL Server 2016 and later (on-premises, VM, Linux containers)

Yes

Reads the `system_health*.xel` rollover files first, ring buffer as fallback. Verified in this build against SQL Server 2025 Developer Edition on Linux (a real seeded deadlock read back through the collector).

Azure SQL Edge

Expected to work, not verified

Reports `EngineEdition` 9 and ships the same `system_health` session; Jam treats it like SQL Server. Not run against a live Edge container in this build.

SQL Server 2012–2014

Expected to work, not verified

`sys.fn_xe_file_target_read_file` has no `timestamp_utc` column before SQL Server 2017, so Jam filters on the event's own `timestamp` attribute instead (a slower query). We have not run the collector against a 2012–2014 instance; treat it as unverified.

Azure SQL Managed Instance

Documented, not verified by us

Microsoft documents that the built-in `system_health` files can be read with the plain local name `system_health*.xel` without a storage credential, and Jam uses exactly that name when it detects `EngineEdition` 8. We have not run the collector against a live Managed Instance yet; treat it as unverified.

Azure SQL Database

No — import only

There is no built-in `system_health` session in Azure SQL Database. Jam detects `EngineEdition` 5, explains this and offers import. Capture `xml_deadlock_report` with your own Extended Events session (event file in Azure Storage or ring buffer), export the XML and open it here.

### Permissions

The collector uses `sys.dm_xe_sessions`, `sys.dm_xe_session_targets`, `sys.server_event_sessions` and `sys.fn_xe_file_target_read_file`. When the server denies one of them (errors 297, 229 or 300) the panel names the object that failed and the permission it needs on your version:

-   **SQL Server 2019 and earlier:** `VIEW SERVER STATE`.
-   **SQL Server 2022 and later:** `VIEW SERVER PERFORMANCE STATE` (the broader `VIEW SERVER STATE` also works).
-   **Azure SQL Managed Instance:** `VIEW SERVER STATE`.

Jam never grants permissions or changes server settings; ask a sysadmin for the grant and click Refresh again.

### Filters

-   **Time** is applied on the server in UTC. The panel shows the queried window with UTC boundaries (`2026-09-05 08:41Z`) and, separately, the same instants in your local time; incident timestamps in the list are shown in local time. Custom boundaries are entered in local time and stored as UTC.
-   **Database** is applied after the read. An incident is kept when *any* participant belongs to the selected database — a process whose current database matches, or a lock resource whose `dbid` matches — so a cross-database deadlock between `OrdersDb` and `BillingDb` stays visible when you filter on either one. The list offers every database on the server plus any name that only appears inside an incident (a database that has since been dropped).
-   Results are kept per connection and time window until you click **Refresh**: switching from *Last 24 hours* to *Last 7 days* and back shows the 24-hour result you already read, not a new read. The panel shows when the visible result was fetched ("fetched <local time>") so you can tell how old it is. Changing the connection or opening a window you have not read yet shows "Refresh to load"; responses from an abandoned read are ignored.

### Retention and truncation

`system_health` is a rolling window, not a history. Its event files roll over at 5 MB × 4 files (100 MB × 10 files on Standard and Enterprise editions), so a busy server keeps hours or days. The ring buffer holds fewer events still, is emptied when the engine restarts, and is limited to 4 MB when SQL Server converts it to XML. Jam prefers the files; when only the ring buffer is available (or Azure rejects the local file path) the panel labels the read **Ring-buffer fallback** and, when SQL Server marks the XML as truncated or announces more events than it returned, says so explicitly: what you see is a retained sample, not a complete history.

Each refresh reads at most the newest 200 events in the window; when that cap is hit the panel tells you to narrow the time range. Events are keyed by their UTC timestamp and a hash of the normalized `<deadlock>` XML — the event file offset alone is not unique because SQL Server writes several events per buffer — so re-reads do not duplicate incidents and two different deadlocks written to the same buffer both stay.

An event whose XML cannot be parsed (for example one cut off by ring-buffer truncation) is listed as *Unreadable* with its timestamp and the parser's message; the other incidents still open normally.

### Refresh, Cancel and failures

Reads are manual: nothing polls in the background. Every statement is bounded to 30 seconds, and **Cancel** stops the statement that is running on the server. On a missing or stopped session, an unsupported service, a denied permission, a timeout, a cancel or a dropped connection the tab keeps the panel, explains the state and offers **Open XML/XDL…** and **Paste XML** as the fallback. A stopped session is reported as such; Jam does not start it (`ALTER EVENT SESSION system_health ON SERVER STATE = START` is a sysadmin decision).

### Troubleshooting missing history

-   **No incidents in the window but you know a deadlock happened** — widen the time range; check the retention notice (ring-buffer reads and the 200-event cap only show the newest events); on a restarted server the ring buffer is empty and only the files remain.
-   **"system\_health session not found"** — the session was dropped. A sysadmin can restore it with the `u_tables.sql` script from the SQL Server `Install` folder (Microsoft Learn) and start it.
-   **"session is stopped"** — no new deadlocks are being retained; retained files may still be readable once it runs again.
-   **Permission denied** — see the grant above; the panel names the object and permission for your version.
-   **Azure SQL Database** — the collector is not available; use your own session and import.

## Storage and privacy

In collector mode the tab persists only the connection id and the filters (time window, database) with your session; the collected events, their XML and the parsed incidents live in memory (kept per time window until you Refresh, with the fetch time shown in the panel) and are re-read when you click Refresh after a restart. Analytics record the refresh outcome, whether the files or the ring buffer were read, an incident-count bucket and a truncation flag — never server, database or login names.

For imported reports, the report XML and the parsed incidents live in the app's memory only. When you save or restore a session, the tab keeps just its source metadata: whether the report came from a file, a paste or the sample, the file path for files, and the selected incident and node. A file-backed tab re-reads the file on restore; a pasted report cannot be restored and the tab asks you to paste or open it again. Analytics record the import action, its outcome and an incident-count bucket — never the XML, SQL text or file name.

Reports are treated as untrusted input: documents that declare a DTD or entities (`<!DOCTYPE>`, `<!ENTITY>`) are rejected before parsing, external resources are never fetched, and the source is always rendered as text.

## What the graph does and does not prove

The graph proves exactly what SQL Server wrote: which process owned which lock in which mode, and which process requested which lock in which mode at the moment the deadlock monitor ran. It does not prove why the statements ran in that order, whether an index would have avoided the lock, or that any particular change is safe. Use the execution stacks and input buffers to find the statements, then decide on changes — consistent access order, narrower transactions, a covering index, a different isolation level — and verify them against the server.

## Frequently asked questions

Can I open XDL on Mac?

Yes. Jam SQL Studio opens .xdl and .xml deadlock reports on macOS, Windows and Linux. Use More > Deadlock Analyzer, click Open XML/XDL and pick the file. No SQL Server connection is needed.

Does the app upload my XML?

No. The report is parsed locally in the app and kept in memory. Only the source metadata (file path or that it was pasted, the selected incident and node) is saved with your session; the XML itself is never written to the session file or sent anywhere. Analytics record only the import outcome and an incident-count bucket, and for the collector the refresh outcome, whether the event files or the ring buffer were read, an incident-count bucket and a truncation flag.

Can this automatically fix a deadlock?

No. The analyzer shows what the report proves: which processes held or requested which locks, in what mode, and which process was chosen as the victim. It does not generate corrective SQL or claim a root cause; use the execution stacks and input buffers to decide on index, transaction-order or isolation-level changes yourself.

Which deadlock XML formats are supported?

A bare <deadlock> element, a <deadlock-list> with several incidents, and Extended Events xml\_deadlock\_report events, including a ring-buffer or event list export with multiple events. Files may be UTF-8 (with or without BOM) or UTF-16 LE/BE, up to 10 MiB. Binary .xel files are not readable locally; the collector reads .xel event files through SQL Server itself.

Can I read Azure SQL Database system\_health?

No. Azure SQL Database has no built-in system\_health session, so there are no retained deadlock events for the collector to read; Jam detects EngineEdition 5 and explains this instead of running the read. Capture xml\_deadlock\_report with your own Extended Events session, export the XML and import it. SQL Server and Azure SQL Managed Instance do have system\_health.

Why are old incidents missing?

system\_health keeps a rolling window, not a history. Its event files roll over at 5 MB x 4 files (100 MB x 10 files on Standard and Enterprise editions), so busy servers keep hours or days, not months. The ring buffer holds even less and is limited to 4 MB when read as XML; Jam labels a ring-buffer read and shows when SQL Server reported the XML as truncated. Jam also reads at most the newest 200 events in the selected time window and never creates a longer-lived session for you.