Deadlock Detection & Analysis: A Practical Setup Guide

See exactly what collided and why, the moment it happens, instead of reconstructing it from a partial error log entry hours later.

If you use SQL Diagnostic Manager for SQL Server (SQL DM) every day for dashboards and alerts, there’s a good chance your team still defaults to the SQL Server error log or standing up an ad hoc trace flag or Extended Events session: every time a deadlock gets reported. SQL DM already captures every deadlock automatically as it happens and visualizes it, and most teams have never turned that capability on. This guide covers what deadlock detection shows you, when to reach for it, what to configure before you need it, and the exact click paths in both the desktop console and the web console.

Why Deadlock Detection & Analysis Is Worth Your Time

A deadlock is a race condition: two or more sessions each hold a lock the other needs, and SQL Server kills one of them off as the “victim” to break the standoff. The whole thing can resolve itself in a fraction of a second, which is exactly why it’s so hard to catch after the fact. SQL DM removes the guesswork by watching for it continuously:

• Automatic, continuous capture — no trace flag or Extended Events session to stand up in advance, and no need to guess which day it will happen.
• A visualized conflict, not raw XML — both competing queries, the resources each session held or waited on, and which session was chosen as the victim.
• The Blocking Chart — blocked sessions, lead blockers, and total deadlocks at a glance, right alongside your other performance data.
• The Deadlock Sessions Report — full deadlock detail (SPID, host, login, wait resource, lock object/mode, and more), exportable in XDL format for deeper analysis.
• A record you can hand off — export and hand the exact conflict to a developer without translating raw trace output first.

When to Use It

Before You Start: Six Considerations

Deadlock capture is a separate switch from the live Sessions views. The Sessions tab works as soon as an instance is monitored; collection is agentless. But the detailed deadlock record behind an alert depends on the Activity Monitor. If nobody has enabled Capture deadlocks, you’ll see that a deadlock happened without the detail behind it. Phase 1 below fixes that.

Set the blocked process threshold to at least 5 seconds. Collecting blocked-process information is resource-intensive on SQL Server. Enabling Capture Blocking sets this to 5 seconds automatically; IDERA recommends leaving it at 5 or higher, or your deadlock monitor effectively runs constantly. Changing this value in SQL DM also changes it on the monitored SQL Server instance itself.

Extended Events vs. SQL Trace. For instances running SQL Server 2012 or higher, the Activity Monitor uses Extended Events by default; lightweight and efficient. Only fall back to SQL Trace for pre-2008 instances, and expect some performance cost on the monitored server. Deadlock capture requires SQL Server 2005 or greater.

Deadlocks will not appear in the Blocking Tree. Deadlocks are circular by nature and have no lead blocker, so SQL DM doesn’t display them there. They do appear as counts in the Blocking Chart and in full detail in the Deadlock Sessions Report. Knowing this in advance saves you from concluding the tree is broken.

Capture and alerting are two different steps. Turning on Capture deadlocks gives you the detailed record; it doesn’t by itself set a threshold that pages anyone. Phase 4 below wires the actual Deadlock alert.

Exports are in XDL format. Deadlock Sessions Report exports open directly in SQL Server Management Studio or SQL Server Profiler; useful when the fix requires a developer’s review of a multi-statement transaction chain.

Part 1: Step-by-Step in the Desktop Console

Four phases. Phase 1 is one-time configuration per instance. Phases 2 and 3 are the workflow you’ll use during and after an incident. Phase 4 makes the alert point you here.

Phase 1: Enable Deadlock Capture

Do this once per monitored instance, ideally before you need it. The live Sessions views work without it; the detailed deadlock record does not.

1. In the SQL Diagnostic Manager desktop console, find the instance in the Servers sidebar.
2. Right-click the instance and select Properties.
3. In the Monitored SQL Server Properties window, click the Activity Monitor tab.
4. Select Enable the Activity Monitor. Nothing below this point applies until this is checked.
5. Under non-query activities, select Capture deadlocks (SQL 2005+). This is what lets the Deadlock alert and the Deadlock Sessions Report show SPID, host, login, client application, database, wait time, wait resource, lock object, lock mode, lock owners, and lock waiters; instead of just “a deadlock occurred.”

The Activity Monitor tab of the Monitored SQL Server Properties window, with Capture Blocking and Capture deadlocks enabled.

1. Also select Capture Blocking (SQL 2005+) while you’re here. Deadlocks are a special case of blocking, and this is what gives you the full blocking-chain context (and the Blocked Process Threshold field in the next step) alongside deadlock detail.
2. Optionally select Capture Autogrow, the third non-query event type; it feeds the autogrow alert with log and data growth detail and has no bearing on deadlock capture.
3. Confirm the collection mechanism. For SQL Server 2012 and higher, leave Use Extended Events selected. Choose Use SQL Trace only for pre-2008 instances, and expect some performance cost on the monitored server.
4. Check the Blocked Process Threshold value, which appears once Capture Blocking is enabled. It defaults to 5 seconds. Keep it at 5 or higher.

The Blocked Process Threshold field, which appears once Capture Blocking is enabled, and defaults to 5 seconds

1. Click OK to save, then repeat for your remaining production instances.

