Understanding SQL Server Locking: From Lock Escalation to Optimized Locking

/* PSG Stack · SQL Server Locking · Query 1   Overview: which switches are on, who holds which locks, who is waiting?    !!! WARNING — please read before running !!!   - Read-only. The query changes no data and no setting and does not kill any session.     It only creates two small table variables (in tempdb, for its own session).   - Use with care in production: part 2 reads sys.dm_tran_locks once in full. With very many     locks (hundreds of thousands, e.g. during large modifications without lock escalation)     this costs CPU and memory itself. Do not run it in a loop or every second.   - Snapshot: locks and waits change constantly. An empty result only means "nothing at this     moment", not "never".   - Permissions: VIEW SERVER STATE (SQL Server 2022 and later: VIEW SERVER PERFORMANCE STATE     is enough). VIEW DATABASE STATE alone is not enough. Without the server permission only     part 1 returns rows: part 2 fails with error 300, part 3 as well (2017–2022) or stays     empty without a message (2025).     Object names appear only for databases the login can access and whose metadata it can     see (e.g. a user in the database plus VIEW DEFINITION, or VIEW ANY DEFINITION);     otherwise object_id or hobt_id is shown.   - MAXDOP 1: every statement runs serially and uses no additional cores. This keeps the     diagnosis small even while the server is under load.   - SQL Server 2017 and later. Tested in the PSG lab on 2017, 2019, 2022 and 2025 (2026-11-08).     Columns a version does not know (ADR before 2019, optimized locking before 2025) show     n/a; the query checks whether the column exists.    Result sets:     1  Switches per database (RCSI, snapshot isolation, ADR, optimized locking, ...)     2a Sessions holding locks or an open transaction     2b Held and requested locks, aggregated     3  Waiting requests with their blocker   This query does not search for the head blocker of a blocking chain; the advisor page   "Blocking and timeouts" has its own query for that. Note for 2025: when several sessions   wait for the same transaction (XACT), blocked_by can point to another waiting session   instead of the holder.*/SET NOCOUNT ON; /* 1 · Switches per database   laq_possible: lock after qualification works only with optimized locking AND RCSI.   Optimized locking requires ADR; without RCSI only TID locking remains. */DECLARE @adr nvarchar(300) =    CASE WHEN COL_LENGTH(N'sys.databases', N'is_accelerated_database_recovery_on') IS NOT NULL         THEN N'CASE d.is_accelerated_database_recovery_on WHEN 1 THEN N''on'' ELSE N''off'' END'         ELSE N'N''n/a''' END;DECLARE @ol nvarchar(300) =    CASE WHEN COL_LENGTH(N'sys.databases', N'is_optimized_locking_on') IS NOT NULL         THEN N'CASE d.is_optimized_locking_on WHEN 1 THEN N''on'' ELSE N''off'' END'         ELSE N'N''n/a''' END;DECLARE @sql1 nvarchar(max) = N'SELECT d.name                                                            AS database_name,       CASE d.is_read_committed_snapshot_on WHEN 1 THEN N''on'' ELSE N''off'' END AS rcsi,       d.snapshot_isolation_state_desc                                   AS snapshot_isolation,       ' + @adr + N'                                                     AS adr,       ' + @ol + N'                                                      AS optimized_locking,       CASE WHEN ' + @ol + N' = N''n/a'' THEN N''n/a''            WHEN ' + @ol + N' = N''on'' AND d.is_read_committed_snapshot_on = 1 THEN N''yes''            ELSE N''no'' END                                             AS laq_possible,       d.recovery_model_desc                                             AS recovery_model,       d.compatibility_level                                             AS compatibility_level,       d.log_reuse_wait_desc                                             AS log_reuse_waitFROM sys.databases AS dWHERE d.state = 0          -- ONLINE only  AND d.database_id <> 3   -- modelORDER BY CASE WHEN d.database_id <= 4 THEN 1 ELSE 0 END, d.nameOPTION (MAXDOP 1);';EXEC sys.sp_executesql @sql1; /* 2 · Locks: read once, then aggregate   - The own session and the shared DATABASE S lock of every connection are left out.   - resources = distinct locked resources; entries = rows in sys.dm_tran_locks.     On servers with 16 or more logical CPUs, SQL Server splits table locks (lock     partitioning): a table-level S or X lock, e.g. after lock escalation, then appears once     per partition. It counts as one resource here; partitions gives the number of parts.   - XACT (SQL Server 2025 with optimized locking): lock on a transaction. The writer holds X     on its own transaction (object_name = "(transaction)"). Waiting requests ask for S     (status WAIT); for them object_name shows the table of the row they are waiting for. */DECLARE @locks TABLE (    database_id    int           NOT NULL,    session_id     int           NOT NULL,    resource_type  nvarchar(60)  NOT NULL,    entity_id      bigint        NULL,    request_mode   nvarchar(60)  NOT NULL,    request_status nvarchar(60)  NOT NULL,    resources      int           NOT NULL,    entries        int           NOT NULL,    partitions     int           NOT NULL); INSERT INTO @locksSELECT l.resource_database_id,       l.request_session_id,       l.resource_type,       CASE WHEN l.resource_type IN (N'OBJECT', N'KEY', N'PAGE', N'RID', N'HOBT', N'XACT')            THEN NULLIF(l.resource_associated_entity_id, 0) END,       l.request_mode,       l.request_status,       COUNT(DISTINCT CONCAT(l.resource_associated_entity_id, N'|', l.resource_description,                             N'|', l.resource_subtype)),       COUNT(*),       COUNT(DISTINCT COALESCE(l.resource_lock_partition, 0))FROM sys.dm_tran_locks AS lWHERE l.request_session_id <> @@SPID  AND l.request_session_id > 0  AND NOT (l.resource_type = N'DATABASE' AND l.request_mode = N'S')GROUP BY l.resource_database_id, l.request_session_id, l.resource_type,         CASE WHEN l.resource_type IN (N'OBJECT', N'KEY', N'PAGE', N'RID', N'HOBT', N'XACT')              THEN NULLIF(l.resource_associated_entity_id, 0) END,         l.request_mode, l.request_statusOPTION (MAXDOP 1); /* KEY, PAGE, RID and HOBT locks carry a hobt_id. Only the database itself knows the table   name, so the query takes a short look at sys.partitions per database. Databases without   access are skipped and keep the ID. */DECLARE @names TABLE (database_id int NOT NULL, hobt_id bigint NOT NULL,                      object_name nvarchar(300) NULL, index_name sysname NULL);DECLARE @db int, @list nvarchar(max), @sql2 nvarchar(max);DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR    SELECT database_id,           STRING_AGG(CAST(entity_id AS nvarchar(max)), N',')    FROM (SELECT DISTINCT database_id, entity_id FROM @locks          WHERE resource_type IN (N'KEY', N'PAGE', N'RID', N'HOBT', N'XACT') AND entity_id IS NOT NULL) AS x    WHERE HAS_DBACCESS(DB_NAME(database_id)) = 1    GROUP BY database_id;OPEN dbs;FETCH NEXT FROM dbs INTO @db, @list;WHILE @@FETCH_STATUS = 0BEGIN    SET @sql2 = N'SELECT ' + CAST(@db AS nvarchar(10)) + N', p.hobt_id,       QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id, ' + CAST(@db AS nvarchar(10)) + N')) + N''.''         + QUOTENAME(OBJECT_NAME(p.object_id, ' + CAST(@db AS nvarchar(10)) + N')), i.nameFROM ' + QUOTENAME(DB_NAME(@db)) + N'.sys.partitions AS pLEFT JOIN ' + QUOTENAME(DB_NAME(@db)) + N'.sys.indexes AS i       ON i.object_id = p.object_id AND i.index_id = p.index_idWHERE p.hobt_id IN (' + @list + N')OPTION (MAXDOP 1);';    BEGIN TRY        INSERT INTO @names EXEC sys.sp_executesql @sql2;    END TRY    BEGIN CATCH        -- no access to this database: IDs stay    END CATCH;    FETCH NEXT FROM dbs INTO @db, @list;END;CLOSE dbs;DEALLOCATE dbs; /* 2a · Sessions holding locks or an open transaction   isolation_level: session setting. READ COMMITTED still reads row versions without waiting   in a database with RCSI.   last_batch: most recent batch of the connection (truncated); for sleeping sessions often the   command that opened the transaction. No host name, so the output can be shared. */SELECT es.session_id,       es.login_name                                                    AS login,       es.program_name                                                  AS program,       es.status,       es.open_transaction_count                                        AS open_transactions,       CASE es.transaction_isolation_level            WHEN 1 THEN N'READ UNCOMMITTED' WHEN 2 THEN N'READ COMMITTED'            WHEN 3 THEN N'REPEATABLE READ'  WHEN 4 THEN N'SERIALIZABLE'            WHEN 5 THEN N'SNAPSHOT'         ELSE N'unknown' END          AS isolation_level,       tx.begin_time                                                    AS transaction_since,       DATEDIFF(SECOND, tx.begin_time, SYSDATETIME())                   AS transaction_s,       COALESCE(s.resources, 0)                                         AS locks,       COALESCE(s.waiting, 0)                                           AS of_which_waiting,       COALESCE(s.xact, 0)                                              AS xact_locks,       r.wait_type                                                      AS waiting_for,       NULLIF(r.blocking_session_id, 0)                                 AS blocked_by,       LEFT(REPLACE(REPLACE(t.text, CHAR(13), N' '), CHAR(10), N' '), 200) AS last_batchFROM sys.dm_exec_sessions AS esLEFT JOIN (SELECT session_id,                  SUM(resources)                                                   AS resources,                  SUM(CASE WHEN request_status <> N'GRANT' THEN resources ELSE 0 END) AS waiting,                  SUM(CASE WHEN resource_type = N'XACT' THEN resources ELSE 0 END)    AS xact           FROM @locks GROUP BY session_id) AS s       ON s.session_id = es.session_idLEFT JOIN sys.dm_exec_requests AS r       ON r.session_id = es.session_idLEFT JOIN sys.dm_exec_connections AS c       ON c.session_id = es.session_idOUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS tOUTER APPLY (SELECT MIN(at.transaction_begin_time) AS begin_time             FROM sys.dm_tran_session_transactions AS st             JOIN sys.dm_tran_active_transactions AS at               ON at.transaction_id = st.transaction_id             WHERE st.session_id = es.session_id) AS txWHERE es.session_id <> @@SPID  AND es.is_user_process = 1  AND (s.session_id IS NOT NULL OR es.open_transaction_count > 0)ORDER BY locks DESC, es.session_idOPTION (MAXDOP 1); /* 2b · Locks per session, database, object, resource, mode and status   Resource types: OBJECT = table, PAGE = page, KEY = index row, RID = heap row,   XACT = transaction (optimized locking). Status GRANT = held, WAIT/CONVERT = requested. */SELECT TOP (500)       s.session_id,       DB_NAME(s.database_id)                                           AS database_name,       CASE WHEN s.resource_type = N'OBJECT'                 THEN COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(s.entity_id, s.database_id)) + N'.'                               + QUOTENAME(OBJECT_NAME(s.entity_id, s.database_id)),                               N'object_id ' + CAST(s.entity_id AS nvarchar(20)))            WHEN s.resource_type IN (N'KEY', N'PAGE', N'RID', N'HOBT')                 THEN COALESCE(n.object_name, N'hobt_id ' + CAST(s.entity_id AS nvarchar(20)))            WHEN s.resource_type = N'XACT' THEN COALESCE(n.object_name, N'(transaction)')            ELSE N'' END                                                AS object_name,       n.index_name                                                     AS index_name,       s.resource_type                                                  AS resource,       s.request_mode                                                   AS mode,       s.request_status                                                 AS status,       s.resources,       s.entries,       s.partitionsFROM @locks AS sLEFT JOIN @names AS n       ON n.database_id = s.database_id      AND n.hobt_id = s.entity_id      AND s.resource_type IN (N'KEY', N'PAGE', N'RID', N'HOBT', N'XACT')ORDER BY s.session_id, database_name, object_name, index_name,         CASE s.resource_type WHEN N'OBJECT' THEN 1 WHEN N'HOBT' THEN 2 WHEN N'PAGE' THEN 3                              WHEN N'KEY' THEN 4 WHEN N'RID' THEN 4 WHEN N'XACT' THEN 5 ELSE 6 END,         s.request_modeOPTION (MAXDOP 1); /* 3 · Waiting requests with their blocker   LCK_M_S_XACT_MODIFY / LCK_M_S_XACT_READ / LCK_M_S_XACT (2025): the request waits for another   transaction to end, not for a row lock. wait_resource then shows the XACT resource followed   by the affected row. Monitoring that only filters on LCK_M_S, LCK_M_U and LCK_M_X misses   these waits. */SELECT r.session_id,       NULLIF(r.blocking_session_id, 0)                                 AS blocked_by,       r.wait_type,       CASE WHEN r.wait_type LIKE N'LCK[_]M[_]%XACT%' THEN N'waits for transaction end (XACT)'            WHEN r.wait_type LIKE N'LCK[_]M[_]%'      THEN N'waits for lock'            ELSE N'other wait' END                                      AS kind,       r.wait_time                                                      AS wait_ms,       r.wait_resource,       DB_NAME(r.database_id)                                           AS database_name,       CASE r.transaction_isolation_level            WHEN 1 THEN N'READ UNCOMMITTED' WHEN 2 THEN N'READ COMMITTED'            WHEN 3 THEN N'REPEATABLE READ'  WHEN 4 THEN N'SERIALIZABLE'            WHEN 5 THEN N'SNAPSHOT'         ELSE N'unknown' END          AS isolation_level,       LEFT(REPLACE(REPLACE(SUBSTRING(t.text, r.statement_start_offset / 2 + 1,            (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)                  ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1),            CHAR(13), N' '), CHAR(10), N' '), 200)                      AS statement_textFROM sys.dm_exec_requests AS rOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS tWHERE r.session_id <> @@SPID  AND (r.blocking_session_id <> 0 OR r.wait_type LIKE N'LCK[_]M[_]%')ORDER BY r.wait_time DESCOPTION (MAXDOP 1);

