Diagnosing Blocking in SQL Server: Finding Who’s Blocking Whom

A deadlock (previous lesson) is SQL Server actively resolving an impossible situation by killing one transaction. Ordinary blocking is different and far more common: one session simply waiting its turn for a lock another session holds, which resolves on its own once the first session finishes — no error, no victim, just a delay. Not every case of one session waiting on another is a bug; moderate, short-lived blocking is normal under real concurrent load. Here’s how to tell when it’s actually a problem.

Finding the Blocker

SELECT
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time,
    blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.blocking_session_id <> 0;

This is the same blocking_session_id column flagged as the single most actionable field in Chapter 8’s DMV lesson — this query is that pointer, fully realized into a real diagnostic report.

The Real Root Cause, Most of the Time

It becomes a genuine problem when a transaction holds locks far longer than necessary. The fix is almost always “keep transactions as short as possible” — not “add more indexes” or “increase timeout,” which just makes users wait longer for a symptom instead of fixing the actual cause.

-- The specific anti-pattern that causes most real-world blocking incidents:
BEGIN TRANSACTION;
UPDATE dbo.Order SET status = 'processing' WHERE order_id = 500;
-- ...application code here calls an external API, waits on a user click,
-- or does anything else slow, all while the transaction (and its locks) stays open...
COMMIT TRANSACTION; -- doesn't happen until that slow thing finishes

-- The fix: do all slow, non-database work BEFORE or AFTER the transaction,
-- never DURING it. Keep the window between BEGIN and COMMIT as short as possible.

Chapter 9, End to End

These three lessons form one continuous story: ACID (Lesson 1) is the guarantee; isolation levels (Lesson 2) are the tunable dial controlling how strictly “Isolation” is enforced, with deadlocks as the sharp edge of getting concurrent access patterns wrong; and blocking (this lesson) is the everyday, non-error version of the same underlying mechanism — locks doing their job, just visible when they hold longer than expected.