SQL Server Blockierungen analysieren: Blockierer finden und Ursachen beheben

Eine Anwendung reagiert nicht mehr, ein UPDATE läuft ungewöhnlich lange und weitere Benutzer warten auf ihre Ergebnisse. Dahinter kann eine Blockierung stecken. Wenn du SQL Server Blockierungen analysieren möchtest, brauchst du deshalb zunächst Antworten auf drei Fragen: Welche Sitzung wartet? Wer blockiert sie? Und warum hält diese Sitzung die benötigten Sperren noch fest?

Diese Anleitung zeigt dir lesende Diagnoseabfragen für laufende Requests, beteiligte Sitzungen und offene Transaktionen. Anschließend kannst du das Verhalten mit zwei Testsitzungen nachvollziehen. Außerdem erfährst du, welche Gegenmaßnahmen zur jeweiligen Ursache passen und welche Folgen ein KILL-Befehl hat.

Die Beispiele richten sich an SQL Server ab Version 2016. Für SQL Server 2022 und neuere Versionen unterscheiden sich die erforderlichen Diagnoseberechtigungen von älteren Versionen. Azure SQL Database hat teilweise andere Berechtigungs- und Sichtbarkeitsregeln; die folgenden Angaben beziehen sich auf eine SQL-Server-Instanz.

Blocking, Deadlocks und langsame Abfragen unterscheiden

Nicht jede langsame Abfrage ist blockiert. Und nicht jede Blockierung ist ein Deadlock. Diese Unterscheidung entscheidet darüber, welche Diagnose sinnvoll ist.

Kurze Blockierungen gehören zum normalen Betrieb einer Datenbank. Problematisch werden sie, wenn ihre Dauer oder die Anzahl wartender Vorgänge den vorgesehenen Ablauf beeinträchtigt. Dafür gibt es keine allgemeingültige Grenze: Ein interaktiver Vorgang kann bereits nach wenigen Sekunden unbrauchbar sein, während eine geplante Stapelverarbeitung längere Laufzeiten verträgt.

Für zyklische Blockierungen findest du eine eigene Anleitung unter SQL Server Deadlocks erkennen und beheben. Dort steht die Auswertung des Deadlock-Graphen im Mittelpunkt.

Ein Wartezustand allein beweist noch keine Sperrblockierung

Ein Request mit dem Status suspended wartet auf eine Ressource. Welche Ressource das ist, erkennst du am Wartetyp:

• LCK_M_... bezeichnet Wartearten beim Erwerb von Sperren.
• PAGEIOLATCH_... kann auf das Warten auf eine Datenseite hinweisen, die gerade vom Datenträger gelesen wird.
• RESOURCE_SEMAPHORE bezeichnet das Warten auf eine Speicherzuteilung für die Abfrageausführung.
• ASYNC_NETWORK_IO kann auftreten, wenn SQL Server auf die Abnahme von Ergebnissen durch den Client wartet.

Eine hohe Gesamtlaufzeit bei geringer CPU-Zeit spricht für einen erheblichen Warteanteil, benennt aber noch nicht dessen Ursache. Bei parallelen Abfragen kann die aufsummierte CPU-Zeit sogar über der verstrichenen Laufzeit liegen. Weitere Hintergründe beschreibt Microsoft in der Anleitung zur Analyse langsamer Abfragen.

Vorbereitung: Berechtigungen und Grenzen der Diagnose

Die folgenden Diagnoseabfragen lesen dynamische Verwaltungsansichten und Verwaltungsfunktionen. Sie ändern weder Anwendungsdaten noch Servereinstellungen. Trotzdem benötigen sie ausreichende Rechte, um fremde Sitzungen und deren SQL-Texte anzuzeigen.

Ohne die passenden Rechte liefern einzelne Ansichten nur Informationen zur eigenen Sitzung; andere Zugriffe können mit einem Berechtigungsfehler scheitern. Ein leeres Ergebnis ist deshalb nicht automatisch ein Beleg dafür, dass keine Blockierung existiert. Die versionsabhängigen Anforderungen stehen beispielsweise in der Dokumentation zu sys.dm_exec_sessions.

