$ guides / microsoft-sql-server / microsoft-sql-server-lock-escalation ▌

Operations Guides

SQL Server lock escalation: when row locks become a table lock and block everyone

A batch UPDATE or DELETE runs longer than usual. CPU looks fine, I/O looks fine, and then dozens of sessions start waiting on LCK_M_* waits, all blocked by the batch session. By the time you log in, the worker thread pool is draining and the instance is heading toward THREADPOOL. The root cause is not a hung transaction or a resource bottleneck. It is lock escalation: SQL Server traded thousands of row locks for a single table lock, and every other session that wants to touch that table is now serialized behind it.

Lock escalation is a memory conservation mechanism, not a bug. The lock manager cannot track an unbounded number of locks per transaction. When a single statement crosses a threshold on a single table reference, the engine escalates the entire set of row and page locks on that HoBt (heap or B-tree) to one TABLE-granularity lock. The savings in lock memory come at the cost of concurrency. On a large, intended batch operation that is the right trade. On a transaction that was supposed to touch a few hundred rows but is scanning millions because of a bad plan, escalation is the symptom that turns a slow query into a system-wide stall.

This article covers how to recognize escalation while it is happening, what triggers it, how to distinguish it from ordinary blocking and deadlocks, and how to prevent recurrence.

What this means

Lock escalation is the engine converting many fine-grained locks held by one transaction on one table reference into a single coarse-grained lock on that table. The escalated lock is always at TABLE granularity (shared or exclusive, depending on the locks being escalated), never at page granularity. Once escalation happens, every other session that needs a conflicting lock on that table waits behind the holder until the transaction commits or rolls back.

The trigger most operators learn first is the lock-count threshold: when a single statement holds roughly 5000 row or page locks on a single non-partitioned table or index reference, the lock manager escalates. In practice the mechanism fires a little higher, because the lock manager checks at fixed intervals of held-lock count, and the HoBt-level counter that drives the decision excludes the table lock itself and the lock currently being acquired. A simple scan that takes locks one at a time typically escalates around 6250 total held locks rather than exactly 5000. Monitoring for exactly 5000 locks held will miss the event.

A second trigger is less obvious. If lock memory exceeds roughly 40 percent of the lock memory cap (which itself is about 60 percent of the buffer pool, so roughly 24 percent of buffer pool size), escalation can fire on a statement regardless of how many locks it personally holds. This memory-pressure escape valve explains why escalation can appear on workloads that never seem to cross the row-count threshold.

flowchart TD
    A[Batch UPDATE/DELETE starts] --> B[Row/page locks accumulate]
    B --> C{~5000-6250 locks
on one HoBt?}
    C -- No --> B
    C -- Yes --> D[Lock manager escalates
to single TABLE lock]
    D --> E[Row/page locks released,
TABLE lock held to commit]
    E --> F[Other sessions request
conflicting locks]
    F --> G[LCK_M_* waits rise]
    G --> H[Workers pile up]
    H --> I[THREADPOOL waits
if pool drains]

The signature that distinguishes escalation from ordinary blocking is the granularity of the held lock. Ordinary blocking chains are row, key, or page locks on specific objects. Escalation produces a TABLE lock held by one session while many unrelated sessions wait. The signature that distinguishes it from a deadlock is that there is no cycle: the head blocker is making progress (or will, once it commits), and the deadlock monitor will not intervene.

Common causes

Cause	What it looks like	First thing to check
Large batch UPDATE/DELETE with no batching	One session holds X table lock, many sessions wait on `LCK_M_X`	`sys.dm_exec_requests` for the head blocker statement and affected row estimate
Plan regression scanning more rows than intended	Statement that usually touches hundreds of rows now holds millions of locks	Query Store plan comparison for the regressed query
Missing supporting index on the predicate	UPDATE or DELETE plan shows a clustered index scan	Execution plan, look for a scan where a seek was expected
SELECT escalating to a shared table lock	Read query under read committed with a bookmark lookup using PREFETCH	`resource_description` of the escalated lock in `dm_tran_locks`
UPDLOCK scan where no rows qualify	U locks held on scanned rows, then X table lock even though zero rows matched	Whether the predicate is sargable and supported by an index
LOB columns accessed by reference	SELECT projecting `varchar(max)` / `nvarchar(max)` escalates unexpectedly	Schema for LOB columns and the access method in the plan
Memory pressure trip	Escalation on a session holding far fewer than 5000 locks	`MEMORYCLERK_SQLBUFFERPOOL` size and `process_physical_memory_low`

