Sperren in SQL Server verstehen: von Lock Escalation bis Optimized Locking

/* PSG Stack · SQL Server Locking · Abfrage 1   Lagebild: Welche Schalter sind an, wer hält welche Sperren, wer wartet?    !!! WARNUNG — bitte vor dem Ausführen lesen !!!   - Nur lesend. Die Abfrage ändert keine Daten und keine Einstellung und beendet keine Sitzung.     Sie legt nur zwei kleine Tabellenvariablen an (in der TempDB der eigenen Sitzung).   - Auf Produktion mit Bedacht: Teil 2 liest sys.dm_tran_locks einmal vollständig. Bei sehr vielen     Sperren (Hunderttausende, z. B. während großer Änderungen ohne Lock Escalation) kostet das     selbst CPU und Speicher. Nicht in einer Schleife oder im Sekundentakt ausführen.   - Momentaufnahme: Sperren und Wartezustände ändern sich laufend. Ein leeres Ergebnis heißt     nur „in diesem Augenblick nichts“, nicht „nie“.   - Rechte: VIEW SERVER STATE (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE).     VIEW DATABASE STATE allein genügt nicht. Ohne das Serverrecht liefert nur Teil 1 Zeilen:     Teil 2 bricht mit Fehler 300 ab, Teil 3 ebenso (2017–2022) oder bleibt ohne Meldung leer (2025).     Objektnamen erscheinen nur für Datenbanken, auf die das Login zugreifen darf und deren     Metadaten es sehen darf (z. B. Benutzer in der Datenbank plus VIEW DEFINITION oder     VIEW ANY DEFINITION); sonst steht dort object_id bzw. hobt_id.   - MAXDOP 1: Jede Teilabfrage läuft seriell und belegt keine weiteren Kerne. So bleibt die     Diagnose klein, auch wenn der Server gerade unter Last steht.   - Ab SQL Server 2017. Geprüft im PSG-Lab auf 2017, 2019, 2022 und 2025 (08.11.2026).     Spalten, die eine Version nicht kennt (ADR vor 2019, Optimized Locking vor 2025),     erscheinen als n/a; die Abfrage prüft dazu, ob die Spalte existiert.    Ergebnisse:     1  Schalter je Datenbank (RCSI, Snapshot Isolation, ADR, Optimized Locking, ...)     2a Sitzungen mit Sperren oder offener Transaktion     2b Gehaltene und angeforderte Sperren, verdichtet     3  Wartende Anfragen mit Blocker   Den Headblocker einer Blocking-Kette sucht diese Abfrage nicht; dafür gibt es die eigene   Abfrage auf der Ratgeber-Seite „Blocking und Timeouts“. Hinweis zu 2025: Warten mehrere   Sitzungen auf dieselbe Transaktion (XACT), kann blockiert_von auf eine andere wartende   Sitzung zeigen statt auf den Halter.*/SET NOCOUNT ON; /* 1 · Schalter je Datenbank   laq_moeglich: Lock after Qualification wirkt nur mit Optimized Locking UND RCSI.   Optimized Locking setzt ADR voraus; ohne RCSI bleibt nur das TID-Locking. */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''an'' ELSE N''aus'' 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''an'' ELSE N''aus'' END'         ELSE N'N''n/a''' END;DECLARE @sql1 nvarchar(max) = N'SELECT d.name                                                            AS datenbank,       CASE d.is_read_committed_snapshot_on WHEN 1 THEN N''an'' ELSE N''aus'' 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''an'' AND d.is_read_committed_snapshot_on = 1 THEN N''ja''            ELSE N''nein'' END                                           AS laq_moeglich,       d.recovery_model_desc                                             AS wiederherstellungsmodell,       d.compatibility_level                                             AS kompatibilitaet,       d.log_reuse_wait_desc                                             AS log_wartet_aufFROM sys.databases AS dWHERE d.state = 0          -- nur ONLINE  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 · Sperren: einmal lesen, dann verdichten   - Eigene Sitzung und die gemeinsame DATABASE-S-Sperre jeder Verbindung bleiben draußen.   - ressourcen = verschiedene gesperrte Ressourcen; eintraege = Zeilen in sys.dm_tran_locks.     Auf Servern mit 16 oder mehr logischen CPUs teilt SQL Server Tabellensperren auf     (Lock Partitioning): Eine Tabellensperre S oder X, etwa nach einer Lock Escalation,     steht dann einmal je Partition in der Sicht. Sie zählt hier als eine Ressource;     partitionen nennt die Zahl der Teile.   - XACT (ab SQL Server 2025 mit Optimized Locking): Sperre auf eine Transaktion. Der Schreiber     hält X auf seine eigene Transaktion (objekt = „(Transaktion)“). Wartende fordern S an     (Status WAIT); bei ihnen nennt objekt die Tabelle der Zeile, auf die sie warten. */DECLARE @sperren 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,    ressourcen     int           NOT NULL,    eintraege      int           NOT NULL,    partitionen    int           NOT NULL); INSERT INTO @sperrenSELECT 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- und HOBT-Sperren nennen eine hobt_id. Den Tabellennamen kennt nur die   jeweilige Datenbank; deshalb je Datenbank ein kurzer Blick in sys.partitions. Datenbanken   ohne Zugriff werden übersprungen, dort bleibt die ID stehen. */DECLARE @namen TABLE (database_id int NOT NULL, hobt_id bigint NOT NULL,                      objekt nvarchar(300) NULL, index_name sysname NULL);DECLARE @db int, @liste 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 @sperren          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, @liste;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 (' + @liste + N')OPTION (MAXDOP 1);';    BEGIN TRY        INSERT INTO @namen EXEC sys.sp_executesql @sql2;    END TRY    BEGIN CATCH        -- kein Zugriff auf diese Datenbank: IDs bleiben stehen    END CATCH;    FETCH NEXT FROM dbs INTO @db, @liste;END;CLOSE dbs;DEALLOCATE dbs; /* 2a · Sitzungen mit Sperren oder offener Transaktion   isolationsstufe: Einstellung der Sitzung. READ COMMITTED liest in einer Datenbank mit RCSI   trotzdem Zeilenversionen, ohne zu warten.   letzter_batch: zuletzt gesendeter Batch der Verbindung (gekürzt), bei schlafenden Sitzungen   oft der Befehl, der die Transaktion geöffnet hat. Kein Hostname, um die Ausgabe teilen zu können. */SELECT es.session_id,       es.login_name                                                    AS login,       es.program_name                                                  AS programm,       es.status,       es.open_transaction_count                                        AS offene_transaktionen,       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'unbekannt' END        AS isolationsstufe,       tx.beginn                                                        AS transaktion_seit,       DATEDIFF(SECOND, tx.beginn, SYSDATETIME())                       AS transaktion_s,       COALESCE(s.ressourcen, 0)                                        AS sperren,       COALESCE(s.wartend, 0)                                           AS davon_wartend,       COALESCE(s.xact, 0)                                              AS xact_sperren,       r.wait_type                                                      AS wartet_auf,       NULLIF(r.blocking_session_id, 0)                                 AS blockiert_von,       LEFT(REPLACE(REPLACE(t.text, CHAR(13), N' '), CHAR(10), N' '), 200) AS letzter_batchFROM sys.dm_exec_sessions AS esLEFT JOIN (SELECT session_id,                  SUM(ressourcen)                                                   AS ressourcen,                  SUM(CASE WHEN request_status <> N'GRANT' THEN ressourcen ELSE 0 END) AS wartend,                  SUM(CASE WHEN resource_type = N'XACT' THEN ressourcen ELSE 0 END)    AS xact           FROM @sperren 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 beginn             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 sperren DESC, es.session_idOPTION (MAXDOP 1); /* 2b · Sperren je Sitzung, Datenbank, Objekt, Ressource, Modus und Status   Ressourcentypen: OBJECT = Tabelle, PAGE = Seite, KEY = Indexzeile, RID = Heap-Zeile,   XACT = Transaktion (Optimized Locking). Status GRANT = gehalten, WAIT/CONVERT = angefordert. */SELECT TOP (500)       s.session_id,       DB_NAME(s.database_id)                                           AS datenbank,       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.objekt, N'hobt_id ' + CAST(s.entity_id AS nvarchar(20)))            WHEN s.resource_type = N'XACT' THEN COALESCE(n.objekt, N'(Transaktion)')            ELSE N'' END                                                AS objekt,       n.index_name                                                     AS index_name,       s.resource_type                                                  AS ressource,       s.request_mode                                                   AS modus,       s.request_status                                                 AS status,       s.ressourcen,       s.eintraege,       s.partitionenFROM @sperren AS sLEFT JOIN @namen 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, datenbank, objekt, 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 · Wartende Anfragen mit Blocker   LCK_M_S_XACT_MODIFY / LCK_M_S_XACT_READ / LCK_M_S_XACT (ab 2025): Die Anfrage wartet auf das   Ende einer anderen Transaktion, nicht auf eine Zeilensperre. wait_resource nennt dann die   XACT-Ressource und dahinter die betroffene Zeile. Wer Wartezeiten nur nach LCK_M_S, LCK_M_U   und LCK_M_X filtert, übersieht diese Fälle. */SELECT r.session_id,       NULLIF(r.blocking_session_id, 0)                                 AS blockiert_von,       r.wait_type,       CASE WHEN r.wait_type LIKE N'LCK[_]M[_]%XACT%' THEN N'wartet auf Transaktionsende (XACT)'            WHEN r.wait_type LIKE N'LCK[_]M[_]%'      THEN N'wartet auf Sperre'            ELSE N'anderer Wartegrund' END                              AS art,       r.wait_time                                                      AS wartet_ms,       r.wait_resource,       DB_NAME(r.database_id)                                           AS datenbank,       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'unbekannt' END        AS isolationsstufe,       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 anweisungFROM 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 · Schalter je Datenbank

datenbank rcsi snapshot_isolation adr optimized_locking laq_moeglich wiederherstellungsmodell kompatibilitaet log_wartet_auf
--------- ---- ------------------ --- ----------------- ------------ ------------------------ --------------- --------------
Kasse     an   OFF                an  an                ja           SIMPLE                   170             NOTHING
Lager     an   ON                 an  aus               nein         FULL                     170             NOTHING
Verkauf   aus  OFF                aus aus               nein         SIMPLE                   170             NOTHING
master    aus  ON                 aus aus               nein         SIMPLE                   170             CHECKPOINT
msdb      aus  ON                 aus aus               nein         SIMPLE                   170             NOTHING
tempdb    aus  OFF                aus aus               nein         SIMPLE                   170             NOTHING

(Auf SQL Server 2017 stehen adr, optimized_locking und laq_moeglich auf n/a, auf 2019 und 2022
 optimized_locking und laq_moeglich.)


2a · Sitzungen mit Sperren oder offener Transaktion (gekürzt)

session_id login       programm        status  offene_transaktionen isolationsstufe transaktion_s sperren davon_wartend xact_sperren wartet_auf          blockiert_von letzter_batch
---------- ----------- --------------- ------- -------------------- --------------- ------------- ------- ------------- ------------ ------------------- ------------- ------------------------------
57         app_buchung Buchungsdienst  running 1                    READ COMMITTED  7             3086    0             0            WAITFOR             NULL          BEGIN TRAN; UPDATE dbo.Konto ...
56         app_buchung Buchungsdienst  running 1                    READ COMMITTED  7             531     0             0            WAITFOR             NULL          BEGIN TRAN; UPDATE dbo.Konto ...
58         app_buchung Buchungsdienst  running 2                    READ COMMITTED  4             3       1             0            LCK_M_X             56            (@1 int,@2 tinyint)UPDATE [dbo].[Konto] ...
62         app_bericht Berichte        running 0                    READ COMMITTED  NULL          3       1             0            LCK_M_S             57            SELECT saldo FROM dbo.Konto WHERE id = 10
55         app_kasse   Kassendienst    running 1                    READ COMMITTED  7             2       0             1            WAITFOR             NULL          BEGIN TRAN; UPDATE dbo.Konto ...
60         app_kasse   Kassendienst    running 2                    READ COMMITTED  4             2       1             1            LCK_M_S_XACT_MODIFY 55            (@1 int,@2 tinyint)UPDATE [dbo].[Konto] ...
63         app_bericht Berichte        running 0                    READ COMMITTED  NULL          2       1             1            LCK_M_S_XACT_READ   60            SELECT ... WITH (READCOMMITTEDLOCK) ...
61         app_bericht Berichte        running 0                    READ COMMITTED  NULL          1       1             0            LCK_M_IS            57            SELECT COUNT(*) FROM dbo.Klein

(Im Lab stand bei Sitzung 55/57/56 status = running, weil sqlcmd die Transaktion mit WAITFOR
 offen hielt; eine Anwendung, die nach dem UPDATE nichts mehr sendet, erscheint als sleeping.)


2b · Sperren je Sitzung, Datenbank, Objekt, Ressource, Modus und Status

session_id datenbank objekt         index_name ressource modus status ressourcen eintraege partitionen
---------- --------- -------------- ---------- --------- ----- ------ ---------- --------- -----------
55         Kasse     (Transaktion)  NULL       XACT      X     GRANT  1          1         1
55         Kasse     [dbo].[Konto]  NULL       OBJECT    IX    GRANT  1          1         1
56         Lager     [dbo].[Konto]  NULL       OBJECT    IX    GRANT  1          1         1
56         Lager     [dbo].[Konto]  PK_Konto   PAGE      IX    GRANT  30         30        1
56         Lager     [dbo].[Konto]  PK_Konto   KEY       X     GRANT  500        500       1
57         Verkauf   [dbo].[Klein]  NULL       OBJECT    X     GRANT  1          1         1
57         Verkauf   [dbo].[Konto]  NULL       OBJECT    IX    GRANT  1          1         1
57         Verkauf   [dbo].[Konto]  PK_Konto   PAGE      IX    GRANT  84         84        1
57         Verkauf   [dbo].[Konto]  PK_Konto   KEY       X     GRANT  3000       3000      1
58         Lager     [dbo].[Konto]  PK_Konto   KEY       X     WAIT   1          1         1
60         Kasse     [dbo].[Konto]  PK_Konto   XACT      S     WAIT   1          1         1
61         Verkauf   [dbo].[Klein]  NULL       OBJECT    IS    WAIT   1          1         1
62         Verkauf   [dbo].[Konto]  PK_Konto   KEY       S     WAIT   1          1         1
63         Kasse     [dbo].[Konto]  PK_Konto   XACT      S     WAIT   1          1         1
(gekürzt: Absichtssperren IS/IX der Wartenden auf Tabelle und Seite)

Lesart: Ohne Optimized Locking hält Sitzung 57 bis zum Commit 3.000 Zeilensperren und 84
Seitensperren. Mit Optimized Locking hält Sitzung 55 für ebenso viele geänderte Zeilen nur die
Sperre auf ihre Transaktion (XACT X) und die Absichtssperre auf die Tabelle.

Auf einem Server mit 24 logischen CPUs (Lab: SQL Server 2017 und 2019) sah die Tabellensperre auf
dbo.Klein so aus (Lock Partitioning: eine Ressource, 24 Einträge):
57         Verkauf   [dbo].[Klein]  NULL       OBJECT    X     GRANT  1          24        24


3 · Wartende Anfragen mit Blocker

session_id blockiert_von wait_type           art                                 wartet_ms wait_resource                                              datenbank anweisung
---------- ------------- ------------------- ----------------------------------- --------- ---------------------------------------------------------- --------- ------------------------------------
58         56            LCK_M_X             wartet auf Sperre                   4436      KEY: 6:72057594047234048 (d08358b1108f)                    Lager     UPDATE [dbo].[Konto] set [saldo] = ...
61         57            LCK_M_IS            wartet auf Sperre                   4421      OBJECT: 5:1269579561:0                                     Verkauf   SELECT @x = COUNT(*) FROM dbo.Klein
62         57            LCK_M_S             wartet auf Sperre                   4171      KEY: 5:72057594047234048 (d08358b1108f)                    Verkauf   SELECT @x = saldo FROM dbo.Konto ...
60         55            LCK_M_S_XACT_MODIFY wartet auf Transaktionsende (XACT)  4170      XACT: 7:1107:0 KEY: 7:72057594047234048 (d08358b1108f)     Kasse     UPDATE [dbo].[Konto] set [saldo] = ...
63         60            LCK_M_S_XACT_READ   wartet auf Transaktionsende (XACT)  4166      XACT: 7:1107:0 KEY: 7:72057594047234048 (286fc18d83ea)     Kasse     SELECT @x = saldo FROM dbo.Konto WITH (READCOMMITTEDLOCK) ...

Lesart: Sitzung 63 wartet auf dieselbe Transaktion (XACT: 7:1107:0) wie Sitzung 60, aber auf eine
andere Zeile. blockiert_von zeigt dennoch 60, also eine selbst wartende Sitzung, nicht den
Halter 55. Das kam in allen fünf 2025-Läufen vor (je zwei auf CU9 und CU10, ein Pilot auf CU10): blockiert_von zeigte auf die
Sitzung, die zuerst auf die Transaktion zu warten begann.