Der Leser, der die Tabelle sperrt

/*
  Diagnose (a): Lock-Escalation-Zähler je Index der aktuellen Datenbank.
  - index_lock_promotion_attempt_count steigt an Prüfpunkten (im Lab auch ohne folgende
    Escalation), index_lock_promotion_count zählt die erfolgten Escalations.
  - Die Werte sind kumuliert seit dem Laden der Metadaten (Neustart, Datenbank offline,
    Verdrängung aus dem Metadaten-Cache). Aussagekräftig sind daher Differenzen zwischen zwei Messungen.
  Getestet im Lock-Escalation-Prefetch-Lab (SQL Server 2019/2022/2025), siehe README.
*/
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;

/*
  Diagnose (b): Plan-Cache-Suche nach Nested Loops mit Prefetch.
  - Liefert je gecachtem Statement jeden Nested-Loops-Operator mit WithUnorderedPrefetch oder
    WithOrderedPrefetch und zeigt, ob seine innere Seite einen Key/RID Lookup enthält
    (inner_lookup_table/inner_lookup_index). Prefetch vor einem Lookup ist das Muster, bei dem ein
    SELECT unter READ COMMITTED die S-Sperren der äußeren Zeilen bis Statement-Ende hält.
  - outer_estimate_rows zeigt, für welche Menge der Plan kompiliert wurde.
  - Die XQuery über den ganzen Plan-Cache ist teuer. Auf großen Produktivsystemen zuerst eingrenzen,
    z. B. auf eine Datenbank (qp.dbid) oder auf Pläne mit hoher Ausführungszahl.
  Getestet im Lock-Escalation-Prefetch-Lab (SQL Server 2019/2022/2025), siehe README.
*/
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;

/*
  Diagnose (c), Teil 1: Extended-Events-Session für Lock Escalations.
  ! ACHTUNG: Nicht unüberlegt auf einem Produktivsystem starten.
  ! Erst auf einem Testsystem ausprobieren, auf Produktion nur nach Abstimmung mit dem Betrieb.
  ! Per Prädikat auf die betroffene Datenbank eingrenzen, Laufzeit begrenzen, Last beobachten.
  ! Danach stoppen und löschen (Aufräumblock am Ende). Keine Ereignisse wie lock_acquired ergänzen.
  - Ereignis sqlserver.lock_escalation mit Statement und Datenbankname, dazu Session, SQL-Text,
    Hostname, Anwendung und Plan-Handle. Ab SQL Server 2022 enthält das Ereignis zusätzlich das Feld
    lock_escalation_with_lock_class (im Lab für 2019 nicht vorhanden).
  - Ziel ring_buffer: nur im Speicher, geht beim Stoppen der Session verloren. Für Dauerbetrieb
    stattdessen ein event_file-Ziel verwenden.
  - Optional per Prädikat auf eine Datenbank eingrenzen, z. B.
      ADD EVENT sqlserver.lock_escalation (... WHERE (sqlserver.database_name = N'MeineDatenbank'))
  Getestet im Lock-Escalation-Prefetch-Lab (SQL Server 2019/2022/2025), siehe README.
*/
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;

/* Aufräumen, wenn die Session nicht mehr gebraucht wird:
ALTER EVENT SESSION psg_lock_escalation ON SERVER STATE = STOP;
DROP EVENT SESSION psg_lock_escalation ON SERVER;
*/

/*
  Diagnose (c), Teil 2: Lock-Escalation-Ereignisse aus dem ring_buffer der Session
  psg_lock_escalation lesen (siehe c-xe-lock-escalation-session.sql).
  - mode S oder X auf resource_type OBJECT ist die eskalierte Tabellensperre.
  - escalated_lock_count: Zahl der dabei ersetzten feineren Sperren; hobt_lock_count: Sperren auf dem
    eskalierenden HoBt zum Zeitpunkt der Escalation.
  - Ereignisse erscheinen erst nach der Dispatch-Latenz der Session (hier bis 5 Sekunden).
  - Zeitstempel sind UTC.
  Getestet im Lock-Escalation-Prefetch-Lab (SQL Server 2019/2022/2025), siehe README.
*/
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, -- ab 2022
       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;

/*
  Diagnose (d): aktuell gehaltene und wartende Sperren je Session, Objekt, Index, Ressourcentyp und
  Modus in der aktuellen Datenbank, verdichtet; dazu Wartetyp und blockierende Session der Anfrage.
  - Viele KEY-Sperren mit Modus S bei einer lesenden Session unter READ COMMITTED sind ungewöhnlich:
    Hinweis auf Sperren bis Statement-Ende, z. B. Nested Loops mit Prefetch vor einem Lookup.
  - OBJECT mit Modus S (statt IS) bedeutet eine Tabellensperre, bei einem Leser typischerweise nach
    einer Lock Escalation.
  Getestet im Lock-Escalation-Prefetch-Lab (SQL Server 2019/2022/2025), siehe README.
*/
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;