Quick checks

These are read-only and safe to run during an active incident.

-- Find current blocking chains and the head blocker
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocker,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    DB_NAME(r.database_id) AS database_name,
    t.text AS blocked_query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- Inspect the lock granularity held by the head blocker
-- A resource_type of OBJECT with request_mode S or X is the escalation signature
SELECT
    tl.resource_type,
    tl.resource_subtype,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,
    tl.request_session_id
FROM sys.dm_tran_locks tl
WHERE tl.request_session_id = <head_blocker_session_id>
ORDER BY tl.resource_type;

-- Confirm whether the head blocker is the source or itself blocked
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    s.last_request_start_time,
    t.text AS last_query_text,
    (SELECT COUNT(*) FROM sys.dm_exec_requests r2
     WHERE r2.blocking_session_id = s.session_id) AS sessions_blocked
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t
WHERE s.session_id = <head_blocker_session_id>;

-- Check the wait profile. A surge of LCK_M_* waits
-- with low CPU and low PAGEIOLATCH points to blocking, not resource starvation
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LCK_M_%'
  AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

How to diagnose it

Confirm you are in a blocking pattern, not a resource bottleneck. CPU low, I/O low, many sessions suspended on LCK_M_* waits in sys.dm_exec_requests. If PAGEIOLATCH_* or SOS_SCHEDULER_YIELD dominates instead, escalation is not your problem.
Identify the head blocker. Use the blocking-chain query above. Note whether the head blocker is running (making progress) or sleeping (idle with an open transaction). A sleeping head blocker is a different problem: an uncommitted transaction. An escalating head blocker is running a large DML statement.
Check the lock granularity the head blocker holds. Query sys.dm_tran_locks for that session. The escalation signature is a single OBJECT-granularity lock in mode S or X on the table, with the previous row and page locks released.
Capture the escalating statement. Pull sql_handle from sys.dm_exec_requests for the head blocker and get the plan via sys.dm_exec_query_plan. Look for a scan or a seek that is estimated to touch far more rows than the application intended.
Confirm with Query Store. If Query Store is enabled, look for the same query_hash over the last several days. A regression from thousands of logical reads to millions, coinciding with a plan change, is the smoking gun.
Verify against an Extended Events session for lock_escalation. The system_health session does not capture this by default; you will need a dedicated session. Microsoft documents the lock_escalation event as the way to monitor escalation but publishes no session template, so a dedicated session is the standard approach.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`LCK_M_*` waits as a share of total waits	Lock waits are the direct symptom of any blocking pattern	Sudden rise above 30 to 40 percent of total waits with no maintenance window
Blocking chain depth and duration	The cascade that turns a slow query into worker exhaustion	Chain deeper than 5 sessions, or any single block over 60 seconds
Worker thread utilization	Escalation cascades consume workers one blocked session at a time	Active workers sustained above 80 percent of `max_workers_count`
Batch requests/sec vs transactions/sec ratio	Requests arrive but nothing commits during a blocking cascade	Batch requests stable or rising while transactions/sec drops
Head blocker status	A sleeping head blocker needs different remediation than a running one	Head blocker `status = sleeping` with `open_transaction_count > 0`
`lock_escalation` Extended Events	Definitive confirmation that escalation occurred	Any event during business hours on a production table

Fixes

Batch the large DML

The most reliable fix is to not cross the threshold. Rewrite single huge UPDATE or DELETE statements as loops that process rows in chunks of a few thousand, with BEGIN TRAN and COMMIT around each chunk. Each chunk stays under the escalation threshold, releases its locks at commit, and lets blocked sessions proceed. Choose a chunk size with headroom below 5000 locks per reference, not exactly at it.

Add the index the plan expected

If the escalating plan is scanning because the predicate is not supported by an index, adding a covering index turns a table scan into a targeted seek. Fewer rows touched means fewer locks taken means no escalation. This is the highest-leverage long-term fix because it also reduces I/O, memory grant pressure, and log volume.