Lass die Diagnoseberechtigungen gezielt vergeben. Eine Mitgliedschaft in sysadmin ist für die hier gezeigten SELECT-Abfragen nicht erforderlich. Beachte außerdem, dass SQL-Texte vertrauliche Werte enthalten können. Speichere und teile Diagnoseergebnisse entsprechend sorgfältig.

Alle Ergebnisse sind Momentaufnahmen. Während die Abfrage läuft, können Requests enden, Transaktionen abgeschlossen und Verbindungen getrennt werden. Wiederhole die Diagnose bei Bedarf mit etwas Abstand. Auch rein lesende Diagnoseabfragen verursachen Aufwand; eine Abfrage in sehr kurzen Dauerschleifen ist für die erste Untersuchung nicht notwendig.

Schritt 1: Laufende Requests und ihre SQL-Texte anzeigen

Die erste Abfrage verbindet sys.dm_exec_requests mit sys.dm_exec_sessions. Über sys.dm_exec_sql_text ergänzt sie den SQL-Text.

Sie zeigt bewusst auch Requests ohne erkannte Blockierung an. So bleibt ein aktiv arbeitender Blockierer im Ergebnis sichtbar.

SELECT
    SYSDATETIME() AS capture_time,
    r.session_id,
    r.request_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    r.status AS request_status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS current_wait_ms,
    r.wait_resource,
    r.total_elapsed_time AS elapsed_ms,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    s.open_transaction_count AS session_open_transactions,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE
                        WHEN r.statement_end_offset IS NULL
                             OR r.statement_end_offset = -1
                            THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS current_statement,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY
    CASE WHEN r.blocking_session_id > 0 THEN 0 ELSE 1 END,
    r.wait_time DESC,
    r.total_elapsed_time DESC;

current_statement grenzt die gerade ausgeführte Anweisung innerhalb des Batches ein. batch_text liefert den umfassenderen Kontext. Die Positionsangaben sind Byte-Offsets; deshalb werden sie für den Unicode-Text durch zwei geteilt. Bei verschlüsselten Modulen kann der SQL-Text fehlen. Details dazu enthält die Dokumentation zu sys.dm_exec_sql_text.

Diese Spalten sind für die erste Einordnung wichtig

• session_id: Kennung der Sitzung, häufig auch SPID genannt.
• blocking_session_id: Eine positive Zahl verweist auf die blockierende Sitzung.
• wait_type: Aktuelle Warteart. Für die Untersuchung von Sperrkonflikten sind insbesondere LCK_M_...-Werte interessant.
• current_wait_ms: Dauer des aktuellen Wartevorgangs, nicht die gesamte bisherige Blockierungsdauer.
• elapsed_ms: Bisherige Laufzeit des Requests einschließlich Wartephasen.
• wait_resource: Technische Beschreibung der Ressource, auf die gewartet wird.

Ein Wert von 0 oder NULL bei blocking_session_id bedeutet, dass kein Blockierer ausgewiesen wird; teilweise ist er auch nicht bestimmbar. Negative Werte sind Sonderfälle. Beispielsweise bezeichnet -2 eine verwaiste verteilte Transaktion. Solche Werte sind keine normalen Sitzungsnummern.

Bei parallelen Requests bildet diese Ansicht nicht alle Wartezustände einzelner Tasks ab. Für eine vertiefte Untersuchung kann zusätzlich sys.dm_os_waiting_tasks erforderlich sein. Die genaue Bedeutung der Spalten und Sonderwerte beschreibt Microsoft unter sys.dm_exec_requests.

Schritt 2: Die Blockierungskette bis zum Anfang verfolgen

Eine wartende Sitzung kann selbst bereits Sperren halten und dadurch weitere Sitzungen blockieren. Deshalb reicht es nicht, nur die unmittelbar blockierende Sitzung zu betrachten.

Ein vereinfachtes Beispiel:

Verfolge also von Sitzung 84 aus die Verweise: 84 → 73 → 62. Untersuche anschließend Sitzung 62 und ihren Transaktionskontext.

