sqldeadlock-review

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 17 known deadlock patterns (P1–P17), 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 .xdl or .xml deadlock graph file
• A description of the deadlock if XML is not available

How to Run

1. Parse the XML structure
2. Extract process list (victim, winner, their queries, lock waits)
3. Extract resource list (what locks are held and requested)
4. Match against pattern library
5. Generate remediation recommendations

---

XML Structure Reference

<deadlock>
  <victim-list>
    <victimProcess id="process1a2b" />
  </victim-list>
  <process-list>
    <process id="process1a2b" taskpriority="0" logused="0"
             waitresource="KEY: 5:72057594038910976 (abc123)"
             waittime="4023" ownerId="123456"
             transactionname="user_transaction"
             currentdb="5" spid="52" kpid="1234"
             status="suspended" isolationlevel="read committed">
      <executionStack>
        <frame procname="adhoc" line="3" stmtstart="100" stmtend="200"
               sqlhandle="0x...">
          UPDATE Orders SET Status = 1 WHERE Id = @id
        </frame>
      </executionStack>
      <inputbuf>UPDATE Orders SET Status = 1 WHERE Id = @id</inputbuf>
    </process>
    ...
  </process-list>
  <resource-list>
    <keylock hobtid="72057594038910976" dbid="5" objectname="dbo.Orders"
             indexname="PK_Orders" id="lock1" mode="X" associatedObjectId="...">
      <owner-list>
        <owner id="process2c3d" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process1a2b" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    ...
  </resource-list>
</deadlock>

---

Extraction Checklist