Phase 2: Review a Captured Deadlock

1. Click the monitored SQL Server instance in the Servers sidebar, then click the Sessions tab.
2. Click Sessions → Blocking. Deadlocks don’t populate the Blocking Tree (they have no lead blocker), but they do show up in the Blocking chart as a Total Deadlocks count alongside blocked sessions and lead blockers, at a given point in time.

The Sessions tab in the SQL Diagnostic Manager desktop console, showing the Summary, Details, Locks, and Blocking views — the Blocking view is where deadlock counts appear.

1. If a Deadlock alert brought you here, right-click the alert in the Alerts view and select Show Deadlock Details for the full record.
2. You can also reach the same information from the Blocking view itself; open the Deadlock Sessions Report (Deadlock Reports).
3. Review the deadlock detail: Session ID, Status, Host Name, User Name, Execution Context, Application, Database, Last Batch Started, Last Batch Completed, Last Transaction Started, Transaction ID, Transaction Name, Open Transactions, Wait Time (ms), Wait Type, and Wait Resource.
4. For an incident that already ended, use the History Browser to rewind to the point in time the deadlock alert fired and see which sessions were involved, this is how you investigate the 2 a.m. alert at 9 a.m.

Phase 3: Export, Hand Off, and Spot Patterns

1. From the Deadlock Sessions Report, export the deadlock detail in XDL format.
2. Open the export in SQL Server Management Studio or SQL Server Profiler, or hand it directly to a developer or application owner; it’s a precise, self-contained record that doesn’t need a DBA to translate raw trace output first.
3. For historical analysis across many occurrences, open the Deadlock report. It filters by Instance Name, Application Name, Database Name, SQL Statement, Collection Date, XDL Data, Deadlock ID, Login Name, and Host Name, and lists every deadlock occurrence historically.
4. Look specifically for deadlocks recurring on the same object, application, or time of day; that’s the signal a query rewrite, an indexing change, or a transaction-isolation adjustment addresses the root cause rather than just the symptom.

Phase 4: Wire the Deadlock Alert

Deadlock capture on its own is reactive; it records the detail once you go looking. The alert is what makes it proactive.

1. Right-click the instance in the Servers sidebar and select Configure Alerts.
2. In the Alert Configuration window, select the Deadlock metric from the list.
3. Set warning and critical thresholds that reflect your workload, there’s no universal correct value; a reporting instance and an OLTP instance have very different tolerances.
4. While you’re in there, also set Blocked Sessions (Count), Blocking Session Wait Time (Seconds), and Oldest Open Transaction (Minutes); a transaction left open for twenty minutes is usually the cause of a blocking chain rather than a victim of one, and deadlocks often show up alongside these.
5. Put a recurring review on someone’s calendar to check the Blocking chart on your two or three busiest instances. This is the habit that turns deadlock detection from a fire extinguisher into an early-warning system.

Part 2: The Same Workflow in the Web Console

The 14.1 web console presents session and deadlock data through the single instance dashboard. The information is much the same; the navigation and filtering are different, and there’s no Trace Session, Kill Session, or Deadlock Sessions Report export here; those live in the desktop console.

1. Open the web console and navigate to the single instance view for the instance you care about.
2. Click the Sessions tab. It contains two tabs: Listing and Charts.
3. On Listing, use the Session Filter: Sessions (everything), Blocked, or Blocking. During an incident, click Blocking first.

The Session Filter narrows the Listing view to all sessions, blocked sessions, or blocking sessions

1. Narrow further with the Column Filter: select a column, click the options icon next to its name, select Filter, and enter or select your criteria.

The Column Filter lets you filter the Listing view by specific criteria for any column

1. Use the Time Range Filter to review session state within a specific window rather than only right now; the web console’s answer to reviewing an incident after the fact.

The Time Range Filter sets the start and end date and time for the session data shown

1. Switch to the Charts tab for the graphical view. The Blocked Sessions chart lists Blocked Sessions, Lead Blockers, and Total Deadlocks for the selected instance, so you can see deadlock counts trend over time alongside blocking.

The Charts tab in the SQL DM 14.1 web console, including the Blocked Sessions chart (blocked sessions, lead blockers, and total deadlocks).

1. When you find a deadlock or blocking chain you need to trace, kill, or export in XDL, switch to the desktop console for those actions.

Reference

Deadlock-Related Views & Reports at a Glance

Deadlock-Related Alerts

Configure these by right-clicking the instance and selecting Configure Alerts.

What Deadlock Detection Accelerates vs. What You Decide

Source Documentation

• Deadlock alert — what the alert shows and how to resolve it
• Set activity monitor options — Capture deadlocks, Capture Blocking, Extended Events vs. SQL Trace, blocked process threshold
• Analyze blocked sessions — the Blocking view, Blocking chart, and the Deadlock Sessions Report
• Deadlock Report — the historical Deadlock report and its filters
• View your SQL Server sessions information — the web console Listing and Charts tabs
• SQL Diagnostic Manager Overview — full product documentation space

Next Steps

• Read the blog: SQL Server Deadlocks Are Nearly Impossible to Catch in the Act
• Start a free 14-day trial of SQL Diagnostic Manager
• Talk to your account team about a live deadlock detection walkthrough