How to Capture SQL Server Deadlocks Using Extended Events

Deadlocks are one of the worst scenarios a DBA will face. They happen when two sessions are fighting for resources and each one is holding a lock the other needs. At that point, the SQL Server engine will choose one session to kill — this is called the deadlock victim.

The reality is you cannot eliminate deadlocks entirely; they’re part of SQL Server’s concurrency model. The goal is to reduce their frequency and impact as much as possible.

Common causes of deadlocks include:

• Long-running queries caused by poorly written SQL
• Very large DDL operations affecting millions of rows
• Incorrect or overly strict transaction isolation levels
• Wait types tied to underlying hardware bottlenecks

How to Capture and Address Deadlocks

To troubleshoot deadlocks properly, you need to capture and analyze them. The main options are:

1. Extended Events – log and analyze deadlocks directly.
2. Vlad Mihalcea’s PSBlitz (PowerShell) – find and report deadlocks.
3. sp_Blitz (Brent Ozar’s scripts) – detect and log deadlocks.
4. Trace Flags – enable the relevant trace flag to write deadlock info into the SQL Server error log.

Do you want me to also rewrite this into a training-style step-by-step guide (like something you’d drop in a runbook for junior DBAs), or keep it more as a high-level explanation?

Capture deadlock using extended event

extended event is great way to capture deadlock , in this section we will configure deadlock to capture deadlock and capture xml type to see image of deadlock

open SSMS and under management folder expand extended event right click on session and select new Session

Give a  name for session then click Events

in event library type “deadlock ” , select lock_deadlock and lock_deadlock _chain and xml_deadlock_report

click “Configure” button the select all Selected events look for (sql_text , client _app_name, client_hostname ,database_name, session_id , username ) and checkmark it

Now click on Data Storage on left panel and then define where you want the logged event to be stored the click Ok

The Extended event is created to start the session just right click on it and select Start Session

To check if it working fine i have downloaded hammerdb which benchmark utility
I will use it to run meltable session “16” and then check the events session we have setup to see deadlock

Right click on session and select watch live data

You can see that there is deadlock captured

Select xml_deadlock_report you will get graph where It shows what are two session where competing and which session was selected as victim

From graph if we analyze it we can see that one session was asking exeslusive lock on object
object tbcc.dbo.order

hover your mouse over the right session to see the query running

you can also double click on xml repot to get  full query details to see what is reason for this deadlock

You can see the first season the victims  session was running select query on tbcc.dbo.order with hint “WITH (repeatableread)” Prevent shared lock form reading the table while this session is running

SELECT order_line.ol_i_id
            , order_line.ol_supply_w_id
            , order_line.ol_quantity
            , order_line.ol_amount
            , order_line.ol_delivery_d
            FROM dbo.order_line WITH (repeatableread)
            WHERE order_line.ol_o_id = @os_o_id
            AND order_line.ol_d_id = @os_d_id
            AND order_line.ol_w_id = @os_w_i 
            
           
EXEC ostat @os_w_id = @P1, @os_d_id = @P2, @os_c_id = @P3, @byname = @P4, @os_c_last = @P    

(@P1 int,@P2 int,@P3 int,@P4 int,@P5 char(20))EXEC ostat @os_w_id = @P1, @os_d_id = @P2, @os_c_id = @P3, @byname = @P4, @os_c_last = @P5

The other session was running the below query

INSERT dbo.orders( o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local)
            VALUES ( @o_id, @no_d_id, @no_w_id, @no_c_id, @TIMESTAMP, @no_o_ol_cnt, @no_o_all_local    
  
EXEC neword @no_w_id = @P1, @no_max_w_id = @P2, @no_d_id = @P3, @no_c_id = @P4, @no_o_ol_cnt = @P5, @TIMESTAMP = @P   
(@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 datetime2)EXEC neword @no_w_id = @P1, @no_max_w_id = @P2, @no_d_id = @P3, @no_c_id = @P4, @no_o_ol_cnt = @P5, @TIMESTAMP = @P6

Root cause looks like the transaction isolation level. In other scenarios it could be a poorly written query, a one-off DDL change run outside a batch, or a hardware bottleneck. Also review wait types.