Wer wartet auf wen, und warum überhaupt?
Der SQL Server sagt dir jederzeit, welche Sitzung eine andere aufhält. Die Kunst besteht darin, vom Wartenden zum Verursacher zu kommen und dort die richtige Frage zu stellen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Anwendung meldet eine Zeitüberschreitung, der Server meldet gar nichts
Der Ablauf ist immer ähnlich. In der Fachabteilung dreht sich seit zwei Minuten ein Ladesymbol, danach erscheint eine Meldung über eine abgelaufene Zeitüberschreitung. Bis jemand nachsieht, ist die Sitzung längst beendet und mit ihr die Sperre. Im Monitor steht nichts Auffälliges, die Prozessorlast ist niedrig, die Platten langweilen sich. Genau das führt zu der falschen Antwort, der Server sei nicht ausgelastet, es müsse also am Netz oder an der Anwendung liegen.
Der Denkfehler steckt in der Annahme, ein wartender Prozess brauche Ressourcen. Eine Sitzung, die auf eine Sperre wartet, verbraucht weder Prozessorzeit noch Ein- und Ausgabe. Ein Server, auf dem hundert Sitzungen hintereinander in einer Kette warten, sieht in jeder Auslastungsanzeige aus wie ein Server im Leerlauf. Wer nur auf Auslastung schaut, sieht die häufigste Ursache für hängende Anwendungen grundsätzlich nicht.
Teuer wird das durch die Reihenfolge, in der reagiert wird. Nach der dritten Beschwerde wird der Server neu gestartet, damit wieder Ruhe ist. Das beendet zwar die Kette, verwirft aber genau die Informationen, mit denen sich der Verursacher hätte bestimmen lassen, und am nächsten Vormittag wiederholt sich alles. Solange nicht festgehalten wird, welche Sitzung die Kette angeführt und welche Anweisung sie ausgeführt hat, bleibt jede Maßnahme geraten.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Fünf Bilder, die im Alltag auftauchen, und was dahintersteckt
Beschrieben wird jeweils das, was jemand tatsächlich meldet. Die Ursache liegt regelmäßig woanders als in der Sperre selbst, deshalb steht sie hier neben dem Symptom.
Symptom
Eine einzelne Maske hängt minutenlang, während alle anderen im Haus normal weiterarbeiten.
Ursache
Am Anfang der Kette steht eine Sitzung im Zustand sleeping mit einer offenen Transaktion. Jemand hat ein Formular geöffnet, die Anwendung hat eine Transaktion begonnen und wartet nun darauf, dass der Mensch auf Speichern klickt. Häufig steckt dahinter auch die Einstellung für implizite Transaktionen im Datenbanktreiber, bei der jede Anweisung eine Transaktion eröffnet, die niemand wieder schließt.
Lösung
Prüf mit sys.dm_tran_session_transactions und DBCC OPENTRAN, seit wann die Transaktion offen ist, und lies mit sys.dm_exec_connections die Anwendung und den Rechnernamen aus. Die Antwort liegt im Programmcode, nicht in der Datenbank: Transaktionen dürfen keine Benutzereingabe umschließen. Bis zur Korrektur hilft ein kurzer Wert für LOCK_TIMEOUT in der Anwendung, damit die wartende Seite mit Fehler 1222 abbricht, statt endlos zu hängen.
Symptom
Jeden Vormittag zwischen acht und neun steht die Anwendung, nachmittags läuft alles flüssig.
Ursache
Eine Auswertung liest zu dieser Zeit eine große Tabelle. Unter der voreingestellten Isolationsstufe Read Committed nimmt sie dabei gemeinsame Sperren, und weil ein passender Index fehlt, geht sie die gesamte Tabelle durch statt einige Zeilen. Die schreibenden Sitzungen aus der Anwendung stellen sich hinter diesen Scan.
Lösung
Nicht die Sperre ist das Problem, sondern die Laufzeit. Hol den Ausführungsplan der Abfrage und sieh nach, ob dort ein Scan über die ganze Tabelle steht. Ein passender Index verkürzt die Laufzeit und damit die Sperrdauer um Größenordnungen. Wenn die Auswertung ohnehin regelmäßig läuft, gehört sie mittelfristig auf eine lesbare zweite Kopie oder in eine eigene Auswertungsschicht.
Symptom
Kleine Änderungen laufen durch, aber sobald ein Lauf mehrere tausend Zeilen anfasst, steht die ganze Tabelle.
Ursache
Der SQL Server hebt Sperren auf eine höhere Ebene, wenn eine einzelne Anweisung sehr viele Zeilensperren auf demselben Objekt hält. Aus vielen kleinen Sperren wird dann eine Sperre auf die gesamte Tabelle, und ab diesem Moment kommt niemand mehr an eine einzige Zeile heran, auch nicht an eine völlig unbeteiligte.
Lösung
Zerleg den Vorgang in Blöcke mit einem COMMIT dazwischen. Die Eskalation setzt ein, sobald eine Anweisung ungefähr 5.000 Sperren auf demselben Objekt hält, Blöcke von wenigen hundert bis tausend Zeilen bleiben also sicher darunter. Wenn das nicht möglich ist, lässt sich das Verhalten je Tabelle mit ALTER TABLE und der Option LOCK_ESCALATION steuern, bei partitionierten Tabellen auch auf die Ebene der Partition begrenzen. Das Abschalten ist die letzte Wahl, denn viele einzelne Sperren kosten Arbeitsspeicher.
Symptom
Benutzer melden Hänger, doch sobald jemand nachsieht, ist alles frei und nichts mehr zu erkennen.
Ursache
Die Anwendung bricht nach ihrer eigenen Zeitüberschreitung ab, meist nach dreißig Sekunden, und gibt damit auch die Sperren frei. Die Beobachtung im Management Studio kommt schlicht zu spät. Eine Abfrage auf die laufenden Anforderungen zeigt immer nur den aktuellen Augenblick.
Lösung
Setz den Schwellenwert für blockierte Prozesse mit sp_configure auf einen Wert wie fünf Sekunden und richte eine Extended-Events-Sitzung auf das Ereignis blocked_process_report ein. Der Server erzeugt dann von selbst einen Bericht mit der vollständigen Kette und dem Text beider Anweisungen. Beachte, dass die Prüfung im Takt des Überwachungsprozesses läuft, sehr kurze Blockierungen tauchen also nicht auf, und genau das ist gewollt.
Symptom
Der Wartetyp der hängenden Sitzung beginnt gar nicht mit LCK, sondern lautet ASYNC_NETWORK_IO.
Ursache
Hier blockiert niemand. Die Abfrage ist fertig, aber der Client holt das Ergebnis nicht schnell genug ab, etwa weil eine Anwendung die Zeilen einzeln durchgeht und dabei rechnet, oder weil jemand eine Auswertung mit mehreren Millionen Zeilen an ein Tabellenwerkzeug schickt. Solange der Client liest, bleibt die Transaktion offen und hält ihre Sperren.
Lösung
Reduzier die Ergebnismenge, statt die Datenbank zu untersuchen. Filter und Aggregation gehören in die Abfrage, nicht in die Anwendung. Wenn ein Programm Zeile für Zeile verarbeitet, lohnt sich der Umbau auf mengenbasierte Verarbeitung, und der Bericht mit Millionen Zeilen gehört auf eine Kopie, nicht auf die Instanz, an der gearbeitet wird.
Fünf Schritte vom Symptom zum Verursacher
- 01 Frag sys.dm_exec_requests ab und sammle alle Sitzungen mit einer Blockierungsnummer.
- 02 Folge der Kette rückwärts, bis eine Sitzung ohne eigene Wartezeit übrig bleibt.
- 03 Hol dir zu dieser Sitzung den Text der laufenden oder zuletzt gesendeten Anweisung.
- 04 Sieh im Wartetyp nach, ob es um eine Sperre, um Ein- und Ausgabe oder um den Client geht.
- 05 Entscheide danach zwischen Index, Abfrage, Anwendungsfehler und Isolationsstufe.
Was du danach in wenigen Minuten beantwortest
Eine Blockierung zu analysieren heißt, vier Fragen der Reihe nach zu beantworten: Wer wartet, worauf wartet er, wer hält es fest und warum so lange. Für jede dieser Fragen gibt es genau eine Quelle im Server, und keine davon braucht ein zusätzliches Werkzeug.
Die Kette bis zum Anfang verfolgen
Du liest blocking_session_id aus sys.dm_exec_requests und gehst so lange rückwärts, bis eine Sitzung übrig bleibt, die selbst auf nichts wartet. Nur diese eine ist die Ursache, alle anderen sind Folgeerscheinungen.
Die Anweisung dazu holen
Über sys.dm_exec_sql_text mit dem sql_handle oder über DBCC INPUTBUFFER siehst du, was die verursachende Sitzung ausführt oder zuletzt ausgeführt hat. Ohne diesen Schritt kennst du eine Nummer, aber keinen Grund.
Wartetypen einordnen
Du unterscheidest Sperrwartezeiten mit dem Präfix LCK von Wartezeiten auf Ein- und Ausgabe oder auf den Client. Ein Wartetyp wie ASYNC_NETWORK_IO sieht für Benutzer aus wie eine Blockierung, hat aber eine völlig andere Ursache.
Offene Transaktionen aufspüren
Du erkennst an einer schlafenden Sitzung mit offener Transaktion, dass eine Anwendung ein COMMIT schuldig geblieben ist, und prüfst mit sys.dm_tran_session_transactions, seit wann das so ist.
Den fehlenden Index als Ursache erkennen
Du prüfst den Ausführungsplan der verursachenden Abfrage auf einen Scan über die gesamte Tabelle. Eine Anweisung, die zehn Sekunden statt zehn Millisekunden braucht, hält ihre Sperren tausendfach länger.
Den Fall festhalten, auch wenn er vorbei ist
Du setzt den Schwellenwert für den Bericht über blockierte Prozesse und fängst ihn mit einer Extended-Events-Sitzung ab. Damit hast du beim nächsten Mal die Kette samt Anweisungen im Protokoll, statt live danebenstehen zu müssen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Kette rückwärts aufrollen
Der Einstieg ist eine einzige Sicht: SELECT session_id, blocking_session_id, wait_type, wait_time, command FROM sys.dm_exec_requests WHERE blocking_session_id <> 0. Jede Zeile darin nennt eine wartende Sitzung und die Nummer derjenigen, die sie aufhält. Diese Nummer schlägst du erneut nach, und so weiter, bis du bei einer Sitzung ankommst, die in dieser Liste nicht mehr auftaucht. Das ist der Kopf der Kette, und nur dort lohnt sich die weitere Suche.
Der häufigste Fehler an dieser Stelle ist, sich die Sitzung mit der längsten Wartezeit vorzunehmen. Die wartet meist nur deshalb am längsten, weil sie zuerst angekommen ist. Ebenso wenig hilft es, eine mittlere Sitzung in der Kette zu beenden, denn die nachfolgenden warten anschließend auf dieselbe Ressource weiter. Wer beenden muss, beendet den Kopf, und auch das ist eine Notmaßnahme: Ein KILL löst ein Zurückrollen aus, das bei einer langen Transaktion deutlich länger dauern kann als das Ausführen selbst.
Der Kopf der Kette ist oft eine Sitzung im Zustand sleeping, die überhaupt keine Anforderung ausführt. In sys.dm_exec_requests taucht eine solche Sitzung gar nicht auf, sondern nur in sys.dm_exec_sessions. Ihr Merkmal ist open_transaction_count über null: eine offene Transaktion ohne laufende Arbeit. Den Text der zuletzt gesendeten Anweisung holst du dir aus sys.dm_exec_connections, allerdings steht dort nur most_recent_sql_handle, den du mit CROSS APPLY sys.dm_exec_sql_text in Klartext verwandelst. Den Namen der Anwendung und des Rechners, von dem sie kommt, liefert sys.dm_exec_sessions. Damit hast du die Antwort auf die Frage, wen du anrufen musst.
Was die Wartetypen erzählen
Sperrwartezeiten beginnen mit LCK_M_ und nennen die angeforderte Sperrart. LCK_M_S steht für eine gemeinsame Sperre, also einen Lesevorgang, der auf einen Schreibvorgang wartet. LCK_M_X steht für eine ausschließliche Sperre, üblicherweise ein Schreibvorgang, der auf einen anderen wartet. LCK_M_U tritt bei Änderungen auf, während der Server prüft, welche Zeilen betroffen sind. LCK_M_SCH_M ist eine Sperre auf das Schema und deutet auf einen Vorgang hin, der die Struktur verändert, etwa ein Index-Neuaufbau oder ein ALTER TABLE. Diese Sperre ist besonders unangenehm, weil sie sich mit allem anderen beißt, auch mit reinen Lesevorgängen.
Wartetypen ohne diesen Vorsatz haben mit Sperren nichts zu tun, sehen für Benutzer aber genauso aus. PAGEIOLATCH_SH bedeutet, dass Daten von der Platte geholt werden, ein Hinweis auf zu wenig Arbeitsspeicher oder zu langsame Datenträger. WRITELOG bedeutet, dass Bestätigungen von Transaktionen auf das Protokoll warten. ASYNC_NETWORK_IO bedeutet, dass der Client die Ergebnisse nicht abholt. Und RESOURCE_SEMAPHORE bedeutet, dass eine Abfrage auf Arbeitsspeicher für ihre Sortierung wartet, was auf einen unpassenden Plan oder eine viel zu große Zwischenmenge hindeutet.
Für den Blick über den Augenblick hinaus gibt es sys.dm_os_wait_stats, eine Summe seit dem letzten Neustart. Diese Sicht beantwortet die Frage, worauf die Instanz insgesamt am meisten wartet, und ordnet damit ein, ob die beobachtete Blockierung ein Einzelfall oder das Grundmuster ist. Wichtig ist der Rahmen: Einige Wartetypen sind im Leerlauf normal und stehen deshalb in jeder Auswertung ganz oben, ohne etwas auszusagen. Zurücksetzen lässt sich die Statistik mit DBCC SQLPERF, was vor einer gezielten Messung sinnvoll ist.
Warum der fehlende Index häufiger schuld ist als die Sperre
Eine Sperre dauert genau so lange, wie die Anweisung braucht, die sie hält. Damit ist jede Frage nach Blockierungen zuerst eine Frage nach Laufzeiten. Eine Aktualisierung, die über einen passenden Index genau die betroffenen Zeilen findet, hält ihre Sperre für Millisekunden. Dieselbe Aktualisierung ohne Index geht die gesamte Tabelle durch, sperrt dabei deutlich mehr Zeilen als nötig und hält sie sekundenlang. Die Sperre verhält sich in beiden Fällen korrekt, nur der Zeitraum unterscheidet sich um den Faktor tausend.
Der zweite Zusammenhang ist weniger bekannt: Ohne Index auf der Bedingung sperrt der Server nicht nur länger, sondern auch mehr. Er muss jede Zeile lesen, um zu entscheiden, ob sie betroffen ist, und nimmt dabei auf jede gelesene Zeile eine Aktualisierungssperre. Unter Read Committed gibt er die auf nicht betroffenen Zeilen zwar gleich wieder frei, aber währenddessen wandert die Sperre über die ganze Tabelle, und andere Sitzungen bleiben der Reihe nach daran hängen. Ein UPDATE, das fachlich drei Zeilen betrifft, arbeitet sich so durch eine halbe Million, und weil die Zahl gleichzeitig gehaltener Sperren dabei hoch werden kann, kommt die Eskalation auf die Tabellenebene noch dazu.
Praktisch heißt das: Bevor du an Isolationsstufen drehst, sieh dir den Ausführungsplan der verursachenden Anweisung an. Der Query Store hält dafür die Pläne und Laufzeiten vor und beantwortet auch die Frage, ob die Abfrage schon immer so lange lief oder erst seit einer Woche. Ein Plan, der sich geändert hat, ist ein anderer Fall als eine Abfrage, die seit Jahren zu langsam ist und nur bisher niemandem im Weg stand.
Read Committed Snapshot: was sich ändert und was es kostet
Mit ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON stellst du die Standardisolationsstufe der Datenbank auf Versionsverwaltung um. Lesende Abfragen nehmen danach keine gemeinsamen Sperren mehr, sondern bekommen die Fassung der Zeile, die beim Start der Anweisung bestätigt war. Damit verschwindet die häufigste Blockierung überhaupt, nämlich Lesen wartet auf Schreiben, und zwar ohne dass am Programmcode etwas geändert werden muss. Genau das macht die Einstellung so verlockend.
Der Preis steht in tempdb. Jede geänderte Zeile wird dort in ihrer bisherigen Fassung abgelegt, solange noch eine Abfrage sie sehen könnte. Diese Ablage wächst mit der Änderungsrate und mit der Laufzeit der längsten laufenden Abfrage. Eine Auswertung, die eine Stunde läuft, hält den gesamten Verlauf dieser Stunde in tempdb fest. Dazu kommen vierzehn Byte je Zeile in den Datenseiten für den Verweis auf die frühere Fassung, was einmalig zu Seitenteilungen und damit zu Fragmentierung führt. tempdb gehört danach auf schnelle Datenträger und mit ausreichend Platz aufgesetzt.
Zwei Dinge sind beim Umschalten zu beachten. Erstens braucht die Umstellung kurz den alleinigen Zugriff auf die Datenbank, es darf also keine andere Verbindung offen sein, weshalb sie in ein Wartungsfenster gehört. Zweitens ändert sich das Verhalten der Anwendung: Wo bisher gewartet wurde, bis eine Änderung fertig war, wird jetzt der alte Wert gelesen. Für die meisten Anwendungen ist das genau richtig, für einen Ablauf, der auf das Warten baut, etwa eine selbstgebaute Warteschlange oder eine Nummernvergabe, ist es ein Verhaltenswechsel, der geprüft werden muss.
Dazu passende Kurse
Weil die Antwort mal in der Abfrage und mal im Betrieb liegt, decken die SQL-Server-2016-Kurse für Entwicklung und Administration beide Seiten ab.
Wer das Zusammenspiel aus Transaktion, Isolationsstufe und Sperre systemübergreifend verstehen will, findet dafür Kurse zu Transaktionen und Sperrverhalten in Datenbanken .
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Ist eine Blockierung dasselbe wie ein Deadlock?
Hilft NOLOCK gegen Blockierungen?
Kann ich sehen, welche Sperren eine Sitzung gerade hält?
Wie finde ich am nächsten Tag heraus, was nachts blockiert hat?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Wartende Sitzungen lesen, statt den Server neu zu starten
Bei cmt erzeugst du Blockierungen auf einer Testinstanz selbst und verfolgst sie mit den Bordmitteln bis zur Anweisung, die sie ausgelöst hat.