Ein Abbruch von Sitzung 84 würde den ursprünglichen Konflikt zwischen 73 und 62 nicht beseitigen. Auch Sitzung 73 ist nicht automatisch die Ursache, nur weil sie als Blockierer von 84 erscheint.

Fehlt Sitzung 62 in der Request-Abfrage, kann sie gerade untätig sein. Genau dafür brauchst du im nächsten Schritt eine Abfrage, die von den Sitzungen ausgeht.

Schritt 3: Auch schlafende Blockierer finden

sys.dm_exec_requests zeigt laufende Requests. sys.dm_exec_sessions enthält dagegen auch Sitzungen, die gerade keinen Request ausführen.

Eine Sitzung mit dem Status sleeping wartet auf einen neuen Auftrag des Clients. Das sagt nichts darüber aus, ob ihre vorherige Transaktion bereits beendet wurde.

Die folgende Abfrage zeigt Sitzungen, die aktuell als Blockierer referenziert werden oder offene Transaktionen melden. Der Input Buffer ergänzt den zuletzt übermittelten Befehl beziehungsweise Batch.

SELECT
    SYSDATETIME() AS capture_time,
    s.session_id,
    s.login_time,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,
    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM sys.dm_exec_requests AS blocked_request
            WHERE blocked_request.blocking_session_id = s.session_id
        )
        THEN 1
        ELSE 0
    END AS is_current_blocker,
    ib.event_info AS last_submitted_batch
FROM sys.dm_exec_sessions AS s
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE s.is_user_process = 1
  AND s.session_id <> @@SPID
  AND
  (
      s.open_transaction_count > 0
      OR EXISTS
      (
          SELECT 1
          FROM sys.dm_exec_requests AS blocked_request
          WHERE blocked_request.blocking_session_id = s.session_id
      )
  )
ORDER BY
    is_current_blocker DESC,
    s.open_transaction_count DESC,
    s.last_request_end_time;

Die Kombination aus sleeping, open_transaction_count > 0 und is_current_blocker = 1 ist besonders interessant: Der Client sendet gerade keinen Auftrag, während die Sitzung weiterhin andere Requests aufhält.

Eine offene Transaktion allein beweist dagegen noch keinen relevanten Sperrkonflikt. Ebenso ist der zuletzt gesendete Batch nicht zwingend die Anweisung, die die Sperren ursprünglich erworben hat. Innerhalb derselben Transaktion können zuvor weitere Befehle gelaufen sein.

last_request_end_time ist außerdem nicht der Beginn der Transaktion. Für deren Alter brauchst du zusätzliche Transaktionsinformationen. Den Funktionsumfang des Input Buffers erläutert die Dokumentation zu sys.dm_exec_input_buffer.

Schritt 4: Alter und Zustand offener Transaktionen prüfen

Mit sys.dm_tran_session_transactions ordnest du Transaktionen den Sitzungen zu. sys.dm_tran_active_transactions ergänzt unter anderem Beginn, Typ und Zustand.

SELECT
    SYSDATETIME() AS capture_time,
    st.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    DATEDIFF_BIG(
        SECOND,
        at.transaction_begin_time,
        SYSDATETIME()
    ) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state,
    st.is_user_transaction,
    st.is_local,
    s.open_transaction_count AS session_open_transactions
FROM sys.dm_tran_session_transactions AS st
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = st.session_id
WHERE s.is_user_process = 1
  AND st.is_user_transaction = 1
  AND s.session_id <> @@SPID
ORDER BY
    at.transaction_begin_time,
    st.session_id;

Ein transaction_state von 2 bezeichnet eine aktive Transaktion. Der Wert 7 steht für eine laufende Rückabwicklung. Eine lange bestehende Transaktion verdient Aufmerksamkeit, ist aber nicht allein aufgrund ihres Alters fehlerhaft.

Die Ausgabe enthält eine Zeile pro zugeordneter Transaktion und Sitzung. Bei verteilten oder gebundenen Transaktionen können die Zuordnungen komplexer sein als „eine Sitzung, eine Transaktion“. Die Werte dokumentiert Microsoft unter sys.dm_tran_active_transactions und sys.dm_tran_session_transactions.

