Deadlock at glance
Introducing sp_xDeadlockAtGlance, a SQL Server utility stored procedure that transforms a deadlock graph (XDL) into a compact, human-readable summary. It provides a quick overview of the deadlock, making it an ideal first step in the troubleshooting process.
Features
- Automatically identifies the deadlock victim
- Summarizes participating sessions and locked resources
- Resolves index names whenever possible
- Generates ready-to-run helper queries to:
- Resolve missing index names
- Retrieve the execution plan and SQL text from the plan cache
- Detects:
- Parallel query deadlocks
- MARS (Multiple Active Result Sets)
- Multi-statement transactions
- Multi-batch transactions
- Compilation deadlocks
- Intra-query deadlocks
- Nested transactions
- Prior ATTENTION events (query cancellation, timeout, or broken client connection)
- Extracts:
- Current SQL statement
- Input buffer
- Stored procedure name
- SQL handle
- Wait resource
- Transaction metadata
- Deadlock priority
Parameters
| Parameter | Type | Description |
|---|---|---|
@dl |
xml |
Deadlock graph XML |
@short |
bit |
1 (default) returns a concise summary. 0 returns detailed diagnostic information |
Short Output
The default output is designed to answer the most common questions when investigating a deadlock.
| Column | Description |
|---|---|
spid |
SQL Server session ID. The deadlock victim is marked with (victim). |
process_id |
Internal deadlock process identifier. |
deadlock_priority |
Deadlock priority (set deadlock_priority) of the session. Values range from -10 (lowest priority) to 10 (highest priority). Sessions with lower priorities are more likely to be selected as the deadlock victim. |
parallel |
Indicates whether the request was executing in parallel. |
mars |
Indicates Multiple Active Result Sets (MARS). |
multi_statement |
Indicates that the transaction spans multiple SQL statements. |
multi_batch |
Indicates that the transaction spans multiple client batches. The reported duration is the idle time between the completion of the previous batch and the start of the current batch while the transaction remained open. Long idle periods may indicate application think time, client-side processing, or unnecessarily long-lived transactions. |
compilation_deadlock |
Indicates a compilation deadlock. |
intra_query_deadlock |
Indicates an intra-query deadlock (parallel worker deadlock). |
abort |
Indicates whether the request had previously received an ATTENTION event, which usually indicates a client-side cancellation or query timeout. |
nested_transaction |
Indicates nested transactions (@@trancount > 2). |
tran_count |
Transaction nesting level. |
lock_mode |
Lock mode currently held by the process. |
isolation_level |
Transaction isolation level. |
proc_name |
Stored procedure containing the current statement, when available. |
res_db_id |
Database ID of the locked resource. |
res_locked_object_name |
Locked object name. |
res_index_name |
Index involved in the deadlock, when available. |
res_lock_mode |
Lock mode held on the resource. |
Detailed Output
When @short = 0, additional troubleshooting information is returned.
| Column | Description |
|---|---|
get_res_index_name |
Ready-to-run query that resolves the index name when it cannot be determined automatically (typically for objects in another database). |
res_wait_type |
Resource wait type. |
res_waiter_type |
Waiter type reported by the deadlock graph. |
res_waiter_wait_mode |
Lock mode requested by the waiting process. |
res_lock_event |
Type of locked resource (KEY, PAGE, OBJECT, RID, HOBT, etc.). |
res_waiter_process_id |
Internal identifier of the waiting process. |
wait_resource |
SQL Server wait resource string. |
input_buf |
Batch submitted by the client. |
statement |
Current SQL statement executing when the deadlock occurred. |
sql_handle |
SQL handle of the executing statement. |
get_execution_plan |
Ready-to-run query that attempts to retrieve the execution plan and SQL text from the plan cache using the captured SQL handle and statement offsets. |
log_used_kb |
Approximate transaction log space used by the transaction (KB). |
Resolving Missing Index Names
Deadlock XML does not always include the index name. This commonly happens when the locked object belongs to a different database than the one executing the procedure.
In these cases, the get_res_index_name column contains a ready-to-run query that resolves the index name from the HoBT ID.
Retrieving the Execution Plan
If the deadlock XML contains a SQL handle, the get_execution_plan column contains a ready-to-run query that attempts to retrieve:
- the execution plan (
query_plan) - the SQL text
- the exact statement within the batch (using statement offsets)
The query uses the SQL handle captured in the deadlock graph and looks up the plan in the SQL Server plan cache.
Note: Execution plans remain in the plan cache only while they have not been evicted. If the plan is no longer cached, the helper query will return no rows.
For more information, see: How to get an execution plan for a query involved in deadlock
Typical Workflow
- Capture a deadlock graph using Extended Events, SQL Server Profiler, or your preferred monitoring tool.
- Pass the deadlock XML to
sp_xDeadlockAtGlance. - Review the concise summary to quickly identify the victim, participating sessions, locked resources, and common deadlock patterns.
- Re-run with
@short = 0if additional investigation is required. - Use the generated helper queries to:
- Resolve missing index names.
- Retrieve the execution plan and SQL text from the plan cache (when available).
Example
1declare @dl xml = ...
2
3-- short output
4exec dbo.sp_xDeadlockAtGlance @dl = @dl
5
6-- detailed output
7exec dbo.sp_xDeadlockAtGlance @dl = @dl, @short = 0
sp_xDeadlockAtGlance is free, open source, and available in the x-sprocs repository on GitHub.
Feel free to share your thoughts in the comments on LinkedIn.
Τα λέμε!