Introduction

SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.

Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.

1. The <deadlock> Root Element

<deadlock>
   ...
</deadlock>

Every deadlock report is wrapped in a <deadlock> element. Inside, you’ll see:

• victim-list
• process-list
• resource-list

These sections collectively describe the details of the deadlock and help you identify the root cause.

2. The <victim-list> Section

<victim-list>
  <victimProcess id="process292009b2ca8"/>
</victim-list>

• victimProcess: Shows the process ID (id) of the session chosen as the deadlock victim. SQL Server terminates this session’s transaction to resolve the deadlock. Here, process292009b2ca8 is the victim.

Key takeaway: If this is your session or query, it means SQL Server chose it to be rolled back. Often, short-running or lower-priority transactions become victims.

3. The <process-list> Section

<process-list>
  <process id="process292009b2ca8" ... spid="87" ... >
    ...
  </process>
  <process id="process292009bb088" ... spid="61" ... >
    ...
  </process>
</process-list>

In a deadlock, at least two processes are involved. Each <process> element provides information such as:

• id: Matches the process in the victim-list or resource-list.
• spid: The SQL Server session ID.
• status: Could be “suspended,” indicating it’s waiting on a lock.
• waittime: How long (in milliseconds) this session has been waiting.
• waitresource: Which resource this session is trying to acquire (e.g., a key lock on a specific table).
• lasttranstarted: When the current transaction started.
• isolationlevel: Transaction isolation level (here, read committed).
• loginname & hostname: Which user and machine initiated the transaction.
• executionStack: Provides the T-SQL call stack and stored procedure calls.
• inputbuf: Shows the query (or stored procedure call) that was executed.

Example Interpretation

<process id="process292009b2ca8" ... spid="87" ... >
  <executionStack>
    <frame procname="testdb.dbo.sp_UpdateTableB" line="33" ...>
      UPDATE dbo.TableA
      SET Column1 = CONCAT('UpdatedA_by_Session_', @@SPID)
      WHERE ID = @I
    </frame>
    <frame procname="adhoc" line="2" ...>
      EXEC dbo.sp_UpdateTableB @ID = 1, @DelaySeconds = 1
    </frame>
  </executionStack>
  <inputbuf>
    EXEC dbo.sp_UpdateTableB @ID = 1, @DelaySeconds = 12;
  </inputbuf>
</process>

• The session with spid="87" is running a stored procedure named sp_UpdateTableB, which in turn updates TableA.
• The inputbuf tells us the exact command that was sent: EXEC dbo.sp_UpdateTableB @ID = 1, @DelaySeconds = 12;

Similarly, another <process> element (spid="61") is calling sp_UpdateTableA, which updates TableB.

When these two sessions simultaneously attempt to update each other’s tables (A and B), a cyclical locking scenario is created, leading to a deadlock.

4. The <resource-list> Section

<resource-list>
  <keylock hobtid="72057594046382080" dbid="20" objectname="testdb.dbo.TableA" ...>
    <owner-list>
      <owner id="process292009bb088" mode="X"/>
    </owner-list>
    <waiter-list>
      <waiter id="process292009b2ca8" mode="X" requestType="wait"/>
    </waiter-list>
  </keylock>
  <keylock hobtid="72057594046447616" dbid="20" objectname="testdb.dbo.TableB" ...>
    <owner-list>
      <owner id="process292009b2ca8" mode="X"/>
    </owner-list>
    <waiter-list>
      <waiter id="process292009bb088" mode="X" requestType="wait"/>
    </waiter-list>
  </keylock>
</resource-list>

Here you see two keylock elements, each representing a locked resource:

1. Keylock on TableA
   • Owned by process292009bb088 (spid="61").
   • Waited on by process292009b2ca8 (spid="87").
2. Keylock on TableB
   • Owned by process292009b2ca8 (spid="87").
   • Waited on by process292009bb088 (spid="61").

This cyclical pattern of “owner” and “waiter” is the hallmark of a deadlock: each process owns a lock the other needs, and neither can proceed until the other releases it. The mode="X" attribute indicates both processes are waiting for an exclusive lock.

5. Putting It All Together

1. Identify the VictimIn the <victim-list> tag, look for which process was chosen to be rolled back.
2. Review Each ProcessThe <process-list> shows what each session was doing, including stored procedures, line numbers, and queries.
3. Check ResourcesThe <resource-list> clarifies which locks each process holds and which they are requesting. Notice the cyclical dependency where one session holds a lock on one table while the other holds a lock on another table.
4. Root CauseOften, deadlocks stem from application logic that updates tables in different orders. By consistently ordering updates (for example, always update TableA before TableB in every transaction), you can reduce the likelihood of cyclical locks.

6. Best Practices to Avoid or Resolve Deadlocks

• Consistent Locking OrderEnsure that all queries or procedures acquire locks in the same order (e.g., always update TableA, then TableB).
• Keep Transactions ShortThe less time you hold locks, the less likely you’ll encounter a deadlock.
• Use Proper IndexesWell-designed indexes help queries acquire locks on fewer rows, reducing contention.
• Retry LogicIn applications, implement a retry mechanism for deadlock victims. A short pause and retry can often succeed once the other transaction completes.

Accessing the system_health Event Output

• SQL Server Management Studio (SSMS) Extended Events UI
  • In Object Explorer, expand Management > Extended Events > Sessions.
  • Right-click system_health and choose Watch Live Data (or View Target Data if available).
  • Filter or search for xml_report events that contain the deadlock XML.

By reviewing these deadlock reports, you can pinpoint the processes, resources, and queries involved, then make changes to prevent or reduce future deadlocks.

Final Thoughts

Deadlocks are a normal occurrence in high-concurrency systems but must be managed effectively. The system_health Extended Events session captures snapshots of the involved processes, locks, and queries. By understanding how to read the <deadlock> XML, you can troubleshoot and optimize your SQL Server environment for better concurrency and fewer unexpected rollbacks.