SQL Server Blocking und Timeouts untersuchen

Dieser Diagnoseweg ist Teil unseres Ratgebers SQL Server Performance. Wenn Sie noch nicht wissen, welcher Weg zu Ihrem Problem passt, beginnen Sie dort.

In diesem Diagnoseweg untersuchen Sie solche Sperrkonflikte. Sie lernen, die wartenden Vorgänge vom Anfang der Blocking-Kette zu unterscheiden und auch einen schlafenden Headblocker zu erkennen.

Was Blocking, Timeouts und Deadlocks unterscheidet

Die Begriffe beschreiben unterschiedliche Situationen:

Ein Query Timeout sagt also nur, dass die Zeit abgelaufen ist. Blocking ist eine mögliche Ursache, aber nicht die einzige.

Ein Connection Timeout tritt dagegen beim Verbindungsaufbau auf. Wenn noch gar kein Befehl an SQL Server gesendet wurde, passt die Suche nach der blockierenden Abfrage nicht zu dieser Meldung. Microsoft erklärt Query Timeouts und ihre Abgrenzung zum Connection Timeout.

Für einen Lock Timeout ist die Einstellung LOCK_TIMEOUT maßgeblich. Standard ist -1, also unbegrenztes Warten. Sie gilt für die jeweilige Verbindung und ist kein allgemeines Laufzeitlimit für jede Abfrage. Microsoft dokumentiert SET LOCK_TIMEOUT.

Warum ein schlafender Headblocker andere Vorgänge aufhalten kann

Zwei Begriffe vorab: Eine Sitzung ist eine Verbindung zu SQL Server. Ein Request ist ein Befehl, den diese Sitzung gerade ausführt. Eine Sitzung kann bestehen, ohne dass gerade ein Request läuft.

Eine Sitzung kann eine Transaktion beginnen, Daten ändern und anschließend auf den nächsten Befehl der Anwendung warten. Ist die Transaktion noch offen, bleiben ihre Sperren bestehen.

Der Sitzungszustand sleeping bedeutet deshalb nur, dass gerade kein Request läuft. Er sagt nicht, dass alle Transaktionen beendet und alle Sperren freigegeben sind. Viele schlafende Sitzungen sind völlig unauffällig. Interessant wird eine Sitzung, wenn andere auf sie warten und sie eine offene Transaktion hat. Microsoft erläutert die Sitzungszustände.

Beispiel mit einem schlafenden Headblocker

In unserem synthetischen Versuch arbeiteten drei Verbindungen mit zwei Datenzeilen:

1. A begann eine Transaktion, änderte Zeile 1 und wartete danach auf den nächsten Befehl. Die Transaktion blieb offen.
2. B änderte innerhalb einer eigenen Transaktion Zeile 2. Beim anschließenden Änderungsversuch an Zeile 1 musste B auf A warten.
3. C wollte Zeile 2 ändern und musste deshalb auf B warten.

Die Kette lautet C → B → A. Jeder Pfeil bedeutet „wartet auf“.

Wer nur die laufenden Requests betrachtet, sieht B und C, also die Wartenden. Den Anfang der Kette findet man erst, wenn man auch Sitzungen ohne laufenden Request einbezieht.

Im Lab wurde die Transaktion in A gezielt zurückgerollt. Danach konnten B und C weiterlaufen.

Schritt 1: Fehlermeldung und Zeitpunkt sichern

Notieren Sie zuerst den vollständigen Fehlertext und die betroffene Aufgabe. „Timeout beim Speichern“ ist ein guter Ausgangspunkt, aber noch keine Ursache.

Halten Sie fest:

• Zeitpunkt und betroffene Anwendung;
• Datenbank und möglichst den konkreten Vorgang;
• Fehlertext und Fehlernummer, soweit vorhanden (1205, 1222 oder ein Timeout der Anwendung);
• nach wie vielen Sekunden abgebrochen wurde;
• ob die Verbindung bereits bestand oder erst aufgebaut werden sollte;
• ob andere Vorgänge im gleichen Zeitraum ebenfalls warteten.

Ein allgemeiner Query Timeout beweist keinen Deadlock. Für den Wartekreis benötigen Sie den passenden Deadlockgraphen. Microsoft beschreibt Deadlocks und die Auswahl des Opfers.

Ihr erster Befund: Sie wissen, welche Art von Abbruch gemeldet wurde und welches Zeitfenster untersucht werden muss.

Schritt 2: Die aktuelle Blocking-Kette prüfen

Wenn die Störung gerade auftritt, beginnen Sie mit einer aktuellen Sicht auf die blockierten Vorgänge.

