# Deadlock Analysis

> 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`.

- Skill: `microsoft/deadlock-analysis` (Agent Skill)
- Install (CLI): `npx get-skill add microsoft/deadlock-analysis`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/deadlock-analysis/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/deadlock-analysis

---


# 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`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) 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)
```sql
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)
```sql
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.