Warum bleiben Transaktionen offen?

Typische Prüfpunkte sind ein fehlendes COMMIT oder ROLLBACK, unvollständige Fehlerbehandlung und Anwendungsvorgänge, die innerhalb einer Transaktion auf Benutzereingaben oder externe Dienste warten.

Unterscheide außerdem zwei verschiedene Zeitlimits: Ein Client-Timeout beendet aus Sicht der Anwendung das Warten auf einen Befehl. SET LOCK_TIMEOUT begrenzt dagegen die Wartezeit beim Sperrerwerb. Aus einem Timeout darfst du nicht pauschal ableiten, dass die gesamte explizite Transaktion sauber beendet wurde. Prüfe deren Zustand und den Fehlerpfad der Anwendung.

Die Grundlagen zu Transaktionsgrenzen und Fehlerbehandlung findest du im Beitrag Arbeiten mit Transaktionen in MSSQL. Zur Unterscheidung von Verbindungs- und Befehlszeitlimits gibt es außerdem die Microsoft-Anleitung zu Query-Timeouts.

Testbeispiel: Eine Blockierung mit zwei Sitzungen nachvollziehen

Das Beispiel arbeitet ausschließlich mit einer eigens angelegten globalen temporären Tabelle in tempdb. Es verändert keine bestehenden Anwendungstabellen. Führe es auf einer Entwicklungs- oder Testinstanz aus.

Öffne zwei neue SSMS-Abfragefenster mit getrennten Verbindungen zur gleichen Instanz. Diese sind Sitzung A und Sitzung B. Ein zusätzliches Diagnosefenster kannst du ausschließlich für die vorherigen SELECT-Abfragen verwenden. Unterschiedliche Verbindungen erkennst du an unterschiedlichen Werten von @@SPID.

Die beiden Rautezeichen im Tabellennamen machen die temporäre Tabelle für beide Testsitzungen erreichbar. Verwende die Fenster nur für diesen Versuch und halte Sitzung A bis zum Aufräumen geöffnet.

1. Sitzung A: Testtabelle anlegen

Das Skript bricht ab, wenn bereits eine Transaktion offen ist oder der Tabellenname schon existiert. Eine vorhandene Tabelle wird nicht automatisch gelöscht.

USE tempdb;
SET NOCOUNT ON;

IF @@TRANCOUNT <> 0
BEGIN
    THROW 50001, N'Bitte eine neue Testsitzung ohne offene Transaktion verwenden.', 1;
END;

SET IMPLICIT_TRANSACTIONS OFF;

IF OBJECT_ID(N'tempdb..##ITFundus_BlockingDemo') IS NOT NULL
BEGIN
    THROW 50002, N'Die Testtabelle existiert bereits. Bestehenden Test zuerst klären.', 1;
END;

CREATE TABLE ##ITFundus_BlockingDemo
(
    Id int NOT NULL PRIMARY KEY,
    Wert int NOT NULL
);

INSERT INTO ##ITFundus_BlockingDemo (Id, Wert)
VALUES (1, 100);

SELECT @@SPID AS session_a;

2. Sitzung A: Eine Änderung offen lassen

Führe anschließend in derselben Sitzung diesen Block aus. Die Transaktion bleibt für den Versuch absichtlich offen.

IF @@TRANCOUNT <> 0
BEGIN
    THROW 50003, N'In dieser Sitzung ist bereits eine Transaktion offen.', 1;
END;

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE ##ITFundus_BlockingDemo WITH (TABLOCKX)
    SET Wert = Wert + 1
    WHERE Id = 1;

    SELECT
        @@SPID AS session_a,
        @@TRANCOUNT AS open_transactions;

    -- Absichtlich noch kein COMMIT und kein ROLLBACK.
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    THROW;
END CATCH;

Der Hint TABLOCKX fordert hier gezielt eine exklusive Tabellensperre an. Er macht den Konflikt auf der kleinen Testtabelle eindeutig nachvollziehbar und ist keine Empfehlung für produktive UPDATE-Anweisungen.