1 · Switches per database

database_name rcsi snapshot_isolation adr optimized_locking laq_possible recovery_model compatibility_level log_reuse_wait
------------- ---- ------------------ --- ----------------- ------------ -------------- ------------------- --------------
Sales         off  OFF                off off               no           SIMPLE         170                 NOTHING
Stock         on   ON                 on  off               no           FULL           170                 NOTHING
Till          on   OFF                on  on                yes          SIMPLE         170                 NOTHING
master        off  ON                 off off               no           SIMPLE         170                 CHECKPOINT
msdb          off  ON                 off off               no           SIMPLE         170                 NOTHING
tempdb        off  OFF                off off               no           SIMPLE         170                 NOTHING

(On SQL Server 2017, adr, optimized_locking and laq_possible show n/a; on 2019 and 2022,
 optimized_locking and laq_possible.)


2a · Sessions holding locks or an open transaction (shortened)

session_id login       program     status  open_transactions isolation_level transaction_s locks of_which_waiting xact_locks waiting_for         blocked_by last_batch
---------- ----------- ----------- ------- ----------------- --------------- ------------- ----- ---------------- ---------- ------------------- ---------- ------------------------------
57         app_booking BookingSvc  running 1                 READ COMMITTED  7             3086  0                0          WAITFOR             NULL       BEGIN TRAN; UPDATE dbo.Konto ...
56         app_booking BookingSvc  running 1                 READ COMMITTED  7             531   0                0          WAITFOR             NULL       BEGIN TRAN; UPDATE dbo.Konto ...
58         app_booking BookingSvc  running 2                 READ COMMITTED  4             3     1                0          LCK_M_X             56         (@1 int,@2 tinyint)UPDATE [dbo].[Konto] ...
62         app_report  Reports     running 0                 READ COMMITTED  NULL          3     1                0          LCK_M_S             57         SELECT saldo FROM dbo.Konto WHERE id = 10
55         app_till    TillSvc     running 1                 READ COMMITTED  7             2     0                1          WAITFOR             NULL       BEGIN TRAN; UPDATE dbo.Konto ...
60         app_till    TillSvc     running 2                 READ COMMITTED  4             2     1                1          LCK_M_S_XACT_MODIFY 55         (@1 int,@2 tinyint)UPDATE [dbo].[Konto] ...
63         app_report  Reports     running 0                 READ COMMITTED  NULL          2     1                1          LCK_M_S_XACT_READ   60         SELECT ... WITH (READCOMMITTEDLOCK) ...
61         app_report  Reports     running 0                 READ COMMITTED  NULL          1     1                0          LCK_M_IS            57         SELECT COUNT(*) FROM dbo.Klein

