Mastering SQL Server Deadlock Monitoring: Tools, Queries, and Extended Events

Why monitor SQL Server?

Ensuring that a SQL Server instance runs efficiently and maintains good performance is a core responsibility of a DBA; continuous monitoring of resource usage, error logs, and high‑availability components helps detect issues early and keep the database healthy.

Key monitoring areas

CPU, memory, I/O, network traffic, and cache usage of the server.

Health of SQL Server services and SQL Server Agent.

SQL Server error logs and Agent logs.

Critical error events (e.g., error numbers 823, 824, 825, 832, 855, 856) and severity levels 19‑25.

High‑availability technologies such as mirroring, log shipping, replication, and AlwaysOn.

Connection timeouts, query execution timeouts, and deadlocks.

Active processes, slow queries, and blocking sessions.

Wait statistics for pinpointing performance bottlenecks.

Ring buffer contents that capture low‑level system output.

Audit events for tracking engine‑level actions.

Automation via PowerShell and reporting through SSRS.

Understanding deadlocks

A deadlock occurs when two or more processes each hold resources the other needs, causing all of them to wait indefinitely unless an external action terminates one of the sessions.

Deadlock monitoring thread

SQL Server runs a background thread that checks sys.dm_os_waiting_tasks for the REQUEST_FOR_DEADLOCK_SEARCH wait type every five seconds. When a deadlock is detected, the thread selects the victim session—typically the one with the smallest LOG USED value—to kill, allowing the other session to continue.

Methods to monitor deadlocks

Performance Monitor (PerfMon) : Add the counter SQLServer:Locks\Number of Deadlocks/sec (instance _Total).

Trace Flags 1204 and 1222 : Enable these flags to have deadlock details written to the SQL Server error log.

SQL Server Profiler / Trace : Capture the Locks event with the Deadlock Graph event class.

Extended Events (EE) : Preferred lightweight method that provides XML deadlock graphs.

system_health default trace : Continuously runs and records deadlock information in a ring buffer or event file.

Method 1 – Performance Monitor

Object: SQLServer:Locks Counter: Number of Deadlocks/sec Instance:

_Total

Method 2 – Trace Flags

Enable Trace Flag 1204 (available since SQL Server 2000) and Trace Flag 1222 (since SQL Server 2005). Their output appears in the SQL Server error log.

Method 3 – Profiler

Capture the Locks event and select the Deadlock Graph event name to obtain an XML representation of the deadlock.

Method 4 – Extended Events

Creating an EE session called Deadlock_Monitor provides a detailed, low‑impact way to capture deadlocks. Below is a step‑by‑step guide.

In Object Explorer, expand Management → Extended Events → Sessions .

Right‑click Sessions and choose New Session Wizard .

Enter session name Deadlock_Monitor and click Next.

Do not use a template; click Next.

In the Event Library, search for deadlock and select xml_deadlock_report, then add it to the selected events list.

Proceed through the wizard, accepting default columns, and optionally define filters (none needed for basic monitoring).

Choose to save data to a file, specify the file path and size limits, then finish.

Start the session.

Run two queries that will cause a deadlock (example screenshots shown).

Right‑click the Deadlock_Monitor session, choose View Target Data… , locate the timestamped deadlock entry, and open the xml_report to view the graphical deadlock diagram.

Method 5 – system_health default trace

The system_health session runs continuously and records deadlock events in a ring buffer (pre‑SQL 2012) or an event file (SQL 2012+). Query the session list: SELECT * FROM sys.dm_xe_sessions Retrieve the deadlock data:

SELECT name, target_name, CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE s.name = 'system_health'

Note that event_file stores data on disk, while ring_buffer keeps it in memory.

Extracting deadlock reports from system_health

Run the following query to get a deadlock report in XML format:

SELECT target_data
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer'
AND target_data.value('(event/@name)[1]', 'varchar(50)') = 'deadlock'

The XML contains process-list (showing the T‑SQL that caused the deadlock), resource-list, and other details that can be parsed to identify the victim and the resources involved.

Additional EE events for deeper analysis

lock_deadlock

– validates which session was chosen as the victim. lock_deadlock_chain – captures the full chain of lock acquisitions leading to the deadlock.

These events can be added to the Deadlock_Monitor session via the session properties dialog.