Lock escalation

In one line: when a single statement takes enough row or page locks, SQL Server swaps them for one lock on the whole table (or partition). Every other session is then locked out of every row of the table until the transaction ends.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/lock-escalation on GitHub.

What you’ll see

A large UPDATE or DELETE (a purge, a backfill, a nightly job) and everything else touching that table blocks behind it, including sessions after rows the statement never touched.
In sys.dm_tran_locks, the blocking session holds a single OBJECT lock in mode X (or S for a reader) instead of thousands of KEY locks.
index_lock_promotion_count climbing in sys.dm_db_index_operational_stats.

Why it happens

Each lock costs memory, and the lock manager has to track them all. Once a statement holds roughly 5,000 locks on one table, SQL Server tries to trade them all for a single table lock. Three things decide whether it happens:

The count is per statement, not per transaction. Four 5,000-row updates in one transaction never escalated, even with 20,000+ locks held.
The threshold is approximate. On the tested build, the first escalation came at just over 6,250 locks, not 5,000. Don’t design to the number.
Readers can escalate too, under REPEATABLE READ, SERIALIZABLE or HOLDLOCK, because their shared locks are kept until commit. The result is a shared table lock that blocks every writer.

What to do

Batch the work. Modify a few thousand rows per statement, and commit between batches so the locks and the log are released.
Partition and set LOCK_ESCALATION = AUTO. On a partitioned table it escalates to the partition, and rows in other partitions stay available.
LOCK_ESCALATION = DISABLE on the one table that needs it. You keep row-level concurrency and pay in lock memory, which is fine at tens of thousands of locks but not at millions.
READ_COMMITTED_SNAPSHOT stops ordinary readers from waiting on the escalated lock. Writers still block each other.

What doesn’t help

WITH (ROWLOCK). It sets the granularity a statement starts with, and 7,000 row locks still escalated exactly as they did without the hint.
Trace flags 1211 and 1224. They work, but for the whole instance. Use the per-table option instead.

Check your server

-- Tables where SQL Server has escalated locks since the last restart.
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id)        AS table_name,
       s.index_id,
       s.index_lock_promotion_attempt_count,
       s.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS s
WHERE s.index_lock_promotion_count > 0
ORDER BY s.index_lock_promotion_count DESC;

See it happen

The lock-escalation demo runs about 50 probes in a few seconds. It shows the threshold, the per-statement rule, ROWLOCK and PAGLOCK, readers escalating, and the DISABLE and AUTO table options, and checks each result against a prediction.

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.