(In the lab, sessions 55/56/57 showed status = running because sqlcmd kept the transaction open
 with WAITFOR; an application that sends nothing after its UPDATE appears as sleeping.)


2b · Locks per session, database, object, resource, mode and status

session_id database_name object_name     index_name resource mode status resources entries partitions
---------- ------------- --------------- ---------- -------- ---- ------ --------- ------- ----------
55         Till          (transaction)   NULL       XACT     X    GRANT  1         1       1
55         Till          [dbo].[Konto]   NULL       OBJECT   IX   GRANT  1         1       1
56         Stock         [dbo].[Konto]   NULL       OBJECT   IX   GRANT  1         1       1
56         Stock         [dbo].[Konto]   PK_Konto   PAGE     IX   GRANT  30        30      1
56         Stock         [dbo].[Konto]   PK_Konto   KEY      X    GRANT  500       500     1
57         Sales         [dbo].[Klein]   NULL       OBJECT   X    GRANT  1         1       1
57         Sales         [dbo].[Konto]   NULL       OBJECT   IX   GRANT  1         1       1
57         Sales         [dbo].[Konto]   PK_Konto   PAGE     IX   GRANT  84        84      1
57         Sales         [dbo].[Konto]   PK_Konto   KEY      X    GRANT  3000      3000    1
58         Stock         [dbo].[Konto]   PK_Konto   KEY      X    WAIT   1         1       1
60         Till          [dbo].[Konto]   PK_Konto   XACT     S    WAIT   1         1       1
61         Sales         [dbo].[Klein]   NULL       OBJECT   IS   WAIT   1         1       1
62         Sales         [dbo].[Konto]   PK_Konto   KEY      S    WAIT   1         1       1
63         Till          [dbo].[Konto]   PK_Konto   XACT     S    WAIT   1         1       1
(shortened: intent locks IS/IX of the waiting sessions on table and page)