Nach Abschluss des Batches führt Sitzung A keinen Request mehr aus. Ihre Transaktion bleibt jedoch offen.

3. Sitzung B: Dieselbe Zeile ändern

Wechsle jetzt in das zweite neue Abfragefenster. Die Sperrwartezeit wird auf 60 Sekunden begrenzt. Sowohl nach erfolgreicher Ausführung als auch im Fehlerfall wird die Testtransaktion zurückgerollt.

USE tempdb;
SET NOCOUNT ON;

IF @@TRANCOUNT <> 0
BEGIN
    THROW 50004, N'Bitte eine neue Testsitzung ohne offene Transaktion verwenden.', 1;
END;

SET IMPLICIT_TRANSACTIONS OFF;
SET XACT_ABORT ON;
SET LOCK_TIMEOUT 60000;

SELECT @@SPID AS session_b;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE ##ITFundus_BlockingDemo
    SET Wert = Wert + 10
    WHERE Id = 1;

    ROLLBACK TRANSACTION;

    SELECT N'UPDATE ausgeführt und Teständerung zurückgerollt.' AS result;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    SELECT
        ERROR_NUMBER() AS error_number,
        ERROR_MESSAGE() AS error_message;
END CATCH;

SET LOCK_TIMEOUT -1;

Solange Sitzung A ihre Transaktion offen hält, muss Sitzung B auf die unvereinbare Sperre warten. Währenddessen kannst du im Diagnosefenster die Abfragen aus den vorherigen Abschnitten ausführen.

• Die Request-Abfrage sollte Sitzung B mit einem Sperrwartetyp und Sitzung A als Blockierer zeigen.
• Die Sitzungsabfrage kann Sitzung A als sleeping mit offener Transaktion anzeigen.
• Die Transaktionsabfrage zeigt den Beginn der offenen Transaktion von Sitzung A.

Die Diagnoseabfragen blenden jeweils ihre eigene Sitzung aus. Verwende deshalb für die vollständige Beobachtung das separate Diagnosefenster.

Nach Überschreiten der Sperrwartezeit erhält Sitzung B normalerweise Fehler 1222. Das ist ein Lock-Timeout und kein Deadlock. Der CATCH-Block räumt die Transaktion von B auf. Die Transaktion von A bleibt weiterhin offen.

Die Einstellung SET LOCK_TIMEOUT wirkt auf die jeweilige Verbindung und wird in Millisekunden angegeben. Im Beispiel wird sie anschließend auf den Standardwert -1 zurückgesetzt. Einzelheiten stehen in der Dokumentation zu SET LOCK_TIMEOUT.

4. Sitzung A: Blockierung auflösen

Führe in Sitzung A die Rückabwicklung aus:

IF XACT_STATE() <> 0
BEGIN
    ROLLBACK TRANSACTION;
END;

Wartet Sitzung B zu diesem Zeitpunkt noch, kann ihr UPDATE anschließend fortfahren. Danach rollt auch B seine Änderung zurück. Ist B bereits wegen des Zeitlimits beendet worden, kannst du den Block aus Sitzung B erneut ausführen. Ohne die Sperre von A sollte der Vorgang nun abschließen.

5. Ergebnis prüfen und aufräumen

Warte, bis der Testblock in Sitzung B vollständig beendet ist. Führe anschließend in Sitzung A diese Befehle aus:

IF XACT_STATE() <> 0
BEGIN
    ROLLBACK TRANSACTION;
END;

SELECT Id, Wert
FROM ##ITFundus_BlockingDemo;

DROP TABLE ##ITFundus_BlockingDemo;

Da beide Änderungen zurückgerollt wurden, sollte die Zeile wieder den ursprünglichen Wert 100 enthalten. Danach kannst du die Testfenster schließen. Für diesen Versuch ist kein KILL-Befehl notwendig.

Welche Gegenmaßnahmen passen zur Ursache?

Die Diagnose sollte erklären, warum Sperren zu lange bestehen bleiben. Eine kurzfristig wieder reagierende Anwendung reicht als Erfolgskriterium nicht aus, wenn derselbe Ablauf wenig später erneut blockiert.

