SQL Server blocking and deadlock triage

#Blocking vs deadlock

• Blocking: session B waits for a lock held by session A. It lasts until A commits or rolls back, which could be forever.
• Deadlock: A and B each wait for the other. SQL Server detects this within about 5 seconds and kills one of them with error 1205.

#Who is blocking right now?

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time / 1000 AS wait_s,
       r.status, DB_NAME(r.database_id) AS db, s.login_name, s.host_name, s.program_name,
       SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
         (CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1) AS stmt
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

The head blocker is a session that blocks others but isn't blocked itself. It often has no current request because it's an open transaction that's sleeping:

SELECT s.session_id, s.status, s.login_name, s.host_name, s.program_name,
       s.last_request_end_time, t.text AS last_sql
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
  AND s.session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

Tip

If you can install it, sp_WhoIsActive @find_block_leaders = 1, @get_plans = 1 gives you the same answer with more detail.

#Should you kill it?

Before you run KILL <spid>:

1. Find out what it is. A user's SSMS session with an open transaction is safe to kill. An ETL job or an index rebuild may take a long time to roll back.
2. Check how much work has been done: sys.dm_tran_database_transactions.database_transaction_log_bytes_used. Rolling back takes about as long as the work did.
3. After the kill, check progress with KILL <spid> WITH STATUSONLY.

#Get the deadlock graphs

The built-in system_health session records them already:

SELECT x.value('(@timestamp)[1]', 'datetime2') AS utc_time,
       x.query('(data/value/deadlock)[1]') AS deadlock_graph
FROM (
  SELECT CAST(event_data AS xml) AS ev
  FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
  WHERE object_name = 'xml_deadlock_report'
) d
CROSS APPLY d.ev.nodes('/event') AS e(x)
ORDER BY utc_time DESC;

Save the XML as .xdl and open it in SSMS to see the graph. Note the objects, indexes and statements of both victim and survivor.

#Long-term fixes

Warning

RCSI increases tempdb (version store) usage. Turning it on can also change the behavior of code that relies on blocking reads. Test it before you enable it in production.