Common Causes of SQL Server Blocking

Application timeouts during peak load, queries that are fast in isolation but slow during business hours, operations that work fine overnight but fail when users are active — these are common symptoms of SQL Server blocking. Blocking is not a database bug; it is the correct behaviour of a system designed to maintain data consistency. But when it becomes excessive, it degrades application performance significantly.

Understanding the common causes helps both in resolving existing problems and in avoiding them in new development.

What Blocking Is

When one session holds a lock on a resource — a row, a page, or a table — and another session needs to acquire an incompatible lock on the same resource, the second session waits. It cannot proceed until the first session releases the lock. This is blocking.

Under concurrent load, chains form: session A blocks session B, session B blocks session C. The application sees timeouts across multiple users even though only one session (the head blocker) is the root cause. The head blocker is the session that all other blocked sessions are waiting on, directly or indirectly.

How to Identify Blocking

Use sys.dm_exec_requests to see currently running and waiting sessions. Rows where wait_type starts with LCK_M_ are waiting for a lock. The blocking_session_id column shows which session holds the lock they are waiting for. A non-zero blocking_session_id means that session is blocked.

sys.dm_os_waiting_tasks shows the full blocking chain: every session that is waiting, what resource it is waiting for, and which session holds that resource. This view is more useful than sys.dm_exec_requests alone when a chain involves multiple sessions.

To find the head blocker: look for a session with a non-zero blocking_session_id in sys.dm_exec_requests, and trace the chain upward until you find a session that itself has no blocker. That session is where the investigation starts.

For blocking that occurs intermittently rather than continuously, capture it with Extended Events. A session tracking the blocked_process_report event records blocking incidents as they occur, with the full query text of both the blocked and blocking sessions.

Cause 1: Long-Running Transactions

A transaction that stays open for an extended period holds its locks for that entire duration. This is the most common cause of severe blocking.

The pattern often looks like this: application code opens a transaction, performs some database work, then calls an external service, processes a response, or waits for user input — all while the transaction is still open. Any locks acquired during the database work are held for the entire duration, including the time spent outside the database.

Long-running transactions are also caused by batch operations that process large numbers of rows in a single transaction without checkpointing. An import job that processes 500,000 rows in one transaction holds shared or exclusive locks on all affected rows for the entire duration of the import.

The fix is to keep transactions as short as possible: acquire locks as late as possible, release them as early as possible, and never hold a transaction open across network calls, user interactions, or slow external operations.

Cause 2: Missing Indexes

A query without a suitable index has to read more data to find the rows it needs — typically a table scan or a clustered index scan. During that scan, SQL Server holds shared locks on the pages it reads. On a large table, that is a lot of locks, held for a significant amount of time.

An indexed query that does a seek reads only the relevant rows. Fewer locks, held for a shorter time. The difference in blocking impact between a table scan and an index seek is not just performance — it is also the width and duration of the locking footprint.

Missing indexes that cause blocking are often identifiable through the execution plan: look for Index Scan or Clustered Index Scan operators on large tables in queries that are appearing as blockers. Adding an appropriate index reduces both the query duration and the locking scope.

Cause 3: Read Committed Default Isolation Level

The default isolation level in SQL Server is READ COMMITTED. Under this isolation level, readers take shared locks on rows as they read them. Writers take exclusive locks. A reader and a writer on the same row cannot proceed simultaneously — one must wait.

This is the correct behaviour under READ COMMITTED, but in high-concurrency OLTP environments it causes significant read-write blocking: reporting queries blocking updates, or updates blocking queries trying to read current data.

Read Committed Snapshot Isolation (RCSI) changes this behaviour. When RCSI is enabled on the database, readers no longer take shared locks. Instead, they read a row version from the version store in TempDB — a consistent snapshot of the row as it was when the reading transaction began. Writers can update the row concurrently without blocking readers, and readers don't block writers.

Enabling RCSI requires a database-level change (ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON) and temporary TempDB space during the transition. No application code changes are required. It eliminates most read-write blocking without changing any application behaviour.

What RCSI does not fix: write-write blocking remains. Two sessions trying to update the same row still block each other. That requires a different approach.

Cause 4: Lock Escalation

SQL Server starts by acquiring row-level or page-level locks. As the number of locks held by a single statement grows, SQL Server may escalate those locks to a table-level lock — a single lock covering the entire table instead of thousands of individual row locks. This is lock escalation.

Escalation happens when the lock count in a session exceeds a threshold. It is a memory optimisation: holding one table lock uses less memory than holding 100,000 row locks. But it converts a narrow locking footprint into a full table lock, which blocks all other sessions trying to access any row in that table.

Lock escalation is often visible as sudden, broad blocking that affects many sessions simultaneously — every session trying to read or write the table is blocked. The fix is usually to reduce the number of rows affected by individual operations (process large batches in smaller chunks) or to add indexes that allow the operation to be completed with a smaller number of locks.

Cause 5: Implicit Transactions Not Being Closed

Some database drivers and connection configurations open implicit transactions. SET IMPLICIT_TRANSACTIONS ON, for example, causes SQL Server to open a transaction automatically when certain statements execute, and that transaction remains open until an explicit COMMIT or ROLLBACK.

Application code that is not aware of this setting can execute statements that open implicit transactions and then not commit them, leaving the transaction — and its locks — open indefinitely. This is particularly common in connection pooling environments where connections are reused: the implicit transaction opened by one request is still open when the next request uses the connection.

Identifying this pattern: look for sessions with an open transaction (non-zero open_transaction_count in sys.dm_exec_sessions) that have been idle for a significant time. An idle session with an open transaction is holding locks without doing anything useful.

What to Do

The investigation sequence for a blocking problem:

1. Identify the head blocker using sys.dm_exec_requests and sys.dm_os_waiting_tasks
2. Find the query the head blocker is running and how long the transaction has been open
3. Check whether the query has an appropriate index (look for table scans in the execution plan)
4. Determine whether the transaction is open longer than necessary — is it waiting for something outside the database?
5. If read-write blocking is widespread, evaluate whether RCSI is appropriate for the database
6. Set up Extended Events to capture blocking incidents over time if the problem is intermittent