The reader that locks the table

/*
  Diagnostic (a): lock escalation counters per index in the current database.
  - index_lock_promotion_attempt_count rises at checkpoints (in the lab also without a subsequent
    escalation), index_lock_promotion_count counts the escalations that took place.
  - The values accumulate since the metadata was loaded (restart, database offline, eviction from the
    metadata cache). Only differences between two measurements are meaningful.
  Tested in the lock escalation prefetch lab (SQL Server 2019/2022/2025).
*/
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id)        AS table_name,
       s.index_id,
       i.name                          AS index_name,
       s.partition_number,
       s.index_lock_promotion_attempt_count,
       s.index_lock_promotion_count,
       s.row_lock_count,
       s.row_lock_wait_count,
       s.row_lock_wait_in_ms,
       s.page_lock_count,
       s.page_lock_wait_count,
       s.page_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS s
JOIN sys.indexes AS i
  ON i.object_id = s.object_id
 AND i.index_id  = s.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND s.index_lock_promotion_attempt_count > 0
ORDER BY s.index_lock_promotion_count DESC, s.index_lock_promotion_attempt_count DESC;

/*
  Diagnostic (b): plan cache search for nested loops with prefetch.
  - Returns, per cached statement, every nested loops operator with WithUnorderedPrefetch or
    WithOrderedPrefetch and shows whether its inner side contains a key/RID lookup
    (inner_lookup_table/inner_lookup_index). Prefetch ahead of a lookup is the pattern in which a SELECT
    under READ COMMITTED holds the S locks of the outer rows until the end of the statement.
  - outer_estimate_rows shows the row count the plan was compiled for.
  - The XQuery over the whole plan cache is expensive. On large production systems, narrow it down first,
    e.g. to one database (qp.dbid) or to plans with a high execution count.
  Tested in the lock escalation prefetch lab (SQL Server 2019/2022/2025).
*/
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DB_NAME(qp.dbid)                                                    AS database_name,
       OBJECT_SCHEMA_NAME(qp.objectid, qp.dbid) + N'.'
         + OBJECT_NAME(qp.objectid, qp.dbid)                               AS object_name,
       cp.objtype,
       cp.usecounts,
       LEFT(stmt.n.value('@StatementText', 'nvarchar(max)'), 300)         AS statement_text,
       nl.n.value('@NodeId', 'int')                                        AS nested_loops_node_id,
       CASE WHEN nl.n.exist('NestedLoops[@WithUnorderedPrefetch = ("1", "true")]') = 1
            THEN N'unordered' ELSE N'ordered' END                          AS prefetch,
       nl.n.value('(NestedLoops/RelOp/@EstimateRows)[1]', 'float')        AS outer_estimate_rows,
       lk.n.value('(IndexScan/Object/@Table)[1]', 'sysname')               AS inner_lookup_table,
       lk.n.value('(IndexScan/Object/@Index)[1]', 'sysname')               AS inner_lookup_index,
       cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS stmt(n)
CROSS APPLY stmt.n.nodes('.//RelOp[NestedLoops[@WithUnorderedPrefetch = ("1", "true")
                                              or @WithOrderedPrefetch   = ("1", "true")]]') AS nl(n)
OUTER APPLY nl.n.nodes('(NestedLoops/RelOp[2]/descendant-or-self::RelOp[IndexScan/@Lookup = ("1", "true")])[1]') AS lk(n)
WHERE cp.cacheobjtype = N'Compiled Plan'
  AND qp.query_plan.exist('//NestedLoops[@WithUnorderedPrefetch = ("1", "true")
                                         or @WithOrderedPrefetch   = ("1", "true")]') = 1
ORDER BY cp.usecounts DESC, database_name, object_name, nested_loops_node_id;

/*
  Diagnostic (c), part 1: Extended Events session for lock escalations.
  ! CAUTION: Do not start this on a production system without thinking it through.
  ! Try it on a test system first; on production only after agreeing it with operations.
  ! Restrict it to one database with a predicate, limit the runtime, watch the load.
  ! Stop and drop it afterwards (clean-up block at the end). Do not add events such as lock_acquired.
  - Event sqlserver.lock_escalation with statement and database name, plus session, SQL text,
    host name, application and plan handle. From SQL Server 2022 on, the event also contains the field
    lock_escalation_with_lock_class (not present in 2019 in the lab).
  - ring_buffer target: in memory only, lost when the session stops. For continuous use, use an
    event_file target instead.
  - Optionally restrict to one database with a predicate, e.g.
      ADD EVENT sqlserver.lock_escalation (... WHERE (sqlserver.database_name = N'MyDatabase'))
  Tested in the lock escalation prefetch lab (SQL Server 2019/2022/2025).
*/
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'psg_lock_escalation')
    DROP EVENT SESSION psg_lock_escalation ON SERVER;

CREATE EVENT SESSION psg_lock_escalation ON SERVER
ADD EVENT sqlserver.lock_escalation (
    SET collect_database_name = (1), collect_statement = (1)
    ACTION (sqlserver.session_id, sqlserver.sql_text, sqlserver.client_hostname,
            sqlserver.client_app_name, sqlserver.plan_handle)
)
ADD TARGET package0.ring_buffer (SET max_events_limit = (1000))
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = OFF);

