Von Tony Davis
Beim DBA geht ein Anruf vom Helpdesk ein. Es gibt ein dringendes Problem mit einer Berichtsanwendung. Unzufriedene Benutzer sagen, dass ihre Bildschirme alle "eingefroren" sind, und das nicht wegen einer überaktiven Klimaanlage. Einige haben berichtet, dass sie ein Timeout-Problem in Verbindung mit SQL sehen. Handelt es sich um ein Blockierungsproblem?
Als DBA müssen Sie möglicherweise verschiedene Systemansichten und andere Tools verwenden, um sich ein Bild davon zu machen, welche Prozesse aktiv, aber blockiert sind, welche Sitzungen die Blockierung verursachen, welches SQL ausgeführt wird, welche Sperren beteiligt sind usw. Im Idealfall erhalten Sie eine Warnmeldung mit allen erforderlichen Diagnoseinformationen, so dass Sie das Problem beheben können, bevor der Helpdesk überhaupt den Hörer abnimmt.
Eine Datenbank ist eine gemeinsam genutzte Umgebung, in der viele Benutzertransaktionen um den gleichzeitigen Zugriff auf dieselben gemeinsamen Ressourcen, wie z. B. Tabellen und Indizes, konkurrieren.
Im Allgemeinen vermittelt SQL Server den Zugriff auf diese gemeinsamen Ressourcen, indem er verschiedene Arten von Sperren erwirbt. Eine Blockierung tritt auf, wenn eine oder mehrere Sitzungen eine Sperre für eine Ressource, z. B. eine Zeile, Seite oder Tabelle, anfordern, SQL Server diese Sperre jedoch nicht gewähren kann, weil eine andere Sitzung bereits eine nicht kompatible Sperre für diese Ressource hält.
Nehmen wir an, eine Transaktion ändert einige Zeilen in einer Tabelle unter Verwendung der Standardisolierungsebene (READ COMMITTED). SQL Server erwirbt Exklusivsperren (X) für diese Zeilen. Dieser Sperrmodus ist mit anderen Sperrmodi nicht kompatibel. Eine zweite Transaktion, die dieselben Zeilen lesen möchte (wofür eine Sperre im Modus Shared (S) erforderlich ist), wird blockiert, bis die Transaktion, die die X-Sperre hält, ein Commit oder einen Rollback durchführt.
Das Sperren und die dadurch verursachte Blockierung ist in der Regel flüchtig und ein völlig normales und wünschenswertes Betriebsmerkmal einer Datenbank. Sie stellt beispielsweise sicher, dass eine Transaktion keine Daten liest, die sich im Fluss befinden (d. h. sie verhindert "schmutzige Lesevorgänge"), und dass zwei Transaktionen nicht dieselbe Datenzeile ändern können, was zu Datenverfälschung führen könnte.
Wenn die Blockierung jedoch über längere Zeiträume auftritt, kann sie die Leistung vieler Benutzerprozesse beeinträchtigen.
Wenn wir auf ein potenzielles Blockierungsproblem aufmerksam gemacht werden, während es noch auftritt, stellt SQL Server eine Fülle von Informationen zur Verfügung, die uns bei der Untersuchung und Behebung des Problems helfen.
Beginnen Sie mit der dynamischen Verwaltungsansicht (DMV) sys.dm_os_waiting_tasks. Wenn eine Anforderung aktiv ist, aber darauf wartet, eine Ressource zu erhalten, um fortfahren zu können, wird sie in dieser Ansicht angezeigt. Die Ansicht gibt Auskunft über die Art des Wartens, die Ressource, auf die die Anfrage wartet, und mehr. Wenn das Blockieren die Ursache unseres Problems ist, würden wir erwarten, dass wir warten, um Sperren für Zeilen, Seiten, Tabellen usw. zu erhalten.
Es gibt viele verschiedene Möglichkeiten, diese DMV abzufragen und sich mit anderen DMVs zu verbinden, um Details über die blockierten und blockierenden Sitzungen und die ausgeführten Abfragen zu erhalten. Ich habe die Abfrage verwendet, die in SQL Server Performance Tuning using Wait Statistics: A Beginner's Guide (kostenloser PDF-Download).
Abbildung 1 zeigt eine Beispielausgabe (zur besseren Lesbarkeit habe ich das Ergebnis auf zwei Bildschirme aufgeteilt).
Abbildung 1 - Überprüfung blockierter Sitzungen mit dem DMV sys.dm_os_waiting_tasks
Die Warteart für Sperren hat die Form LCK_M_lock type. So ist zum Beispiel LCK_M_SCH_M ein Warten auf eine SCH_M (Schema Modification) Sperre. Abbildung 1 zeigt eine Sperrkette mit drei Sitzungen (IDs 67, 69 und 71). An der Spitze der Kette steht die Sitzung 67, die die Sitzung 69 blockiert, die wiederum die Sitzung 71 blockiert.
Sitzung 67 ist die einzige Sitzung, die nicht darauf wartet, eine Sperre zu erhalten. Sie führt eine Transaktion gegen die Tabelle SalesOrderHeader aus. In der Regel handelt es sich dabei um eine Transaktion, die aus irgendeinem Grund lange braucht, um Daten zu ändern, oder bei der ein unerwarteter Fehler aufgetreten ist, der zum Abbruch des Stapels geführt hat, aber die Transaktion offen (unbestätigt) gelassen hat.
Die von Sitzung 67 gehaltenen Sperren hindern Sitzung 69 daran, die Sperre zu erlangen, die sie benötigt, um den geclusterten Index auf SalesOrderHeader zu ändern (in diesem Fall, um einen Online-Index-Neuaufbau durchzuführen). Am Ende der Kette ist Sitzung 71 blockiert und wartet darauf, dass Sitzung 69 ihre Arbeit beendet, damit sie eine gemeinsame Lesesperre für die Tabelle SalesOrderHeader erhalten kann, um die erforderlichen Zeilen zu lesen.
DBAs und Entwickler diagnostizieren Fälle von schwerem Locking, bei denen mehrere Sitzungen an verschiedenen Punkten einer langen Blockierungskette blockiert sind, manchmal fälschlicherweise als Deadlocks, vor allem, weil es so aussehen kann, als ob die gesamte Datenbank blockiert ist.
Der große Unterschied besteht darin, dass ein Deadlock einen bestimmten Fehler verursacht und eine der Transaktionen zurückgenommen wird. Bei Blocking wird kein Fehler ausgelöst. Die Sitzung am Anfang einer blockierenden Kette wartet nicht auf eine Sperre. Sie kann auf einen Latch, eine Speicherzuweisung oder IO warten, aber die blockierende Kette wird aufgelöst, sobald die Ressource verfügbar wird und der Hauptblocker seine Arbeit beenden kann.
Es gibt eine Fülle weiterer Informationen, die wir aus verschiedenen Quellen gewinnen können, um aktuelle Blockierungsprobleme zu diagnostizieren. Wenn wir zum Beispiel weitere Details über die Arten von Sperren benötigen, die von jeder Sitzung in der Kette gehalten werden, und zwar für welche Ressourcen, sowie über Sperren, auf deren Erwerb die Sitzungen warten, können wir die DMV sys.dm_tran_locks verwenden und die zuvor erworbenen Sitzungs-IDs angeben. DMVs wie sys.dm_tran_active_transactions liefern eine Liste aller Transaktionen, die zum Zeitpunkt der Ausführung der Abfrage aktiv sind. sys.dm_exec_sessions zeigt den Besitzer aller blockierenden Sitzungen an usw.
Wenn jedoch alle diese DMVs nur Daten für aktuelle Abfragen anzeigen, wie können wir dann im Nachhinein einen Fall von "eingefrorenen Bildschirmen" diagnostizieren? Wie können wir SQL Server überwachen, um festzustellen, ob starke Blockierungen Leistungsprobleme auf unseren Servern verursachen?
Wenn wir den Verdacht haben, dass das Blockieren ein ständiges Problem für eine bestimmte Datenbank sein könnte, es aber im Moment nicht auftritt, gibt es eine Reihe von Werkzeugen, die wir zur Untersuchung verwenden können. Die sys.dm_os_wait_stats DMV zeigt uns beispielsweise an, ob es große kumulierte Wartezeiten gibt, die mit dem Warten auf Sperren zusammenhängen.
Abbildung 2 - kumulierte Wartezeiten für jedes Warten, wie von der DMV sys.dm_os_wait_stats angezeigt
Wir können auch die DMV sys.dm_db_index_operational_stats verwenden, um nach Indizes zu suchen, die "Hotspots" sperren, wie zum Beispiel von Jason Strate gezeigt.
Abbildung 3 - Indizes, die mit hohen Sperrwartezeiten verbunden sind, wie die DMV sys.dm_db_index_operational_stats zeigt
Diese aggregierten Wartestatistiken und Indexnutzungsstatistiken können helfen, ein Problem zu erkennen, aber sie allein können die Ursache nicht identifizieren.
Ein Tool wie Performance Monitor (PerfMon) bietet einige Zähler zur Überwachung von übermäßigem Sperren und Blockieren. Das Objekt SQLServer:General Statistics zeigt zum Beispiel die Anzahl der festgestellten blockierten Prozesse an. Das Objekt SQLServer:Locks kann Avg Wait Time (ms), Lock Waits/sec und mehr liefern. Aber auch hier gilt, dass sie nur einen Hinweis auf ein mögliches Problem geben.
Wir müssen eine Überwachungslösung einrichten, die uns nicht nur auf schwere Blockierungen als potenzielles Problem aufmerksam macht, sondern auch genügend Informationen für die Diagnose und Behebung des Problems liefert.
Wenn die DMV-Daten und die PerfMon-Zähler besorgniserregende Anzeichen für übermäßiges Sperren und Blockieren zeigen, lohnt es sich, SQL Trace oder vorzugsweise eine Erweiterte Ereignis-Sitzung einzurichten, um das Auftreten von Blockierungen zu protokollieren, die eine bestimmte Zeitschwelle überschreiten, und den Blocked Process Report für diese Ereignisse zu erfassen.
Standardmäßig ist der "Schwellenwert für blockierte Prozesse" Null, was bedeutet, dass SQL Server keine Berichte über blockierte Prozesse erstellt. Wir müssen den Schwellenwert für blockierte Prozesse mit Hilfe der Option sp_configure auf einen bestimmten Wert (in Sekunden) einstellen. Jeremiah Peschka zeigt, wie man den Schwellenwert konfiguriert und dann ein erweitertes Ereignis zur Erfassung des Berichts definiert.
Listing 1 zeigt die verkürzte Ausgabe für einen der Berichte, die zeigt, dass der Online-Index-Neuaufbau (Sitzung 69) durch eine nicht bestätigte Transaktion auf SalesOrderHeader (Sitzung 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/>
Liste 1 - Ausgabe eines Berichts über blockierte Prozesse, der drei blockierte Sitzungen anzeigt
Der Bericht über blockierte Prozesse liefert zwar die Informationen, die wir für die Fehlersuche in Fällen von Blockierungen benötigen, aber er ist kaum in einem leicht zu verdauenden Format, und oft wird eine überwältigende Anzahl von Berichten generiert, da eine einzelne Blockierungskette mehrere Berichte erzeugen kann, wenn sie den Schwellenwert mehrfach überschreitet.
Ein Tool wie Redgate Monitor bietet Überwachung und Warnungen für blockierte Prozesse. Es löst die Warnung blockierender Prozess für jeden SQL-Prozess aus, der einen oder mehrere andere Prozesse länger als eine bestimmte Zeitspanne blockiert hat. Standardmäßig wird eine Warnung mit niedrigem Schweregrad ausgelöst, wenn die Blockierung 1 Minute überschreitet, aber wie bei jeder Warnung in Redgate Monitor können wir den Schwellenwert anpassen und mehrere Warnstufen für verschiedene Schwellenwerte festlegen. Wahrscheinlich werden auch Long-running query-Warnungen in Bezug auf blockierte Prozessabfragen angezeigt.
Abbildung 4 - Lang laufende Abfrage und blockierende Prozesswarnungen in Redgate Monitor
Wenn wir auf die Meldung Blockierender Prozess klicken, können wir sehen, dass sie sich auf den Hauptblocker in unserer Kette, Sitzung 67, bezieht. Sie hat zwei blockierte Nachkommen. Die Sitzung führt eigentlich kein SQL aus. Es handelt sich lediglich um eine Transaktion, bei der die Übertragung fehlgeschlagen ist und die immer noch Sperren für die Zieltabelle hält.
Abbildung 5 - Details eines Alarms über einen blockierten Prozess in Redgate Monitor
Wenn wir von der Registerkarte Details zur Registerkarte Prozesse wechseln, sehen wir die vollständige Blockierungskette, mit Sitzung 67 an der Spitze, die Sitzung 69 blockiert, die wiederum Sitzung 71 blockiert. Wir sehen auch den Text der Abfragen, die von den beiden blockierten Sitzungen (69 und 71) ausgeführt werden.
Abbildung 6 - eine vollständige Blockierungskette aus der Registerkarte "Prozesse" des Redgate Monitor's Blocking process alert
Im Abschnitt Leistungsdaten der Seite werden auf der Registerkarte Top-Abfragen alle Abfragen angezeigt, die zum Zeitpunkt der Warnung ausgeführt wurden. Sie zeigen die Abfrage, die in Sitzung 67 ausgeführt wurde und deren Übergabe fehlgeschlagen ist, mit ihrem Planhandle und allen zugehörigen Wartezeiten.
Abbildung 7 - eine Abfrage und ihr Planhandle, die eine Blockierung verursachen, auf der Registerkarte Top-Abfragen in Redgate Monitor
Redgate Monitor liefert außerdem als Kontextinformationen alle möglichen Schnappschüsse, Aggregationen und Zusammenfassungen der Ressourcennutzung auf dem Server zum Zeitpunkt der Meldung. In diesem Fall können wir die Spitze der Wartezeiten auf Sperren um die Zeit des Alerts herum sehen (die grüne Linie um 18.09 Uhr).
Abbildung 8 - eine Spitze bei den Wartezeiten auf Sperren zum Zeitpunkt des Redgate Monitor Alarms für blockierte Prozesse
Sehr oft erfordert die Lösung von SQL Server-Blockierungsproblemen die Optimierung ineffizienter Abfragen und Datenänderungen, die länger als nötig laufen und daher Blockierungen verursachen. Manchmal werden Abfragen innerhalb von Transaktionen ausgeführt, obwohl dafür keine wirkliche Notwendigkeit besteht. Transaktionen müssen so kurz wie möglich gehalten werden, ohne die Integrität der Transaktionen zu gefährden.
Manchmal sind Indizes hilfreich. In unserem Beispiel musste die Abfrage der Sitzung 67 die gesamte Tabelle SalesOrderHeader durchsuchen, da ein geeigneter Index für die Suchspalte (Orderdate) fehlte. Das bedeutet, dass wahrscheinlich mehr Daten als nötig gelesen wurden, die Ausführung länger dauerte und daher Sperren länger als nötig gehalten wurden.
Es gibt noch andere mögliche Probleme, auf die man achten sollte. Abbildung 8 zeigt, dass die Abfrage von Sitzung 67 unter der Isolationsebene REPEATABLE READ lief. Abgesehen davon, dass wir herausfinden, warum die Transaktion nicht übertragen wurde, könnten wir untersuchen, ob die Abfrage wirklich diese restriktive Isolierungsebene benötigt, bei der Sperren gehalten werden, bis die Transaktion übertragen wird. Durch Umschreiben auf die Standardisolationsebene READ COMMITTED würden die Sperren nach Abschluss der Anweisung freigegeben, was bedeutet, dass der Indexaufbau nach Abschluss der Abfrage nicht blockiert wird.
Alternativ könnte man eine der Snapshot-basierten Isolationsebenen verwenden, wie z.B. READ_COMMITTED_SNAPSHOT, die alle Lesephänomene verhindert. In diesem Level nehmen Transaktionen keine Sperren beim Lesen von Daten, so dass sie andere Prozesse nicht blockieren. Das SQL Server Concurrency eBook von Kalen Delaney (kostenloser Download) enthält viele weitere Details zu den Vorteilen und möglichen Nachteilen von Snapshot-basierten Isolationsebenen.
Eine letzte Option in diesem speziellen Beispiel wäre die Verwendung der WAIT_AT_LOW_PRIORITY-Option (neu in SQL Server 2014) für den Online-Indexneuaufbau. Dadurch können einige wartende Sitzungen in der Warteschlange übersprungen werden, wenn der Indexneuaufbau selbst blockiert ist. In diesem Beispiel würde die Sitzung 71 mit dieser Option nicht blockiert, da sie die S-Sperre, die sie für die Tabelle SalesOrderHeader benötigt, überspringen und erwerben könnte, da dieser Sperrmodus mit der S-Sperre von Sitzung 67 kompatibel ist.
Das Sperren und Blockieren ist eine normale und erforderliche Aktivität in SQL Server. Wenn wir jedoch lange, komplexe Transaktionen ausführen, die viele Daten lesen und deren Ausführung viel Zeit in Anspruch nimmt, werden Sperren über längere Zeiträume gehalten, und das Blockieren kann zu einem Problem werden. Gleiches gilt, wenn unsere Datenbanken nicht über geeignete Designmerkmale wie Schlüssel, Beschränkungen und Indizes verfügen oder wenn unsere Transaktionen restriktive Transaktionsisolierungsebenen verwenden (wie REPEATABLE READ oder SERIALIZABLE).
Wenn die Blockierung zu stark wird, kommt die Leistung der Datenbank zum Erliegen, und als DBA müssen wir sehr schnell handeln, bevor eine eisige Atmosphäre unter den Benutzern entsteht.
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“.
