sqlplan-deadlock
SQL Server Deadlock Analysis Skill
Purpose
Parse a SQL Server deadlock XML graph, identify the victim and winner processes, extract the queries and lock acquisition patterns involved, match against known deadlock patterns, and produce a prioritized remediation plan.
Input
Accept any of:
- Raw
<deadlock>XML (from system_health XE session or SSMS deadlock graph Save As XML) - A file path to a
.xdlor.xmldeadlock graph file - A description of the deadlock if XML is not available
How to Run
More from vanterx/mssql-performance-skills
sqlplan-review
Analyze SQL Server execution plans for performance anti-patterns. Applies all 99 checks (S1–S33 statement-level, N1–N66 node-level). Use when a user pastes a .sqlplan XML, describes operators, or asks why a query is slow.
2sqlplan-compare
Diff two SQL Server execution plans (baseline vs regression) to identify what changed — join strategies, memory grants, operator topology, new warnings, and missing indexes. Use when a query regressed after a deployment, statistics update, or schema change.
1procstats-review
Analyze SQL Server procedure/trigger/function runtime stats collected from sys.dm_exec_procedure_stats into collect.proc_stats. Applies 20 checks (R1–R20) across four categories — top consumers, per-execution efficiency, pattern detection, and trend analysis. Use when pasting output from the report queries in sql/collection/04_report_queries.sql.
1sqlstats-review
Parse and analyze SQL Server SET STATISTICS IO, TIME ON output. Extracts per-table IO metrics and per-statement CPU/elapsed times, computes % logical read share, detects 22 performance patterns (I1–I15 IO checks, W1–W7 time checks). Use when a user pastes SSMS statistics output or asks why a query does too much I/O.
1sqlwait-review
Analyze SQL Server wait statistics to identify why the server or a session is slow. Applies 40 checks (V1–V40) covering I/O, locks, parallelism, memory, CPU, TempDB, log I/O, network, latch contention, log space exhaustion, poison/throttle waits, backup I/O, insert hotspots, cumulative skew detection, multi-snapshot trend analysis, In-Memory OLTP, Columnstore, Query Store, Transaction/DTC, Service Broker, Full Text Search, Parallel Redo, forced memory grants, grant timeouts, stolen memory, and file I/O latency. Based on Paul Randal and Brent Ozar methodologies. Use when pasting sys.dm_os_wait_stats or sys.dm_exec_requests output.
1sqlplan-batch
Batch-analyze a folder of SQL Server .sqlplan files and produce a summary dashboard of the top issues, most common check violations, and deduplicated missing indexes across all plans. Use this skill whenever a user has a folder or collection of .sqlplan files; asks for a workload-level summary across multiple plans; wants to find systemic patterns across a captured workload; or doesn't know which plan to look at first. Trigger after any workload capture that produced multiple .sqlplan files — offer this before individual sqlplan-review calls.
1