Von Tony Davis
Ein Anruf vom Helpdesk: Die Berichtsanwendung hängt. Genervte User melden, dass ihre Bildschirme „eingefroren“ sind. Die nicht vorhandene Klimaanlage kann's jedoch nicht gewesen sein. Einige sehen sogar Timeout-Meldungen mit SQL-Bezug. Steckt hier Blocking dahinter?
Jetzt sind Sie als DBA gefragt. Mit den richtigen System-Views und Tools können Sie schnell prüfen: Welche Prozesse laufen gerade ins Leere? Welche Session blockiert die anderen? Welches SQL-Statement steckt dahinter? Und welche Locks sind beteiligt? Im besten Fall sorgt eine Monitoring-Lösung dafür, dass Sie schon gewarnt werden, bevor der Helpdesk überhaupt durchklingelt.
Eine Datenbank ist eine gemeinsam genutzte Umgebung. Viele User-Transaktionen greifen gleichzeitig auf dieselben Ressourcen wie Tabellen und Indizes zu.
Damit das reibungslos funktioniert, vermittelt SQL Server den Zugriff auf diese Ressourcen, indem er verschiedene Arten von Locks (Sperren) vergibt. Eine Blockade entsteht, wenn eine Session eine Sperre für eine Ressource anfordert (z. B. eine Zeile, Page oder Tabelle), SQL Server diese Sperre aber nicht gewähren kann, weil eine andere Session bereits eine inkompatible Sperre auf der Ressource hält.
Nehmen wir an, eine Transaktion ändert Zeilen in einer Tabelle (mit der Standard-Isolationsstufe `READ COMMITTED`). SQL Server vergibt Exclusive Locks (X) für diese Zeilen. Dieser Lock-Modus ist nicht mit anderen Sperrmodi kompatibel. Eine zweite Transaktion, die dieselben Zeilen lesen will (was einen Shared Lock (S) erfordert), wird blockiert, bis die erste Transaktion ihren Commit oder Rollback durchführt.
Das Locken und die daraus resultierende Blockade sind in der Regel kurzlebig, völlig normal und sogar ein gewünschtes Feature einer Datenbank. Es stellt zum Beispiel sicher, dass eine Transaktion keine Daten liest, die sich gerade im Schreibvorgang befinden (es verhindert „Dirty Reads“), und dass nicht zwei Transaktionen gleichzeitig dieselbe Datenzeile ändern, was zu Datenkorruption führen könnte.
Wenn eine Blockade jedoch über einen längeren Zeitraum besteht, kann sie die Performance vieler User-Prozesse massiv beeinträchtigen.
Wenn wir auf ein potenzielles Blockierungsproblem aufmerksam gemacht werden, während es noch auftritt, stellt SQL Server uns zahlreiche Informationen zur Verfügung, die bei der Untersuchung und Behebung helfen.
Beginnen Sie mit den dynamischen Verwaltungssichten (DMV) `sys.dm_os_waiting_tasks`. Wenn eine Anfrage aktiv ist, aber auf eine Ressource wartet, um fortzufahren, wird sie in dieser View angezeigt. Die View liefert Informationen über die Art des Wartens, auf welche Ressource die Anfrage wartet und vieles mehr. Wenn Blocking die Ursache des Problems ist, erwarten wir hier, dass auf Locks für Zeilen, Pages, Tabellen usw. gewartet wird.
Es gibt mehrere Möglichkeiten, diese DMV abzufragen und mit anderen DMVs zu kombinieren, um mehr über blockierende und blockierte Sessions sowie die ausgeführten Queries herauszufinden. Für mein Beispiel habe ich die Abfrage aus dem kostenlosen PDF-Download SQL Server Performance Tuning using Wait Statistics: A Beginner's Guide genutzt.
Abbildung 1 zeigt eine Beispielausgabe (zur besseren Lesbarkeit auf zwei Bildschirme aufgeteilt).
Abbildung 1 - Überprüfung blockierter Sessions mit der DMV `sys.dm_os_waiting_tasks`
Der Wait Type für Sperren hat das Format `LCK_M_lock type`. Beispielsweise bedeutet `LCK_M_SCH_M` ein Warten auf eine `SCH_M` (Schema Modification) Sperre. Abbildung 1 zeigt eine Sperrkette mit drei Sessions (IDs 67, 69 und 71). An der Spitze der Kette steht Session 67, die Session 69 blockiert, welche wiederum Session 71 blockiert.
Session 67 ist die einzige, die nicht auf eine Sperre wartet. Sie führt eine Transaktion auf der Tabelle `SalesOrderHeader` aus. Typischerweise handelt es sich dabei um eine Transaktion, die aus irgendeinem Grund lange für Datenänderungen benötigt oder bei der ein unerwarteter Fehler auftrat, der zum Abbruch des Batches, aber nicht der Transaktion (sie blieb also 'offen' bzw. 'uncommited') führte.
Die von Session 67 gehaltenen Sperren hindern Session 69 daran, die notwendige Sperre zu erhalten, um den geclusterten Index auf `SalesOrderHeader` zu ändern (in diesem Fall einen Online-Index-Rebuild durchzuführen). Am Ende der Kette wartet Session 71 blockiert darauf, dass Session 69 ihre Arbeit beendet, um einen Shared Read Lock auf der `SalesOrderHeader`-Tabelle zu erhalten, um die benötigten Zeilen zu lesen.
DBAs und Entwickler diagnostizieren schwere Locking-Fälle, bei denen mehrere Sessions an verschiedenen Stellen einer langen Blockierungskette blockiert sind, manchmal fälschlicherweise als Deadlocks - vor allem, weil es so aussehen kann, als sei die gesamte Datenbank eingefroren.
Der große Unterschied: Ein Deadlock löst einen spezifischen Fehler aus und eine der Transaktionen wird zurückgesetzt. Bei einem Locking-Problem wird kein Fehler ausgelöst. Die Session an der Spitze einer Blockierungskette wartet nicht auf eine Sperre. Sie kann auf einen Latch, eine Speicherzuweisung oder IO warten. Die Blockierungskette löst sich auf, sobald die Ressource verfügbar ist und der Hauptblocker seine Arbeit beenden kann.
Wenn wir ein Blocking-Problem untersuchen, lohnt sich oft ein Blick in weitere Systemansichten. So können wir zum Beispiel mit sys.dm_tran_locks herausfinden, welche Sperren eine bestimmte Session gerade hält - und auf welche Ressourcen sie dabei zugreift. Gleichzeitig sehen wir auch, auf welche Sperren die Session noch wartet. Dazu geben wir einfach die zuvor ermittelten Session-IDs an. Auch andere DMVs helfen uns, das Bild zu vervollständigen: sys.dm_tran_active_transactions listet alle aktuell laufenden Transaktionen auf. sys.dm_exec_sessions zeigt uns, wem die blockierenden Sessions eigentlich gehören. Auf diese Weise lässt sich Schritt für Schritt nachvollziehen, wo genau die Blockade entsteht und wer beteiligt ist.
Wenn diese DMVs nur den aktuellen Stand zeigen, wie können wir dann im Nachhinein herausfinden, warum es zu „eingefrorenen Bildschirmen“ kam? Und wie lassen sich unsere SQL Server so überwachen, dass wir erkennen, ob starke Blockierungen generell für Performance-Probleme sorgen?
Wenn wir vermuten, dass Blocking in einer bestimmten Datenbank immer wieder Probleme verursacht, aktuell aber gerade nicht auftritt, gibt es verschiedene Tools, mit denen wir nachforschen können. Die `sys.dm_os_wait_stats`-DMV zeigt uns zum Beispiel, ob es auffällig hohe kumulierte Wartezeiten im Zusammenhang mit Sperren gibt.
Abbildung 2 - Kumulierte Wartezeiten pro Wait Type, dargestellt durch die DMV `sys.dm_os_wait_stats`
Außerdem können wir die DMV `sys.dm_db_index_operational_stats` nutzen, um Indizes zu identifizieren, die echte „Locking-Hotspots“ sind - wie es Jason Strate anschaulich erklärt.
Abbildung 3 - Indizes mit hohen Wartezeiten für Sperren, wie von der DMV `sys.dm_db_index_operational_stats` ermittelt
Diese gesammelten Wait- und Indexstatistiken sind hilfreich, um Auffälligkeiten zu entdecken. Für sich allein genommen reichen sie jedoch nicht aus, um die eigentliche Ursache klar zu bestimmen.
Ein weiteres hilfreiches Werkzeug ist der Performance Monitor (PerfMon). Damit können wir über verschiedene Counter Locking- und Blocking-Situationen im Blick behalten. Das Objekt `SQLServer:General Statistics` zeigt zum Beispiel, wie viele Prozesse blockiert sind. Mit `SQLServer:Locks` lassen sich Werte wie `Avg Wait Time (ms)` oder `Lock Waits/sec` ablesen. Diese Kennzahlen sind allerdings eher ein Hinweis darauf, dass etwas nicht stimmt, sie beweisen das Problem aber nicht eindeutig.
Um die wirkliche Ursache zu finden, reicht das nicht. Dafür brauchen wir eine Monitoring-Lösung, die uns nicht nur bei kritischen Blockierungen warnt, sondern auch genug Details liefert, um die Probleme im nächsten Schritt zu analysieren und gezielt zu beheben.
Wenn DMV-Daten und PerfMon-Counter auffälliges Locking oder Blocking vermuten lassen, lohnt sich ein genauerer Blick. Hier helfen ein SQL Trace oder - noch besser - eine Extended-Events-Session. Damit lassen sich Blockierungen erfassen, die einen bestimmten Schwellenwert überschreiten. In solchen Fällen erzeugt SQL Server automatisch einen Blocked Process Report.
Standardmäßig ist der `blocked process threshold` auf Null gesetzt. Das bedeutet: SQL Server erstellt keine Reports zu blockierten Prozessen. Um das zu ändern, müssen wir den Wert über `sp_configure` auf die gewünschte Anzahl Sekunden einstellen. Jeremiah Peschka erklärt in seinem Artikel Schritt für Schritt, wie man den Schwellenwert anpasst und ein Extended Event für die Report-Erfassung einrichtet.
Listing 1 zeigt einen gekürzten Report. Darin wird deutlich, dass ein Online-Index-Rebuild (Session 69) von einer unbestätigten Transaktion auf `SalesOrderHeader` (Session 67) blockiert wurde.
<blocked-process-report monitorLoop="694950"><blocked-process><process id="process48d496108" taskpriority="-10" logused="168" waitresource="OBJECT: 19:1266103551:0 " waittime="116153" ownerId="136795154" transactionname="ALTER INDEX" lasttranstarted="2016-05-10T18:01:13.367" XDES="0x4753fb030" lockMode="Sch-M"…output truncated…"><executionStack><frame line="1" stmtend="194" sqlhandle="0x01001300cb55c82e40623db60400000000000000000000000000000000000000000000000000000000000000" /></executionStack><inputbuf>ALTER INDEX PK_SalesOrderHeader_SalesOrderID ON Sales.SalesOrderHeader REBUILD WITH (ONLINE = ON);</inputbuf></process></blocked-process><blocking-process><process status="sleeping" spid="67" sbid="0" ecid="0" priority="0" trancount="1"…output truncated…"><executionStack /><inputbuf>BEGIN TRANSACTION SELECT FirstName , LastName , SUM(soh.TotalDue) AS TotalDue , MAX(OrderDate) AS LastOrder FROM Sales.SalesOrderHeader AS soh WITH (REPEATABLEREAD) INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID WHERE soh.OrderDate >= '2011/01/01' GROUP BY c.CustomerID , FirstName , LastName ;</inputbuf/></process/></blocking-process/></blocked-process-report/>
Listing 1 - Auszug aus einem Report über blockierte Prozesse mit drei beteiligten Sessions
Der Blocked Process Report liefert zwar die Infos, die wir zur Analyse von Blockierungen brauchen, ist aber oft schwer lesbar. Zusätzlich können sehr viele Reports entstehen, da eine einzige Blockierungskette mehrere Meldungen auslöst, sobald der Schwellenwert wiederholt überschritten wird.
Ein Tool wie Redgate Monitor hilft dabei, blockierte Prozesse im Blick zu behalten und rechtzeitig zu reagieren. Es gibt die Warnung „Blocking Process“ aus, sobald ein SQL-Prozess einen oder mehrere andere Prozesse länger als einen definierten Zeitraum blockiert. Standardmäßig wird nach einer Minute eine Low-Severity-Warnung ausgelöst. Wie bei allen Warnungen in Redgate Monitor können wir diesen Schwellenwert anpassen und sogar mehrere Warnstufen für unterschiedliche Zeitgrenzen definieren. Häufig tauchen dabei auch Meldungen wie „Long-running query“ im Zusammenhang mit den blockierten Abfragen auf.
Abbildung 4 - Warnungen zu Long-running Queries und Blocking Processes in Redgate Monitor
Klicken wir auf die Meldung „Blocking Process“, sehen wir sofort, dass sie den Hauptblocker in der Kette betrifft - in unserem Beispiel Session 67. Diese Session blockiert zwei nachgelagerte Prozesse. Interessant dabei: Session 67 führt gar keine aktive Abfrage aus, sondern hält nur noch Sperren, weil eine Transaktion fehlgeschlagen ist und nicht sauber beendet wurde.
Abbildung 5 - Details einer Blocking-Process-Warnung in Redgate Monitor
Wechseln wir vom Tab „Details“ zum Tab „Processes“, wird die gesamte Blockierungskette sichtbar: An der Spitze steht Session 67, die Session 69 blockiert, welche wiederum Session 71 blockiert. Zusätzlich sehen wir die Abfrage-Texte der blockierten Sessions (69 und 71).
Abbildung 6 - Komplette Blockierungskette im Tab „Processes“ einer Blocking-Process-Warnung
Im Bereich „Performance“ zeigt der Tab „Top Queries“ alle Abfragen, die zum Zeitpunkt der Warnung aktiv waren. Darunter auch die fehlgeschlagene Abfrage aus Session 67 - inklusive Plan Handle und den dazugehörigen Wartezeiten.
Abbildung 7 - Eine Abfrage und ihr Plan Handle, die eine Blockierung verursachen, im Tab „Top Queries“ von Redgate Monitor
Redgate Monitor zeigt uns zusätzlich viele hilfreiche Kontextinfos: Snapshots, Aggregationen und Übersichten zur Ressourcennutzung auf dem Server genau zum Zeitpunkt der Warnung. In unserem Beispiel sehen wir zum Beispiel die Spitze der Wartezeiten auf Sperren (die grüne Linie um 18:09 Uhr).
Abbildung 8 - Spitze der Wartezeiten auf Sperren zum Zeitpunkt des Redgate Monitor-Alarms für blockierte Prozesse
Oft lässt sich SQL Server Blocking beheben, indem man ineffiziente Queries optimiert oder lange laufende Datenänderungen verkürzt. Häufig laufen Abfragen innerhalb von Transaktionen, obwohl das gar nicht nötig ist. Wichtig ist: Transaktionen so kurz wie möglich halten - natürlich ohne dabei die Datenintegrität zu gefährden.
Auch Indizes spielen eine Rolle. In unserem Beispiel musste die Query von Session 67 die komplette Tabelle `SalesOrderHeader` durchsuchen, weil ein passender Index auf der Spalte `OrderDate` fehlte. Die Folge: Es wurden mehr Daten gelesen als nötig, die Abfrage dauerte länger und Sperren wurden länger gehalten.
Ein weiteres Thema sind die Isolationsstufen. Abbildung 8 zeigt, dass die Query von Session 67 unter `REPEATABLE READ` lief. Bevor wir prüfen, warum die Transaktion nicht abgeschlossen wurde, sollten wir hinterfragen, ob diese restriktive Stufe wirklich gebraucht wird. Mit der Standardisolationsstufe `READ COMMITTED` würden Sperren nach jedem Statement freigegeben - der Index-Rebuild wäre also nicht blockiert.
Eine Alternative sind Snapshot-Isolationsstufen, wie zum Beispiel `READ_COMMITTED_SNAPSHOT`. Hier werden Lesevorgänge komplett ohne Sperren durchgeführt. Dadurch blockieren Transaktionen beim Lesen keine anderen Prozesse. Viele Details zu Vor- und Nachteilen dieser Methode findet ihr im kostenlosen SQL Server Concurrency eBook von Kalen Delaney.
Eine weitere Möglichkeit in unserem Beispiel ist die Option `WAIT_AT_LOW_PRIORITY` (seit SQL Server 2014 verfügbar) für den Online-Index-Rebuild. Damit kann der Rebuild bestimmte wartende Sessions überspringen, wenn er selbst blockiert wird. In unserem Fall würde Session 71 nicht blockiert werden, weil sie die benötigte S-Sperre auf `SalesOrderHeader` trotzdem erhalten könnte - dieser Sperrmodus ist mit der S-Sperre von Session 67 kompatibel.
Locking und Blocking gehören ganz selbstverständlich zu SQL Server. Problematisch wird es, wenn lange und komplexe Transaktionen laufen, die viele Daten lesen und dadurch Sperren länger festhalten. Auch fehlende Design-Elemente wie Schlüssel, Constraints oder Indizes können dazu führen, dass Blockierungen auftreten. Gleiches gilt, wenn restriktive Isolationsstufen wie `REPEATABLE READ` oder `SERIALIZABLE` eingesetzt werden.
Werden die Blockierungen zu stark, leidet die Performance der Datenbank massiv - bis gefühlt gar nichts mehr geht. Als DBA ist es dann unsere Aufgabe, schnell zu reagieren und die eingefrorene Situation wieder aufzulösen.
Für weitere Informationen über Redgate Monitor, eine Demo oder Best Practices nehmen Sie gern Kontakt mit uns auf.
Seit mehr als 25 Jahren entwickelt Redgate spezialisierte Software für Datenbanken. 92 % der Fortune-100-Unternehmen setzen auf unsere Lösungen. Insgesamt vertrauen uns über 200.000 Kunden weltweit.
Redgate bietet eine umfassende Dokumentation und ein kompetentes, engagiertes Support-Team. Im Schnitt bewerten 87 % unserer Kundinnen und Kunden unseren Support mit „Ausgezeichnet“.