ALTER EVENT SESSION psg_lock_escalation ON SERVER STATE = START;

/* Clean up when the session is no longer needed:
ALTER EVENT SESSION psg_lock_escalation ON SERVER STATE = STOP;
DROP EVENT SESSION psg_lock_escalation ON SERVER;
*/

/*
  Diagnostic (c), part 2: read lock escalation events from the ring_buffer of the session
  psg_lock_escalation (see c-xe-lock-escalation-session.sql).
  - mode S or X on resource_type OBJECT is the escalated table lock.
  - escalated_lock_count: number of finer-grained locks replaced; hobt_lock_count: locks on the
    escalating HoBt at the time of the escalation.
  - Events appear only after the session's dispatch latency (here up to 5 seconds).
  - Timestamps are UTC.
  Tested in the lock escalation prefetch lab (SQL Server 2019/2022/2025).
*/
WITH rb AS (
    SELECT CAST(t.target_data AS xml) AS x
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
      ON t.event_session_address = s.address
    WHERE s.name = N'psg_lock_escalation'
      AND t.target_name = N'ring_buffer'
)
SELECT e.n.value('@timestamp', 'datetime2(3)')                                      AS utc_time,
       e.n.value('(data[@name = "database_name"]/value)[1]', 'sysname')             AS database_name,
       OBJECT_NAME(e.n.value('(data[@name = "object_id"]/value)[1]', 'int'),
                   e.n.value('(data[@name = "database_id"]/value)[1]', 'int'))      AS object_name,
       e.n.value('(data[@name = "hobt_id"]/value)[1]', 'bigint')                     AS hobt_id,
       e.n.value('(data[@name = "resource_type"]/text)[1]', 'nvarchar(60)')          AS resource_type,
       e.n.value('(data[@name = "mode"]/text)[1]', 'nvarchar(60)')                   AS mode,
       e.n.value('(data[@name = "escalation_cause"]/text)[1]', 'nvarchar(60)')       AS escalation_cause,
       e.n.value('(data[@name = "escalated_lock_count"]/value)[1]', 'int')           AS escalated_lock_count,
       e.n.value('(data[@name = "hobt_lock_count"]/value)[1]', 'int')                AS hobt_lock_count,
       e.n.value('(data[@name = "lock_escalation_with_lock_class"]/value)[1]',
                 'nvarchar(10)')                                                     AS with_lock_class, -- from 2022 on
       e.n.value('(action[@name = "session_id"]/value)[1]', 'int')                   AS session_id,
       e.n.value('(action[@name = "client_hostname"]/value)[1]', 'nvarchar(128)')    AS client_hostname,
       e.n.value('(data[@name = "statement"]/value)[1]', 'nvarchar(max)')            AS statement,
       e.n.value('(action[@name = "sql_text"]/value)[1]', 'nvarchar(max)')           AS sql_text
FROM rb
CROSS APPLY rb.x.nodes('/RingBufferTarget/event[@name = "lock_escalation"]') AS e(n)
ORDER BY utc_time;

/*
  Diagnostic (d): currently held and waiting locks per session, object, index, resource type and
  mode in the current database, aggregated; plus wait type and blocking session of the request.
  - Many KEY locks with mode S for a reading session under READ COMMITTED are unusual:
    a hint at locks held until the end of the statement, e.g. nested loops with prefetch ahead of a lookup.
  - OBJECT with mode S (instead of IS) means a table lock, for a reader typically after a lock escalation.
  Tested in the lock escalation prefetch lab (SQL Server 2019/2022/2025).
*/
SELECT l.request_session_id                                          AS session_id,
       s.host_name,
       r.wait_type,
       r.blocking_session_id,
       l.resource_type,
       COALESCE(OBJECT_NAME(p.object_id),
                CASE WHEN l.resource_type = N'OBJECT'
                     THEN OBJECT_NAME(l.resource_associated_entity_id) END) AS object_name,
       p.index_id,
       i.name                                                        AS index_name,
       l.request_mode,
       l.request_status,
       COUNT(*)                                                      AS lock_count
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p
       ON p.hobt_id = l.resource_associated_entity_id
      AND l.resource_type IN (N'KEY', N'PAGE', N'RID', N'HOBT')
LEFT JOIN sys.indexes AS i
       ON i.object_id = p.object_id
      AND i.index_id  = p.index_id
LEFT JOIN sys.dm_exec_sessions AS s
       ON s.session_id = l.request_session_id
LEFT JOIN sys.dm_exec_requests AS r
       ON r.session_id = l.request_session_id
WHERE l.resource_database_id = DB_ID()
  AND l.request_session_id <> @@SPID
GROUP BY l.request_session_id, s.host_name, r.wait_type, r.blocking_session_id, l.resource_type,
         p.object_id, l.resource_associated_entity_id, p.index_id, i.name,
         l.request_mode, l.request_status
ORDER BY session_id, object_name, index_id, resource_type, request_mode;