In SQL Server Management Studio finden Sie auf Instanzebene unter Reports → Standard Reports → Activity – All Blocking Transactions die aktuellen Blocking-Zusammenhänge. Im Activity Monitor zeigt die Prozessliste die Spalten Blocked By und Head Blocker; eine 1 in Head Blocker markiert den Anfang einer Kette. Bezeichnungen können je Oberflächensprache abweichen. Microsoft nennt diese Einstiege zur Blocking-Diagnose.

Gehen Sie von einer betroffenen Sitzung aus:

1. Welche Sitzung blockiert sie gerade?
2. Wartet diese blockierende Sitzung selbst auf eine weitere Sitzung?
3. Wo endet die Kette?
4. Welche Sitzung steht dort, auch wenn sie gerade keinen laufenden Request hat?

Diese Fragen beantwortet auch die folgende Abfrage. Sie verbindet zwei Systemsichten: sys.dm_exec_requests zeigt laufende Requests mit Wartetyp, blocking_session_id und gesperrter Ressource. sys.dm_exec_sessions enthält auch Sitzungen ohne laufenden Request und mit open_transaction_count die Zahl ihrer offenen Transaktionen. Erst zusammen zeigen sie den schlafenden Headblocker.

/* PSG Stack · SQL Server Performance Ratgeber · Abfrage 2
   Aktuelle Blocking-Kette, einschließlich schlafender Headblocker.

   - Nur lesend. Beendet keine Sitzung und ändert nichts.
   - Läuft mit MAXDOP 1, damit die Diagnose den Server nicht zusätzlich
     mit paralleler Arbeit belastet.
   - Zeigt nur, was im Moment der Ausführung blockiert ist.
   - Rechte: VIEW SERVER STATE
     (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE).
     Ohne diese Rechte bricht die Abfrage mit Fehler 300 ab.
   - Ab SQL Server 2016 SP1 (sys.dm_exec_input_buffer).
     Geprüft im PSG-Lab auf 2017, 2019, 2022 und 2025.
   - ebene 0 = Headblocker (Anfang der Kette). pfad liest sich
     "Headblocker <- wartet <- wartet".
   - Sonderwerte von blocking_session_id (-2, -3, -4) und Sitzungen,
     die nur auf eigene parallele Worker warten, werden ausgelassen.
*/
WITH anfragen AS (
    SELECT session_id, blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
      AND blocking_session_id <> session_id
),
koepfe AS (            -- blockieren jemanden, warten selbst aber auf niemanden
    SELECT DISTINCT a.blocking_session_id AS session_id
    FROM anfragen AS a
    WHERE NOT EXISTS (SELECT 1 FROM anfragen AS b
                      WHERE b.session_id = a.blocking_session_id)
),
kette AS (
    SELECT k.session_id,
           CAST(NULL AS smallint)                AS wartet_auf,
           0                                     AS ebene,
           k.session_id                          AS headblocker,
           CAST(k.session_id AS varchar(400))    AS pfad
    FROM koepfe AS k
    UNION ALL
    SELECT a.session_id,
           CAST(a.blocking_session_id AS smallint),
           ke.ebene + 1,
           ke.headblocker,
           CAST(ke.pfad + ' <- ' + CAST(a.session_id AS varchar(10)) AS varchar(400))
    FROM anfragen AS a
    JOIN kette AS ke ON a.blocking_session_id = ke.session_id
)
SELECT
    ke.ebene,
    ke.pfad,
    ke.session_id,
    ke.wartet_auf,
    s.status                       AS sitzung_status,      -- sleeping = gerade kein Befehl
    r.status                       AS request_status,      -- NULL = kein laufender Request
    s.open_transaction_count       AS offene_transaktionen,
    tx.transaktion_seit,
    r.wait_type,
    r.wait_time                    AS wartet_ms,
    r.wait_resource,
    DB_NAME(s.database_id)         AS datenbank,
    s.program_name,
    s.host_name,
    s.login_name,
    s.login_time,
    SUBSTRING(st.text, r.statement_start_offset / 2 + 1,
        (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1)
                                   AS aktuelles_statement,
    ib.event_info                  AS letzter_batch          -- zuletzt gesendeter Befehl
FROM kette AS ke
JOIN sys.dm_exec_sessions AS s        ON s.session_id = ke.session_id
LEFT JOIN sys.dm_exec_requests AS r   ON r.session_id = ke.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
OUTER APPLY sys.dm_exec_input_buffer(ke.session_id, NULL) AS ib
OUTER APPLY (SELECT MIN(at.transaction_begin_time) AS transaktion_seit
             FROM sys.dm_tran_session_transactions AS sto
             JOIN sys.dm_tran_active_transactions AS at
                  ON at.transaction_id = sto.transaction_id
             WHERE sto.session_id = ke.session_id) AS tx
ORDER BY ke.headblocker, ke.ebene, ke.session_id
OPTION (MAXDOP 1, MAXRECURSION 100);

Beispielausgabe aus unserem Lab (SQL Server 2022, gekürzt):

ebene pfad            session_id wartet_auf sitzung_status request_status offene_transaktionen wait_type wartet_ms letzter_batch
0     54              54         NULL       sleeping       NULL           1                    NULL      NULL      BEGIN TRAN; UPDATE dbo.t SET v = v + 1 WHERE id = 1;
1     54 <- 56        56         54         running        suspended      2                    LCK_M_X   6152      UPDATE dbo.t SET v = v + 1 WHERE id = 1;
2     54 <- 56 <- 80  80         56         running        suspended      2                    LCK_M_X   4026      UPDATE dbo.t SET v = v + 1 WHERE id = 2;

Sitzung 54 ist der Anfang der Kette: Sie schläft, hat keinen laufenden Request, hält aber eine offene Transaktion. Die Sitzungen 56 und 80 warten nur. Um die Kette aufzulösen, muss man sich um Sitzung 54 kümmern, nicht um die Wartenden. Bei Sitzungen mit laufendem Request lag offene_transaktionen in unserem Test um eins höher als erwartet; aussagekräftig ist die Spalte vor allem bei schlafenden Sitzungen.

Ein Wait wie LCK_M_X bedeutet: Dieser Request wartet auf eine exklusive Sperre. Die Spalte wait_resource zeigt, worauf genau, etwa eine Zeile (KEY: …), eine Seite (PAGE: …) oder ein ganzes Objekt (OBJECT: …). Den verantwortlichen Anwendungsschritt benennt der Wait allein noch nicht. Verfolgen Sie deshalb die Kette bis zum Anfang.

Schritt 3: Den Headblocker und seine Transaktion untersuchen

Beim Headblocker interessiert vor allem, weshalb die Sperre bestehen bleibt. Ergänzen Sie zur Sitzungsnummer:

Der letzte Batch ist ein Anhaltspunkt. Eine längere Transaktion kann vorher bereits andere Statements ausgeführt haben. Er erklärt deshalb nicht zwingend alle noch gehaltenen Sperren.

Prüfen Sie mit der Anwendung oder ihrem Entwicklungsteam, ob ein Abschluss fehlt: Wartet der Ablauf auf Benutzereingaben? Wurde nach einem Fehler ein Teil des Transaktionsablaufs übersprungen? Ist der eigentliche Befehl bereits abgebrochen, während die Transaktion offen blieb?

Eine Timeoutmeldung ist kein Beleg, dass die Transaktion beendet wurde. Bei einem Query Timeout oder Lock Timeout bricht SQL Server nur das laufende Statement ab. Eine explizit begonnene Transaktion bleibt offen, bis die Anwendung sie zurückrollt oder die Verbindung schließt. Bei Connection Pooling kann das deutlich später sein. Microsoft beschreibt diesen Blockingfall nach Timeout oder Abbruch.

Schritt 4: Vergangene Blockierungen mit passenden Belegen untersuchen

Wenn der Timeout vorbei ist und keine Kette mehr sichtbar ist, zeigt die aktuelle Sitzungsliste nicht, was zuvor passiert ist.

Prüfen Sie dann, welche Daten für das Zeitfenster bereits vorhanden sind: gespeicherte Blocking-Snapshots eines Monitorings, passende Ereignisse oder Anwendungslogs. Query-Store-Wartewerte können die Untersuchung ergänzen, ersetzen aber keine aufgezeichnete Kette mit ihren beteiligten Sitzungen.

Blocked Process Reports dokumentieren länger anhaltende Sperrkonflikte. Dafür braucht es zwei Dinge: die Serveroption blocked process threshold (s) (Standard 0, also aus) und eine Extended-Events-Sitzung, die das Ereignis blocked_process_report aufzeichnet. Die standardmäßig laufende Sitzung system_health erfasst diese Reports nicht. Kurze Konflikte können vollständig unter der Schwelle bleiben.

Was unser Vergleich mit Blocked Process Reports zeigt

Im gleichen Drei-Verbindungen-Lab untersuchten wir drei Fälle:

Vier Reports bedeuteten hier nicht vier unabhängige Störungen. Es waren wiederholte Beobachtungen zweier Beziehungen innerhalb derselben Kette. Die fünf Sekunden waren eine Versuchsbedingung, keine Empfehlung. Eine Schwelle garantiert auch keine Meldung exakt zu diesem Zeitpunkt; Microsoft beschreibt die Erzeugung als best effort. Dokumentation zur Berichtsschwelle.

Bei einem vermuteten Deadlock prüfen Sie stattdessen die Deadlockereignisse. Diese erfasst die Sitzung system_health standardmäßig. Ob der gesuchte Fall noch darin enthalten ist, hängt davon ab, wie viel seitdem aufgezeichnet wurde. Microsoft erläutert die Deadlockereignisse in system_health.

Ihr Befund darf auch eine Lücke benennen: „Der Konflikt ist aktuell nicht mehr sichtbar und wurde im fraglichen Zeitraum nicht aufgezeichnet.“ Daraus folgt ein gezielter Plan für die nächste Beobachtung.

Schritt 5: Die Maßnahme und ihre Wirkung prüfen

Die geeignete Maßnahme hängt vom nachgewiesenen Ablauf ab:

• Eine Transaktion bleibt nach Fehler oder Timeout offen: Die Anwendung muss in diesem Fall zurückrollen. Am einfachsten geht das mit SET XACT_ABORT ON am Anfang von Procedures oder der Verbindung: Dann rollt SQL Server die Transaktion bei einem Laufzeitfehler und bei einem Abbruch durch die Anwendung selbst zurück. Alternativ fängt ein TRY…CATCH mit ROLLBACK den Fehler ab. Wir haben das im Lab auf SQL Server 2022 geprüft: Ohne XACT_ABORT blieben nach einem Timeout der Anwendung und nach einem Lock Timeout (Fehler 1222) Transaktion und Sperre bestehen; mit XACT_ABORT ON waren beide weg.
• Eine notwendige Transaktion hält Sperren sehr lange: Umfang, Zugriffsweg und fachlich nötige Transaktionsgrenzen untersuchen.
• Vorgänge geraten wiederholt an denselben Ressourcen in Konflikt: Reihenfolge und Ablauf der Zugriffe gemeinsam betrachten.
• Ein SELECT hält unerwartet viele Sperren: Den konkreten Plan untersuchen. Unser Beitrag SELECT und Lock Escalation in SQL Server vertieft einen solchen Sonderfall.

Eine Sitzung mit KILL zu beenden, kann eine akute Entscheidung sein, ersetzt aber keine Ursachenklärung. SQL Server muss die offene Transaktion dann zurückrollen, und das kann selbst dauern. Den Fortschritt zeigt KILL <Sitzungsnummer> WITH STATUSONLY. Dokumentieren Sie vor einem Eingriff, welche Transaktion betroffen ist und welche fachlichen Folgen ein Abbruch hätte.

Ein höherer Query Timeout verändert zunächst nur das Zeitbudget. Ob dieses Budget zur Aufgabe passt, ist eine berechtigte Frage. Die Ursache einer nachgewiesenen Blocking-Kette bleibt trotzdem zu bearbeiten.

Ein Wechsel des Isolationsverfahrens ist eine eigene fachliche Entscheidung. Read Committed Snapshot Isolation (RCSI) hilft Lesern, die bisher auf Schreiber warten mussten: Sie lesen dann den letzten bestätigten Stand. Die hier gezeigte Kette besteht aber aus Konflikten zwischen Schreibern, und die löst RCSI nicht.

Den Blocking-Befund verständlich festhalten

Für unser Lab lässt sich der Befund so formulieren:

C wartete auf B. B wartete auf A und hielt selbst bereits eine Sperre, die C benötigte. A hatte keinen laufenden Request, aber eine offene Transaktion. Nach dem gezielten Rollback in A konnten die anderen Vorgänge weiterlaufen. Der Anfang der Kette lag damit bei A.

Für Ihren eigenen Fall ergänzen Sie Zeitpunkt, Anwendung, Transaktionsbeginn und die vorhandenen Belege. Nach einer Änderung beobachten Sie, ob derselbe Konflikt unter vergleichbaren Bedingungen erneut auftritt und ob die betroffenen Vorgänge ohne Timeout abschließen. Dass eine Kette gerade verschwunden ist, zeigt nur das Ende dieser einen Beobachtung; die dauerhafte Wirkung zeigt erst der Vergleich über mehrere vergleichbare Zeiträume.

Weitere Diagnosewege