Deadlock Analysis — SQL Server
Use this skill when the user asks about deadlocks, deadlock reports, or deadlock history on SQL Server or Managed Instance.
Platform Guard: This skill targets Engine Edition 1–4 or 8. The system_health XE session with fn_xe_file_target_read_file is not available on Azure SQL DB.
Scope
- Live retrieval reads
system_health deadlock reports and works on SQL Server and Managed Instance only.
- Also explains deadlock XML or a deadlock graph that the user supplies: victim, participating processes and statements, contested resources (KEY, PAGE, OBJECT, RID locks), and lock modes.
- For deadlocks on Azure SQL Database, use the
deadlock-analysis-azuresql skill.
Required tools
- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL
Tools MCP server that ships with the
microsoft-sql-admin plugin (mssql_list_database_connections,
mssql_connect_database, then mssql_execute_query); any other SQL tool the agent host provides for
running T-SQL and reading result sets;
sqlcmd from a terminal. If the SQL
Tools MCP server is not connected or lists no connections, follow the sql-tools-mcp-setup skill,
which asks before installing or configuring anything. If no SQL tool can be made available, give the
user the T-SQL to run and ask for the results.
- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
holds only
VIEW SERVER PERFORMANCE STATE (VIEW SERVER STATE before SQL Server 2022) or, on Azure
SQL Database, VIEW DATABASE PERFORMANCE STATE or ##MS_ServerStateReader##, plus any other read
permission these queries need. A tool is only as read-only as the login it connects with.
SQL Queries
Get Deadlock Reports (Server-wide)
SELECT
event_data.value('(event/@timestamp)[1]', 'datetime2') AS [Date],
event_data.query('.') AS [Event_Data]
FROM (
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
) AS events
ORDER BY [Date] DESC;
Get Deadlock Reports (Current Database Only)
DECLARE @dbid INT = DB_ID();
SELECT
event_data.value('(event/@timestamp)[1]', 'datetime2') AS [Date],
event_data.query('.') AS [Event_Data]
FROM (
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
) AS events
WHERE event_data.exist('/event/data/value/deadlock/process-list/process[@currentdb = sql:variable("@dbid")]') = 1
ORDER BY [Date] DESC;
Behavioral Guidance
- Deadlock reports can be very long and difficult to read.
- Provide a list of the deadlock reports found and summarize each in 30 words or less.
- Conclude by offering to provide more details on any specific deadlock report.
- Unless the user specifies, default to the current database scope.
1---2name: deadlock-analysis3description: "Use when the user asks about deadlocks or deadlock history on SQL Server or Managed Instance, or to explain a supplied deadlock graph or XML. For Azure SQL Database, use `deadlock-analysis-azuresql`."4user-invocable: true5metadata:6 source: https://skillmd.com/skills/microsoft/deadlock-analysis7---89# Deadlock Analysis — SQL Server1011Use this skill when the user asks about deadlocks, deadlock reports, or deadlock history on **SQL Server** or **Managed Instance**.1213> **Platform Guard**: This skill targets Engine Edition 1–4 or 8. The `system_health` XE session with `fn_xe_file_target_read_file` is not available on Azure SQL DB.1415## Scope1617- Live retrieval reads `system_health` deadlock reports and works on SQL Server and Managed Instance only.18- Also explains deadlock XML or a deadlock graph that the user supplies: victim, participating processes and statements, contested resources (KEY, PAGE, OBJECT, RID locks), and lock modes.19- For deadlocks on Azure SQL Database, use the `deadlock-analysis-azuresql` skill.2021## Required tools2223- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL24 Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,25 `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for26 running T-SQL and reading result sets;27 [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL28 Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill,29 which asks before installing or configuring anything. If no SQL tool can be made available, give the30 user the T-SQL to run and ask for the results.31- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that32 holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure33 SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read34 permission these queries need. A tool is only as read-only as the login it connects with.3536## SQL Queries3738### Get Deadlock Reports (Server-wide)39```sql40SELECT41 event_data.value('(event/@timestamp)[1]', 'datetime2') AS [Date],42 event_data.query('.') AS [Event_Data]43FROM (44 SELECT CAST(event_data AS XML) AS event_data45 FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)46 WHERE object_name = 'xml_deadlock_report'47) AS events48ORDER BY [Date] DESC;49```5051### Get Deadlock Reports (Current Database Only)52```sql53DECLARE @dbid INT = DB_ID();54SELECT55 event_data.value('(event/@timestamp)[1]', 'datetime2') AS [Date],56 event_data.query('.') AS [Event_Data]57FROM (58 SELECT CAST(event_data AS XML) AS event_data59 FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)60 WHERE object_name = 'xml_deadlock_report'61) AS events62WHERE event_data.exist('/event/data/value/deadlock/process-list/process[@currentdb = sql:variable("@dbid")]') = 163ORDER BY [Date] DESC;64```6566## Behavioral Guidance6768- Deadlock reports can be very long and difficult to read.69- Provide a **list** of the deadlock reports found and **summarize each** in 30 words or less.70- Conclude by offering to provide more details on any specific deadlock report.71- Unless the user specifies, default to the **current database** scope.