How to read it: without optimized locking, session 57 holds 3,000 row locks and 84 page locks
until commit. With optimized locking, session 55 holds only the lock on its transaction (XACT X)
and the intent lock on the table for the same number of changed rows.

On a server with 24 logical CPUs (lab: SQL Server 2017 and 2019) the table lock on dbo.Klein
looked like this (lock partitioning: one resource, 24 entries):
57         Sales         [dbo].[Klein]   NULL       OBJECT   X    GRANT  1         24      24


3 · Waiting requests with their blocker

session_id blocked_by wait_type           kind                              wait_ms wait_resource                                              database_name statement_text
---------- ---------- ------------------- --------------------------------- ------- ---------------------------------------------------------- ------------- ------------------------------------
58         56         LCK_M_X             waits for lock                    4436    KEY: 6:72057594047234048 (d08358b1108f)                    Stock         UPDATE [dbo].[Konto] set [saldo] = ...
61         57         LCK_M_IS            waits for lock                    4421    OBJECT: 5:1269579561:0                                     Sales         SELECT @x = COUNT(*) FROM dbo.Klein
62         57         LCK_M_S             waits for lock                    4171    KEY: 5:72057594047234048 (d08358b1108f)                    Sales         SELECT @x = saldo FROM dbo.Konto ...
60         55         LCK_M_S_XACT_MODIFY waits for transaction end (XACT)  4170    XACT: 7:1107:0 KEY: 7:72057594047234048 (d08358b1108f)     Till          UPDATE [dbo].[Konto] set [saldo] = ...
63         60         LCK_M_S_XACT_READ   waits for transaction end (XACT)  4166    XACT: 7:1107:0 KEY: 7:72057594047234048 (286fc18d83ea)     Till          SELECT @x = saldo FROM dbo.Konto WITH (READCOMMITTEDLOCK) ...

How to read it: session 63 waits for the same transaction (XACT: 7:1107:0) as session 60, but for
a different row. blocked_by nevertheless shows 60, a session that is waiting itself, not the
holder 55. This happened in all five 2025 runs (two each on CU9 and CU10, one pilot on CU10): blocked_by pointed to the session that
started waiting for the transaction first.