SQL Server blocking happens when one session holds a lock that another session needs. The waiting session sits idle until the lock is released. SQL...
SQL Server blocking happens when one session holds a lock that another session needs. The waiting session sits idle until the lock is released. SQL Server works this way by design.
Blocking turns into a problem when the wait runs longer than your application can absorb. Some blocks clear on their own. Others hold until you kill the session at the head of the chain.
This guide walks through the DMV queries that expose the head blocker, the trap of blocks that clear before you can log in, and the four root causes behind chronic blocking. It then moves to the fixes that hold, from KILL to Read Committed Snapshot Isolation.
If you manage SQL Server instances, here is what you can put to work from this guide.
Locking is the mechanism. SQL Server’s lock manager acquires shared and exclusive locks on rows, pages, or objects to enforce transaction isolation. Every concurrent workload involves locks, and locking itself is a normal part of that work.
Blocking is the consequence of lock contention. It occurs when one session holds a lock that a second session needs, and the second session waits until the first releases. Microsoft documents this as an unavoidable, by-design characteristic of any relational database management system with lock-based concurrency (KB 224453). It becomes a concern only when the wait extends beyond what the application tolerates.
Deadlocking is a circular dependency. A deadlock happens when two sessions each hold a resource the other needs, so neither can proceed. SQL Server detects the cycle and automatically terminates one session as the victim. This distinguishes deadlocking from blocking, where no automatic resolution occurs and the chain persists until the head blocker releases or is terminated. For a fuller comparison, see understanding blocking versus deadlocks.
SQL Server exposes live engine state through its dynamic management views (DMVs), and a handful of them cover the reactive workflow.
sys.dm_exec_requests is the correct first reactive check. Filter on blocking_session_id <> 0 to surface only sessions currently waiting on another. Joining to sys.dm_exec_sql_text recovers the executing statement for each waiter.
SELECT session_id, blocking_session_id, wait_type, wait_time, sql_handle
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
sys.dm_os_waiting_tasks confirms the current wait type. When dm_exec_requests shows a blocked SPID (session ID) but the wait detail is unclear, this DMV isolates the lock wait, most often LCK_M_S or LCK_M_X.
SELECT session_id, wait_type, wait_duration_ms, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE ‘LCK%’;
sys.dm_os_wait_stats reveals chronic blocking at the instance level. This is the cumulative signal. Rising LCK_M_X and LCK_M_S counts across a time window point to structural blocking rather than a one-off event. Track these against a wait statistics baseline.
sp_who2 is the fastest native scan. Run EXEC sp_who2; for a quick assessment when you need an immediate look. It shows no wait type detail, no blocking chain, and does not scale to systematic diagnosis, so treat it as a first glance and move to the DMVs for anything deeper.
The head blocker is the session whose own blocking_session_id equals 0 while other sessions point their blocking_session_id at its SPID. To trace the chain, start from any waiting session, follow its blocking_session_id to the next SPID, and repeat until you reach a session with blocking_session_id = 0. That session sits at the root of the chain.
One detail catches many DBAs. The head blocker may show no active query in sys.dm_exec_requests at all, because it holds an open transaction without an executing statement. That absence is itself a diagnostic signal, and the analyze blocking for SQL Server walkthrough covers how to read it.
Set the blocked process threshold first. The sp_configure ‘blocked process threshold’ setting defines a duration in seconds after which SQL Server fires a blocked process report event. This is the prerequisite for any proactive capture. Setting the threshold to 0 disables the event.
EXEC sp_configure ‘blocked process threshold’, 15;
RECONFIGURE;
Pair the threshold with an Extended Events session. Target the blocked_process_report event and persist it to a file target. The session captures the full blocking chain, including the head blocker’s query text and the waiting session’s query text, even when no DBA is present. This closes part of the gap manual DMV queries leave open.
Manual queries fail here by design. DMV checks require an active DBA watching the instance. Intermittent blocks that resolve within seconds leave no trace a manual query can find after the fact, because the condition clears before anyone can investigate.
Microsoft documents this moving-target behavior, with blocks shifting across SPIDs and resources faster than a person can query them. The Extended Events approach creates an auditable history, though someone still has to retrieve and interpret the file target after the incident, a limitation that the continuous monitoring section returns to.
A cleared block will re-form unless you find its cause.
Four patterns account for most chronic blocking, and each leaves a distinct signature in DMV data.
| Root cause | DMV fingerprint | Why it creates blocking |
| Long-running transactions | High open_transaction_count and long elapsed time on the head blocker; lock-holding statement may no longer appear in sys.dm_exec_requests | Exclusive locks stay alive for the full transaction duration, blocking any reader or writer needing the same rows |
| Missing or incomplete indexes | High wait_time on LCK_M_S or LCK_M_IX combined with high logical_reads on the head blocker’s statement | Without an index seek, the engine scans and escalates to page-level or table-level locks that block every session needing those rows |
| Overly restrictive isolation levels | LCK_M_RIn_X or LCK_M_RX_X wait types under SERIALIZABLE; extended shared-lock windows under REPEATABLE READ | SERIALIZABLE range locks conflict with INSERTs on the locked range; REPEATABLE READ holds shared locks until transaction end |
| Open transactions from application error handling | No executing query and no wait type on the head blocker’s sys.dm_exec_requests row, yet other sessions stay blocked | An exception without a ROLLBACK leaves every acquired lock held indefinitely |
Transactions that hold locks across large data sets, or that include user interaction inside the transaction boundary, keep exclusive locks alive for the full transaction duration. The lock-holding statement may already have completed while the transaction stays open, so it no longer appears in sys.dm_exec_requests even though the locks remain. The locks, blocks, and deadlocks breakdown expands on this pattern.
When SQL Server cannot satisfy a query with an index seek, it scans and acquires page-level or table-level locks instead of row-level locks. A table-level lock blocks every session needing any row in that table, which is why a single unindexed query can stall an otherwise healthy workload.
SERIALIZABLE adds range lock acquisition to prevent phantom reads, and those range locks conflict with INSERT statements on the locked range, generating blocking that does not occur under READ COMMITTED. REPEATABLE READ also holds shared locks until transaction end, extending the lock-hold window beyond what most workloads need.
When an application raises an exception inside a transaction and never issues a ROLLBACK, SQL Server holds every lock the transaction acquired indefinitely. The head blocker’s sys.dm_exec_requests row shows no executing query and no wait type, a diagnostic signal that matches only this pattern.
Confirm it by checking open_transaction_count in sys.dm_exec_sessions. Resolution requires terminating the SPID, because the application cannot recover the transaction on its own.
The fixes below split into an immediate one that clears the chain in front of you and structural ones that stop it from recurring.
KILL {session_id} terminates the head blocker and rolls back its open transaction. The rollback runs proportional to the transaction size, so a large uncommitted transaction can take time to unwind.
Confirm the correct head blocker SPID from DMV output before issuing KILL. Terminating the wrong session does not clear the chain and can introduce data inconsistency.
RCSI is the most broadly effective structural change for most environments. Read Committed Snapshot Isolation routes read operations to row versions stored in tempdb rather than acquiring shared locks, which eliminates reader-writer blocking without application changes. As Michael J. Swart argues, it resolves most locking and blocking issues without touching application code.
Writers still acquire exclusive locks, so RCSI leaves write-write contention unresolved. Expect a brief online period as tempdb populates, during which blocking may temporarily rise.
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON;
Index optimization reduces lock granularity. Adding or restructuring indexes to support seeks rather than scans drops lock acquisition from page or table level to row level. Filtered indexes and included columns can eliminate scan-based contention on high-traffic tables without adding maintenance overhead disproportionate to the write workload.
Transaction design keeps lock-hold durations short. Move user interaction outside the transaction scope, batch large DML into smaller units with explicit commit points, and avoid nested transactions that obscure rollback behavior. Shorter transactions release locks sooner, which is where most database performance tuning gains originate.
NOLOCK is an anti-pattern to apply with caution. NOLOCK (READ UNCOMMITTED) eliminates shared lock acquisition but allows dirty reads, meaning uncommitted rows that may be rolled back, missing rows mid-scan, and duplicate rows during page splits. These correctness problems often exceed the performance benefit. Reserve it for narrow analytical scenarios with a documented tolerance for read inconsistency.
Manual DMV queries require the DBA to be present and watching. They cannot surface incidents that resolve in under a minute, and they do not scale across dozens of instances. Extended Events closes part of the gap but still leaves someone retrieving and interpreting file targets after the fact. Continuous monitoring maps a specific capability to each of those failure modes:
Instead of running queries after a user complains, you receive an alert before application timeouts accumulate. Product detail lives on the SQL Diagnostic Manager page.
Optimized Locking introduces transaction ID (TID) locks alongside the traditional lock manager hierarchy, allowing readers to detect whether a row’s transaction is still active without acquiring a shared lock on the row itself. This reduces LCK_* wait volume significantly in environments running RCSI.
LAQ holds off on exclusive lock acquisition until the query engine confirms a row satisfies the WHERE clause. Traditional behavior locks before qualification, holding locks on rows that may be discarded. LAQ reduces the total number of locks held at any point and reduces contention on high-DML tables.
Optimized Locking requires RCSI; without it, the TID locking mechanism cannot function. For anyone monitoring these environments, LCK_* wait counts will fall substantially, so wait statistics baselines built on pre-2025 instances will not transfer. IDERA SQL Diagnostic Manager’s wait-state analysis adapts to the reduced LCK_* wait profile these environments exhibit, keeping the wait statistics view meaningful after migration.
SQL Server blocking is expected behavior, and managing it comes down to persistent visibility. The DMV queries and Extended Events sessions covered here still depend on someone being there to run them.
SQL Diagnostic Manager captures what manual queries miss, the blocks that clear in seconds before you can investigate and the chains that span more instances than a single query can scan. The next block on your instances will not wait for you to be watching.
Start a free 14-day trial of SQL Diagnostic Manager and see the full chain the moment it forms.
There is no fixed threshold, because normal depends on what your applications tolerate. Brief waits measured in milliseconds are routine on any busy instance, and most teams only investigate once waits approach the application timeout. A blocked process threshold of 5 to 15 seconds is a common starting point for alerting.
Yes. Under the default READ COMMITTED isolation level, a SELECT acquires shared locks, so a long read can block writers just as a writer can block readers. Enabling Read Committed Snapshot Isolation removes most of this reader-writer contention by serving reads from row versions.
A slow query is actively consuming CPU, memory, or I/O, while a blocked query is idle and waiting for a lock to release. You can tell them apart by the wait type, since a blocked session reports an LCK_* wait in sys.dm_os_waiting_tasks. The distinction matters because query tuning fixes the first and lock management fixes the second.
The main cost is tempdb. Row versioning adds a 14-byte pointer to modified rows and stores the versions in tempdb, so long-running transactions can grow the version store. Test it against workloads with heavy updates, and confirm the application does not depend on readers waiting for in-flight changes.
Contact our sales team to get a personalized demo of our database management software for SQL Server!