Correct the plan regression

When Query Store shows the query used to seek and now scans, force the known-good plan with sp_query_store_force_plan (revertible with sp_query_store_unforce_plan). Alternatively, clear the bad plan with DBCC FREEPROCCACHE(plan_handle). This is disruptive: it forces recompilation on the next call, which can spike CPU if the query is hot. Long-term, address the underlying statistics staleness or parameter sniffing with OPTIMIZE FOR hints, OPTION (RECOMPILE), or Query Store automatic plan correction where available.

Tame read-side escalations

SELECT statements can escalate too, particularly under read committed with bookmark lookups using PREFETCH, or when projecting LOB columns accessed by reference. Adding a covering index that satisfies the SELECT without a lookup, or revisiting whether the query needs to project LOB columns, removes the lock accumulation.

Set LOCK_ESCALATION per table, surgically

ALTER TABLE ... SET (LOCK_ESCALATION = ...) controls behavior per table.

TABLE (default): escalation goes to the table, as described above.
AUTO: on a partitioned table, escalation targets the partition instead of the whole table. Useful when a large operation touches one partition but should not block access to other partitions.
DISABLE: escalation is suppressed for that table in most cases. It can prevent the cascade you are seeing, but it forces the lock manager to keep tracking row and page locks, which costs memory and can move you toward the memory-pressure trigger instead. Use it on specific hot tables after batching and indexing have been considered.

Trace flags 1211 and 1224, as a last resort

Trace flag 1211 disables lock escalation globally. Trace flag 1224 disables the lock-count trigger but still allows escalation under memory pressure. If both are set, 1211 takes precedence. These are instance-wide and change the concurrency behavior of every database on the instance. They are appropriate for specific, well-understood workloads and dangerous as a default. The ROWLOCK hint is not a substitute: it affects initial lock granularity but does not prevent escalation.

Prevention

Batch every large DML by default. Treat any single statement that could touch more than a few thousand rows as a code smell. This single discipline prevents most escalation incidents.
Make sure predicates are indexable. Non-sargable predicates (functions on the column, implicit conversions, leading wildcards) force scans, and scans on large tables force escalation.
Watch plan regressions before users do. Query Store is the right tool. Alert on queries whose average duration or logical reads jumped by an order of magnitude after a plan change.
Do not disable escalation globally as a precaution. The memory pressure escape valve exists for a reason. Disabling it instance-wide trades one failure mode for another that is harder to diagnose.
On SQL Server 2025 and later, evaluate Optimized Locking. Optimized Locking (TID-based locking combined with Lock After Qualification) releases row locks immediately after each row is modified and holds a single lock on the transaction ID until commit. With it enabled, escalation becomes far less likely because the number of held locks at any instant is dramatically lower. It must be enabled per database and requires Accelerated Database Recovery. Microsoft documents Optimized Locking for SQL Server 2025 (not for SQL Server 2022 or older); the availability table lists no edition restriction.
Track escalation events over time. A standing Extended Events session for lock_escalation, even if it only writes to a ring buffer for spot checks, lets you see whether a code change actually reduced escalation frequency or just moved it to a different table.

How Netdata helps

Correlate the escalation signature directly. When LCK_M_* wait time spikes while CPU utilization and PAGEIOLATCH_* stays low, per-second metrics make the blocking pattern visible immediately rather than something you reconstruct after the fact from cumulative DMVs.
Watch the cascade form in real time. Per-second blocking chain depth, worker thread utilization, and the batch-requests-versus-transactions divergence are the leading indicators that an escalation has begun cascading toward THREADPOOL.
Distinguish escalation from resource stalls. Lock escalation looks like the database is frozen with the CPU idle. Memory pressure and I/O saturation look similar from the outside. Correlated resource panels (CPU, memory grants pending, I/O stall, PLE per NUMA node) let you rule those out without a second tool.
Baseline wait statistics as a time series. sys.dm_os_wait_stats is cumulative since startup. A single snapshot is nearly useless for diagnosing current behavior. Persisted, sampled wait stats let you see the moment LCK_M_* overtook other waits, which is the moment escalation began.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and ML anomaly detection.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free