SQL Server lock escalation blocking the whole table: find the statement that escalated and fix it

#Symptoms

• A purge, archive or bulk update job starts and the application stalls on one table, even for rows the job never touches.
• Blocked sessions wait on LCK_M_IX, LCK_M_IS or LCK_M_IU against an OBJECT resource, not a key or page.
• The head blocker holds an X (or S) lock on the whole table.
• The problem goes away the moment the job finishes, and comes back the next night.

That pattern is lock escalation. SQL Server started with row or page locks, crossed a threshold, and converted them into a single table (or partition) lock to save lock memory.

#How escalation is triggered

Escalation is per statement and per object reference. Locks taken by earlier statements in the same transaction do not count towards the threshold, but the escalated lock is held until the transaction commits.

Note

Azure SQL Database and SQL Server 2025 can use optimized locking (requires accelerated database recovery). It releases row locks earlier and greatly reduces escalation. Check SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn'); before tuning; if it returns 1, escalation is far less likely to be your problem.

#Check 1: confirm a table-level lock is the blocker

Run this while the blocking is happening:

SELECT  tl.request_session_id      AS session_id,
        OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) AS object_name,
        tl.resource_type,
        tl.request_mode,
        tl.request_status,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000          AS wait_s
FROM    sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_exec_requests AS r
       ON r.session_id = tl.request_session_id
WHERE   tl.resource_type = 'OBJECT'
  AND   tl.resource_database_id = DB_ID()
ORDER BY object_name, tl.request_status;

Look for one row with request_mode = X and request_status = GRANT on the table, and many WAIT rows with IX or IS on the same object. That granted X is the escalated lock.

Get the statement the holder is running:

SELECT  s.session_id, s.login_name, s.program_name,
        r.command, r.status, r.open_transaction_count,
        t.text AS current_sql
FROM    sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   s.session_id = 87;   -- the X lock holder

If r is NULL the session is idle with an open transaction: the statement finished, but nobody committed. That is an application or job bug, not a tuning problem.

#Check 2: which tables escalate, historically

sys.dm_db_index_operational_stats counts escalation attempts and successes since the index metadata was last loaded into memory (usually since restart):

SELECT TOP (20)
        OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) AS table_name,
        i.name                              AS index_name,
        ios.index_lock_promotion_attempt_count AS escalation_attempts,
        ios.index_lock_promotion_count         AS escalations,
        ios.row_lock_wait_count,
        ios.row_lock_wait_in_ms,
        ios.page_lock_wait_count,
        ios.page_lock_wait_in_ms
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
JOIN    sys.indexes AS i
        ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE   ios.index_lock_promotion_attempt_count > 0
ORDER BY ios.index_lock_promotion_count DESC;

A high escalations number on a busy OLTP table is your target list.

#Check 3: capture escalations with Extended Events

Leave this running for a day or over the job window. It records every escalation with the statement text:

CREATE EVENT SESSION [lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation (
    ACTION (sqlserver.sql_text, sqlserver.session_id, sqlserver.client_app_name,
            sqlserver.database_name, sqlserver.username)
    WHERE  (sqlserver.database_name = N'SalesDB'))
ADD TARGET package0.event_file (SET filename = N'lock_escalation', max_file_size = 50, max_rollover_files = 4)
WITH (MAX_DISPATCH_LATENCY = 10 SECONDS, STARTUP_STATE = OFF);

ALTER EVENT SESSION [lock_escalation] ON SERVER STATE = START;

Read it back:

SELECT  x.value('(event/@timestamp)[1]', 'datetime2')                         AS utc_time,
        x.value('(event/data[@name="escalation_cause"]/text)[1]', 'nvarchar(60)') AS cause,
        x.value('(event/data[@name="escalated_lock_count"]/value)[1]', 'int')    AS locks_escalated,
        OBJECT_NAME(x.value('(event/data[@name="object_id"]/value)[1]', 'int'))  AS object_name,
        x.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(200)') AS app,
        x.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)')     AS sql_text
FROM (
    SELECT CAST(event_data AS xml) AS x
    FROM   sys.fn_xe_file_target_read_file(N'lock_escalation*.xel', NULL, NULL, NULL)
) AS e
ORDER BY utc_time DESC;

Run OBJECT_NAME in the right database context. Field names on this event vary slightly between builds; if a column comes back NULL, open one event in SSMS (Watch Live Data) and check the names. escalation_cause tells you whether the lock count or memory pressure triggered it.

#Fix it now

1. If the holder is an idle session with an open transaction, contact the owner and kill it. The rollback releases the table lock.
2. If it is a running purge job, decide whether to let it finish or stop it. A rollback of a large DML statement can take as long as the work already done, and the table lock stays held for the whole rollback.

KILL 87;
KILL 87 WITH STATUSONLY;   -- shows estimated rollback completion

Caution

Killing a session that has been deleting for 40 minutes can mean 40 more minutes of rollback with the table still locked. Check KILL ... WITH STATUSONLY and the transaction log usage before you decide.

#Fix it properly

#Batch large deletes

DECLARE @rows int = 1;
WHILE @rows > 0
BEGIN
    DELETE TOP (4000) FROM dbo.OrderAudit
    WHERE  CreatedAt < DATEADD(DAY, -90, SYSUTCDATETIME());
    SET @rows = @@ROWCOUNT;
    -- optional pause to let other work through
    WAITFOR DELAY '00:00:00.200';
END;

Each DELETE runs in its own autocommit transaction. Make sure CreatedAt is indexed, or each batch will scan the table and the job will get slower over time. Non-clustered indexes also take locks, so on wide tables with many indexes drop the batch size further.

#Change escalation behaviour per table

-- See the current setting
SELECT name, lock_escalation_desc FROM sys.tables WHERE name = 'OrderAudit';

-- Partitioned table: escalate to the partition (HoBT) instead of the table
ALTER TABLE dbo.OrderAudit SET (LOCK_ESCALATION = AUTO);

-- Last resort: never escalate this table
ALTER TABLE dbo.OrderAudit SET (LOCK_ESCALATION = DISABLE);

Warning

DISABLE and the instance-wide trace flags 1211 and 1224 trade blocking for lock memory. A single statement can then hold millions of row locks, and under memory pressure SQL Server can still escalate or fail with error 1204 (out of locks). Prefer batching and indexing; use these only after testing with production-sized data.

AUTO on a partitioned table can create deadlocks between sessions that hold partition locks in different orders. Test it against the real workload before rolling it out.

#Prevent it next time

• Keep the lock_escalation Extended Events session (or the DMV query above) in your weekly review and look for new tables appearing.
• Require batching in code review for any job that modifies more than a few thousand rows.
• Index the columns used by purge and archive predicates.
• Alert on LCK_M_* waits against OBJECT resources lasting more than a minute; that is almost always escalation or an explicit TABLOCK hint.
• On Azure SQL Database or SQL Server 2025, evaluate optimized locking, which removes most escalation for normal DML.