Finding and Resolving Blocking Queries in SQL Server

What Is Blocking?

In SQL Server, blocking occurs when one session holds a lock on a resource (a row, page, or table) that another session needs. The waiting session is blocked — it cannot proceed until the first session releases its lock. Brief blocking is normal and expected. Chronic blocking that lasts seconds or minutes indicates a performance problem.

Common causes include: long-running transactions that don't commit promptly, missing indexes that force table scans while holding locks, and implicit transactions left open by application bugs.

Blocking vs Deadlock

These are related but different problems:

• Blocking — Session A holds a lock. Session B waits. Eventually A commits or rolls back, and B proceeds. B might wait a long time, but it will eventually get through.
• Deadlock — Session A holds lock X and wants lock Y. Session B holds lock Y and wants lock X. Neither can proceed. SQL Server detects this and kills one session (the deadlock victim) with error 1205.

Using the Query Builder

Ready-to-Run Diagnostic Queries

Find all current blocking chains

SELECT
  blocking.session_id       AS blocking_session,
  blocking.status           AS blocking_status,
  blocking_sql.text         AS blocking_query,
  waiting.session_id        AS waiting_session,
  waiting.wait_type,
  waiting.wait_time / 1000  AS wait_seconds,
  waiting_sql.text          AS waiting_query
FROM sys.dm_exec_requests waiting
INNER JOIN sys.dm_exec_sessions blocking
  ON waiting.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) AS blocking_sql
CROSS APPLY sys.dm_exec_sql_text(waiting.sql_handle) AS waiting_sql
WHERE waiting.blocking_session_id > 0
ORDER BY waiting.wait_time DESC;

Find the head of a blocking chain

-- Sessions that are blocking others but not themselves blocked
SELECT s.session_id, s.status, s.login_name, s.host_name,
       t.text AS current_query, s.open_transaction_count
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT DISTINCT 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);

Resolving Blocking

• Kill the blocker — KILL <session_id> terminates the blocking session and rolls back its transaction. Use as a last resort for critical production issues.
• Check for open transactions — run DBCC OPENTRAN to find the oldest open transaction. Applications that open transactions and don't commit them are a common culprit.
• Add missing indexes — long-running queries caused by table scans hold locks longer. Adding the right indexes shortens the lock duration.
• Reduce transaction scope — keep transactions as short as possible. Commit immediately after the work is done.

Prevention Strategies

• Use Read Committed Snapshot Isolation (RCSI) to allow readers to not block writers
• Keep transactions short — don't include user interaction inside a transaction
• Use NOLOCK hints cautiously — they prevent blocking but allow dirty reads
• Index foreign key columns to prevent escalation locks during CASCADE operations
• Monitor with Extended Events or Query Store for recurring blocking patterns

Frequently Asked Questions