Free resource

SQL Server Blocking & Deadlock Troubleshooting Guide

A practical guide for working DBAs: identify blocking chains, interpret deadlock graphs, use trace flags 1222/1204, configure extended events, and apply the 5-step diagnostic approach to resolve blocking fast.

~12-page guide

SQL Server 2016–2022

5-step diagnostic method

Trace flags 1222 & 1204

🔒

Lock Types & Escalation

Reference

Row locks (KEY, RID) vs. page locks (PAG) vs. table locks (TAB)
Intent locks (IS, IX, SIX) — how they signal escalation
LCK_M_U, LCK_M_S, LCK_M_X — what they mean in deadlock graphs
Lock escalation threshold and when SQL Server auto-escalates
Table lock prevention: SET LOCK_ESCALATION = DISABLE

🛡

Victim Selection Logic

Deep dive

How SQL Server chooses the deadlock victim — cycle detection
DEADLOCK_PRIORITY — LOW/NORMAL/HIGH and numeric range
Impact of session_cost and batch_cost in victim selection
Setting per-session priority to protect critical transactions
Testing priority behavior: simulate deadlock conditions

💻

Trace Flags & Extended Events

Tools

TF 1222: XML deadlock graph format — parsing with SSMS
TF 1204: human-readable deadlock output to ERRORLOG
Extended Events: deadlock_ring_buffer_target vs. xml_file_target
Capturing only new deadlocks without rolling XE sessions
Automated alerting: Query the ring buffer via sys.dm_xe_session_results

🎯

5-Step Diagnostic Method

Framework

Step 1: Identify the blocker — sp_who2 or sys.dm_exec_requests
Step 2: Pull the wait resource — what lock is it holding?
Step 3: Extract the query text — blocking_session_id SPID
Step 4: Review the execution plan — missing indexes, wide scans
Step 5: Apply the fix — index, query rewrite, or partition

🔬

Blocking Chain Analysis

Techniques

Recursive blocking: head blocker vs. leaf nodes
Identifying long-running uncommitted transactions
open_tran count — tracking nested transactions
tempdb contention causing blocking — SOS_SCHEDULER_YIELD
Automation: scheduled check for blocking > 30 seconds

🚀

Query Design Anti-Patterns

Prevention

Wide scan + ORDER BY = table lock under concurrent load
Procedures with implicit transaction wrapping every DML
Batch commits vs. statement-level control — which is safer
NOLOCK hints vs. RCSI — when each creates worse problems
Partition-level lock escalation: the 2022 fix and how to enable

Get the Full Guide as a PDF

Download the complete guide — formatted for printing and keeping at your desk for the next blocking incident.