Hello!
This Procedure analyzes and exports deadlock information across multiple SQL Server instances within a specified time range.
It processes captured deadlock XMLs, identifies victim and survivor processes, and generates a consolidated report with details such as SPID, database, application, host, and optionally the SQL text and deadlock XML.
It's useful for performance tuning and troubleshooting in environments with multiple monitored instances.
Execute on: DPA Repository
DPA Repository SQL Version: 2017 or above (to use string_agg)
Script to create: Download attached query.
Running Proc:
EXEC dbo.usp_GetDeadlockAnalysis
@StartDate = '2025-05-28 00:00:00',
@EndDate = '2025-05-29 00:00:00',
@InstancesList = 'server01,server02',
@ExportSQLText = 1,
@ExportDeadlockXML = 1;
-- Parameters:
-- @StartDate: Start date/time of the analysis period
-- @EndDate: End date/time of the analysis period
-- @InstancesList: Comma-separated list of instance names to analyze
-- @ExportSQLText: Flag to export the SQL text involved in the deadlocks (1 = export, 0 = do not export)
-- @ExportDeadlockXML: Flag to export the raw deadlock XML data (1 = export, 0 = do not export)
Here is a sample of the output:
Instance_Name: The name of the SQL Server instance where the deadlock occurred.
Deadlock_Time: The timestamp of when the deadlock was captured.
Deadlock_ID: A unique identifier for the deadlock event.
session_count: Number of sessions involved in the deadlock.
Victim_Process_ID: The process ID (in the XML) that was chosen as the deadlock victim.
Victim_SPID: The SPID (session ID) of the victim process.
Victim_CurrentDBName: The current database name in use by the victim process.
Victim_LoginName: The login name of the victim process.
Victim_ClientApp: The client application name of the victim process.
Victim_Hostname: The hostname (machine) from which the victim process originated.
Victim_ProcName: The stored procedure or batch name involved for the victim process.
Victim_SQL_Text (only if @ExportSQLText=1): The SQL text executed by the victim process.
Survivor_Process_ID: The process ID of the survivor process.
Survivor_SPID: The SPID (session ID) of the survivor process.
Survivor_CurrentDBName: The current database name in use by the survivor process.
Survivor_LoginName: The login name of the survivor process.
Survivor_ClientApp: The client application name of the survivor process.
Survivor_Hostname: The hostname (machine) of the survivor process.
Survivor_ProcName: The stored procedure or batch name involved for the survivor process.
Survivor_SQL_Text (only if @ExportSQLText=1): The SQL text executed by the survivor process.
Deadlock_XML_XDL (only if @ExportDeadlockXML=1): The full deadlock XML for the event.
In this script, for each deadlock record:
It identifies the victim process using the Victim_Process_ID and extracts detailed information about it (SPID, database, login, application, etc.).
Then, it uses a CROSS APPLY on the process list inside the deadlock XML to select all processes whose ID is different from the Victim_Process_ID.
This means:
For each deadlock, there will be one row per survivor process in the final result.
If a deadlock has 1 victim and 2 survivors, the script will generate 2 rows in the output:
This way, the final result clearly details every victim-survivor relationship, allowing you to see which processes survived and their context (SQL text, application, hostname, etc.). It’s a useful way to understand all the processes involved in the deadlock—not just the victim, but also who was on the other side of the deadlock.
Be careful with long date ranges when running this script. Since it queries XML data from the deadlock logs across multiple instances, using a large time interval can result in significant performance overhead and longer execution times. This is especially true in environments with frequent deadlocks. Adjust your date range to be as narrow as possible to ensure faster and more efficient analysis.