Transaktionen kürzer halten

Prüfe, welche Schritte wirklich atomar zusammengehören. Vermeide Benutzerdialoge, HTTP-Aufrufe oder längere Dateiverarbeitung zwischen BEGIN TRANSACTION und COMMIT. Solche Wartezeiten verlängern den Transaktionskontext, obwohl SQL Server in dieser Zeit möglicherweise nichts ausführt.

Große Änderungen lassen sich gegebenenfalls in kleinere, jeweils abgeschlossene Pakete zerlegen. Das ist allerdings nur sinnvoll, wenn Teilfortschritte fachlich erlaubt sind. Eine vollständig atomare Operation darf nicht allein zur Verringerung von Sperrzeiten beliebig aufgeteilt werden.

Fehlerpfade und Verbindungsverwaltung korrigieren

Jeder relevante Fehlerpfad muss klären, wer die Transaktion beendet. Dazu gehören auch Abbrüche und Timeouts auf der Clientseite. Ein allgemeines „bei Fehler erneut versuchen“ kann die Situation verschärfen, wenn die vorherige Transaktion offen geblieben ist.

Eine passende TRY/CATCH-Behandlung mit Prüfung von XACT_STATE() unterstützt die Bereinigung. SET XACT_ABORT ON kann bei vielen Laufzeitfehlern ebenfalls helfen, ersetzt aber keine durchdachte Transaktionsverwaltung. Microsoft erläutert die Wirkung und Ausnahmen unter SET XACT_ABORT.

Berücksichtige bei wiederverwendbaren Prozeduren außerdem, ob sie eine eigene Transaktion beginnen oder innerhalb einer bereits vorhandenen Transaktion aufgerufen werden.

Abfragen und Datenzugriffe gezielt optimieren

Wenn der Blockierer tatsächlich lange arbeitet, untersuche seinen Ausführungsplan, die Filterbedingungen und die beteiligten Indizes. Unnötig umfangreiche Datenzugriffe können die Laufzeit und den Umfang der Sperren vergrößern. Auch veraltete Statistiken oder ungünstige Pläne können eine Rolle spielen.

Leite Änderungen aus dem konkreten Zugriffsmuster ab. Ein zusätzlicher Index kann eine Abfrage beschleunigen, verursacht aber selbst Pflegeaufwand bei Schreiboperationen.

Isolation und Zeilenversionierung prüfen

READ_COMMITTED_SNAPSHOT kann Konflikte zwischen üblichen Lese- und Schreibzugriffen reduzieren, weil Leser auf passende Zeilenversionen zugreifen. Es beseitigt jedoch weder alle Sperren noch sämtliche Konflikte zwischen Schreibern.

Eine Umstellung verändert das Nebenläufigkeitsverhalten und benötigt Kapazität für die Versionierung. Prüfe deshalb Anwendungskompatibilität und Betriebsfolgen vor einer Änderung. Die technischen Grundlagen stehen im Leitfaden zu Sperren und Zeilenversionierung.

NOLOCK nicht als pauschale Lösung einsetzen

NOLOCK erlaubt unter anderem das Lesen noch nicht bestätigter Änderungen. Dadurch können Ergebnisse fachlich falsch oder inkonsistent sein. Außerdem verhindert der Hint nicht jede Blockierung: Auch solche Abfragen benötigen beispielsweise Schemasperren.

Wenn eine Auswertung korrekte Bestände, Buchungen oder Statuswerte liefern muss, ist das eine wesentliche Einschränkung. Die Auswirkungen beschreibt Microsoft in der Dokumentation zu Tabellenhinweisen.

Was passiert bei KILL?

KILL beendet eine Sitzung. Offene Transaktionen werden zurückgerollt; bereits bestätigte Änderungen bleiben bestehen. Die Anwendung verliert ihre Verbindung und muss den betroffenen Vorgang entsprechend behandeln.

Prüfe unmittelbar vorher Sitzungsnummer, login_time, Anwendung und Transaktion erneut. Die Nummer 62 ist hier nur ein Beispiel. Die Befehle sind deshalb auskommentiert:

-- Beendet die zuvor eindeutig identifizierte Sitzung:
-- KILL 62;

-- Meldet den Fortschritt einer durch KILL gestarteten Rückabwicklung:
-- KILL 62 WITH STATUSONLY;

Die Rückabwicklung kann Zeit benötigen. Sperren können währenddessen weiterbestehen. Ein KILL garantiert deshalb keine sofortige Entlastung.

WITH STATUSONLY startet keine neue Rückabwicklung, sondern meldet deren Fortschritt und eine geschätzte Restdauer. Wiederhole zur Kontrolle keinen normalen KILL: Eine freigewordene Sitzungsnummer kann inzwischen neu vergeben worden sein.

Für KILL benötigt SQL Server ALTER ANY CONNECTION. Diagnoseberechtigungen allein reichen nicht. Bevor du eingreifst, kläre mit dem Verantwortlichen, ob der Vorgang regulär abgeschlossen oder bewusst zurückgerollt werden soll. Ein COMMIT im Diagnosefenster beendet keine fremde lokale Transaktion.

Syntax, Berechtigungen und Statusabfrage beschreibt die Microsoft-Dokumentation zu KILL.

Wiederkehrende Blockierungen über längere Zeit erfassen

Ist die Blockierung bei Beginn der Untersuchung bereits verschwunden, können aktuelle DMV-Abfragen den vergangenen Ablauf nicht vollständig rekonstruieren. Sichere bei wiederkehrenden Vorfällen deshalb die Ergebnisse mit Erfassungszeitpunkt, Sitzungskennungen, Wartearten und SQL-Texten.

Für eine längerfristige Erfassung eignen sich außerdem Extended Events mit dem Ereignis sqlserver.blocked_process_report. Dafür muss die Instanz entsprechend konfiguriert sein: Die Serveroption blocked process threshold ist standardmäßig deaktiviert.

Der Schwellwert legt fest, ab welcher Wartezeit Berichte erzeugt werden sollen. Er beendet keine Abfragen und löst keine Sperren auf. Die Erfassung erfolgt zudem nicht mit garantierter Echtzeitgenauigkeit. Auswahl des Schwellwerts, Ereignissitzung und Aufbewahrung gehören deshalb in eine geplante Überwachung. Details enthält die Dokumentation zur Option blocked process threshold.

Deadlock-Berichte sind davon zu unterscheiden. Ein xml_deadlock_report dokumentiert einen erkannten Deadlock und ersetzt keine Aufzeichnung gewöhnlicher, länger anhaltender Blockierungen.

Checkliste für die nächste Blockierungsanalyse

1. Betroffenen Anwendungsvorgang und Zeitpunkt festhalten.
2. Prüfen, ob ein Sperrwarteereignis oder eine andere Ursache für die lange Laufzeit vorliegt.
3. Wartende Requests und ihre positiven blocking_session_id-Verweise verfolgen.
4. Auch untätige Sitzungen mit offenen Transaktionen untersuchen.
5. Transaktionsbeginn und zuletzt übermittelten Batch mit dem Anwendungsvorgang abgleichen.
6. Eine passende kurzfristige Maßnahme abstimmen und deren Wirkung beobachten.
7. Transaktionsführung, Fehlerbehandlung oder Datenzugriff so korrigieren, dass die Ursache behoben wird.

Werbung / Affiliate-Links: Die mit einem Sternchen (*) gekennzeichneten Links sind Amazon-Partnerlinks. Wenn du darüber etwas kaufst, erhalte ich möglicherweise eine Provision. Für dich entstehen dadurch keine zusätzlichen Kosten. Als Amazon-Partner verdiene ich an qualifizierten Verkäufen.

Weiterführende Informationen

• IT-Fundus: Arbeiten mit Transaktionen in MSSQL
• IT-Fundus: SQL Server Deadlocks erkennen und beheben
• Microsoft Learn: Blockierungsprobleme in SQL Server verstehen und beheben
• Microsoft Learn: Wartearten und sys.dm_os_wait_stats
• Microsoft Learn: Leitfaden zu Deadlocks