SQL Server-Blockierungsanalyse

Sperren bzw. Blockierungen treten auf, wenn zwei (oder mehr) Prozesse gleichzeitig auf dieselbe Ressource zugreifen müssen. Wenn ein Prozess Zugriff auf eine Ressource anfordert, erhält er eine Sperre, die andere Prozesse, die die Ressource benötigen, zwingt zu warten, bis der erste Prozess abgeschlossen wurde. In den meisten Fällen wartet der zweite Prozess, bis der erste Prozess die Sperre freigibt. Anschließend können die Abläufe wie gewohnt fortgesetzt werden. Meist dauert dies wenige Sekunden.

Doch manchmal benötigt der erste Prozess länger als erwartet, sodass der zweite Prozess (und ggf. weitere Prozesse) sich dahinter staut, während er auf die Ressource wartet. Dies kann die Anwendungsleistung und das Endnutzererlebnis beeinträchtigen. Die Dauer und der Transaktionskontext einer Abfrage sind ausschlaggebend dafür, wie lange ihre Sperren gehalten werden.

Wichtig ist dabei: Nicht alle SQL Server-Sperren sind etwas Schlechtes. Tatsächlich sind Sperren ganz normal. Schließlich ist SQL Server ein sperrenbasiertes Parallelitätssystem, und Ressourcen können nur jeweils von einem Prozess genutzt werden. Daher kommt es zwingend gelegentlich zu Überschneidungen. Problematisch sind nur Sperren, die länger andauern und Sperrketten verursachen.

Dazu kommt, dass nicht alle Arten von Sperren automatisch alle anderen Sperren blockieren. Manche Sperrtypen sind restriktiver und führen daher mit größerer Wahrscheinlichkeit zu Blockierungen. Zu den Sperrtypen gehören:

• Exklusive Sperren: Diese Sperren werden eingesetzt, wenn ein Prozess Daten ändern muss. So wird sichergestellt, dass nicht zwei Prozesse gleichzeitig Daten ändern.
• Schema-Sperren: Schema-Sperren werden für Prozesse genutzt, die während der Ausführung auf ein konsistentes Schema angewiesen sind.
• Gemeinsame Sperren: Diese Sperren werden für Lesevorgänge genutzt. Sie sind weniger restriktiv als viele andere Sperrtypen und führen daher selten zu Blockierungen.
• Aktualisierungssperren: Aktualisierungssperren werden für Aktualisierungen genutzt, üblicherweise für solche, die in mehreren Schritten erfolgen.

Manche Sperrtypen sind stärker kompatibel mit anderen. Exklusive Sperren sind z. B. mit keinen anderen Sperrtypen kompatiblen und führen zu Blockierungen, wenn andere Prozesse die Ressource benötigen. Gemeinsame Sperren und Aktualisierungssperren hingegen können gleichzeitig für dieselbe Ressource genutzt werden.

Deadlocks und Sperren können leicht verwechselt werden, da beide auftreten, wenn zwei Prozesse bestimmte Ressourcen benötigen, um eine Abfrage abzuschließen, aber nicht auf sie zugreifen können. Doch Sperren und Deadlocks sind nicht dasselbe.

Sperren treten in SQL Server auf, wenn zwei (oder mehr) Prozesse dieselbe Ressource benötigen. Schließlich kann nur ein Prozess gleichzeitig auf eine Ressource zugreifen. Wenn ein Prozess eine Ressource verwendet, kann daher der nächste Prozess erst auf sie zugreifen, wenn der erste Prozess sie nicht mehr benötigt.

Deadlocks hingegen treten auf, wenn Prozesse versuchen, auf unterschiedliche Ressourcen zuzugreifen. Der erste Prozess sperrt z. B. Ressource A und der zweite Prozess sperrt Ressource B. Wenn der erste Prozess eine Sperre für Ressource B benötigt, um abgeschlossen zu werden, und der zweite für Ressource A, kommen beide Vorgänge zum Erliegen und warten aufeinander. So sind beide in einer Sackgasse.

Zum Glück kann SQL Server häufig Deadlocks automatisch durch den Rollback eines Prozesses lösen, sodass der andere Prozess ungehindert fortgesetzt werden kann. SQL Server startet anschließend den zweiten Prozess neu, sobald der erste die Ressource nicht mehr benötigt.

Es kommt zwangsläufig gelegentlich vor, dass zwei Prozesse dieselbe Ressource gleichzeitig benötigen, und Blockierungen lösen sich oft selbst auf, sobald der erste Prozess abgeschlossen wurde. In manchen Fällen können Blockierungen jedoch zum Problem werden.

Um Blockierungen in SQL Server zu prüfen, können Sie mit einer Abfrage die blockierende SPID identifizieren. Dazu müssen Sie lediglich SQL Server-DMVs nutzen und eine Abfrage mit der DMV sys.dm_exec_requests durchführen. So erhalten Sie eine Liste der blockierenden SPID, aller blockierten SPIDs und Wartezeittyp sowie ‑dauer.

Alternativ können Sie mit SQL Server Management Studio die Blockierungen in SQL Server einsehen und herausfinden, welche Sperre an der Spitze einer Kette steht. Wählen Sie Server Object, Reports, Standard Reports und anschließend Activity — All Blocking Transactions. SQL Server Management Studio gibt anschließend einen Bericht aus, der die Transaktion an der Spitze der Sperrkette sowie alle aktuell von dieser Transaktion blockierten Transaktionen anzeigt.

SQL Sentry macht das Identifizieren problematischer Blockaden noch leichter, da es durchgehend auf SQL Server-Sperren prüft. Mit dem Ereigniskalender von SQL Sentry können Sie Sperren zusammen mit anderen Ereignissen anzeigen. Zusätzlich bietet SQL Sentry robuste Blockierungsanalysen und erfasst Detailinformationen zu Sperren abhängig von Ihren spezifischen Schwellenwerten für die Dauer. Wenn Sie z. B. Informationen zu jeder Sperre erhalten möchten, die länger als zehn Sekunden anhält, speichert SQL Sentry automatisch Daten zu diesen Sperren (z. B. ausgeführte Anweisung, Host, Anmeldung, Datenbank, Wartezeittypen und Ressourcen). Diese Detailinformationen können Sie in Echtzeit oder im Nachhinein einsehen.

Die hierarchische Ansicht mit farblich gekennzeichneten Knoten macht es möglich, Sperrketten auf einen Blick zu überblicken. Sie wissen sofort, ob eine Anweisung ausgeführt wird, abgeschlossen wurde oder blockiert wird. Außerdem können Sie die Beziehungen zwischen den blockierenden und blockierten SPIDs in der Kette einsehen und unkompliziert Analysen durchführen.

Sobald Sie eine problematische Sperre identifiziert haben, ist es Zeit für die Problembehandlung. Zu den häufigen Blockierungsszenarien und Lösungen, die Ihnen begegnen könnten, gehören die folgenden:

• Abfragen mit langer Ausführungszeit: Wenn eine Abfrage eine übermäßig lange Ausführungszeit aufweist und aggressiv Sperren nutzt, damit keine anderen Prozesse dieselben Daten lesen oder bearbeiten können, kann dies zu problematischen Blockierungen in SQL Server führen. Um das Problem zu beheben, müssen Sie erst die blockierende SQL Server-Abfrage finden, indem Sie DMVs analysieren oder Ihre Monitoring-Plattform prüfen. Anschließend können Sie Ihre Abfrage und Ihre Ausführungspläne optimieren.
• Eine Anwendung nutzt eine komplexe Transaktion, die Anweisungen oder Batches vor dem Commit ausführt: Wenn eine Anwendung mehrere Anweisungen oder Batches vor dem Commit (dem Speichern der Daten) ausführt, können andere Prozesse währenddessen nicht auf die Ressource zugreifen. Auch in diesem Fall müssen Sie die Abfrage an der Spitze der Sperrkette identifizieren und anschließend ihre Leistung optimieren.
• Eine Transaktion wird abgebrochen und ihre Sperren werden nicht freigegeben: Wenn es zu einer Zeitüberschreitung bei einer Abfrage einer Anwendung kommt oder die Abfrage abgebrochen wird, beendet die SPID die Abfrage und den Batch. Da sie allerdings nicht automatisch einen Rollback oder Commit für die Transaktion durchführt, kann es zu einer Blockierung kommen. Dieses Problem können Sie beheben, indem Sie identifizieren, welche SPID die Transaktion offen hält, und entweder einen Rollback starten oder den T-SQL-Kill-Befehl nutzen.
• Eine SPID, deren Client-Anwendung nicht alle Ergebniszeilen für die Fertigstellung abgerufen hat: Wenn eine Anwendung nicht sofort alle zur Fertigstellung benötigten Ergebniszeilen abruft, verbleiben die Sperren auf der Tabelle und führen zu einer Blockade. Zur Problemlösung müssen Sie die Anwendung neu schreiben, sodass sie alle Zeilen des Ergebnisses bis zur Fertigstellung neu abruft. Sie können das Problem von vornherein vermeiden, indem Sie Anwendungen mit problematischem Verhalten auf eine separate Berichterstellung oder entscheidungsunterstützende Datenbank beschränken.

Die gute Nachricht ist: SQL Sentry kann Ihnen helfen, egal mit welcher Art von Blockierungsszenario Sie es zu tun haben. SQL Sentry erfasst und speichert Informationen zu Sperren und Deadlocks. Damit bietet es Ihnen nicht nur einen umfassenden Überblick über die aktuelle Leistung, sondern auch Einblicke in die Vergangenheit. Außerdem können Sie Probleme mit Sperren in Echtzeit einsehen und blockierende Prozesse über SQL Sentry mit einem Klick beenden. Mithilfe von SQL Sentry können Sie verstehen, weshalb Blockaden auftreten, und Ihre Abfragen und Transaktionen so neu gestalten, dass sie zukünftig vermieden werden.

SQL Sentry kann sogar automatisch gegen blockierende SQL-Bedingungen vorgehen, wenn Sie gerade nicht da sind. Dazu müssen Sie SQL nur im Voraus so konfigurieren, dass bei bestimmten Bedingungen spezifische Aktionen durchgeführt werden.

Datenbankadministratoren sollten unbedingt wissen, wie sie Blockierungen in SQL Server finden können, da die Blockierungen sie von geschäftskritischen Ressourcen abschneiden und an einem effizienten Datenbankmanagement hindern können. Die Problembehebung kann bei Blockierungen jedoch komplex und zeitaufwändig sein. Hier sind fünf der häufigsten Ursachen für Blockierungen:

1. Ineffiziente SQL-Anweisungen, die ausgeführt wurden, als die Datenbanksitzung gesperrt war
2. Inaktive Sitzungen, die auf der Transaktions-Schachtelungsebene verloren gegangen sind
3. Anwendungen, die nicht alle Ergebniszeilen abrufen konnten
4. Sitzungen, die Rollbacks für Daten durchführen
5. Sitzungen, die abgebrochen wurden, weil die Client-Anwendung abgestürzt ist oder getrennt wurde

In Großunternehmen mit noch größeren Datenbanken gibt es noch viele weitere mögliche Gründe für blockierte Sitzungen. Manuell an die Spitze der Sperrkette zu gelangen und den Grund aufzuspüren wäre für Datenbankadministratoren eine kaum zu bewältigende Aufgabe.

Standardmäßige SQL Server-Überwachungstools stellen Datenbanksperren und Wartezeiten bei Blockierungen dar, aber identifizieren nicht die für eine Sperrkette verantwortliche Sperre. Mehr Sperren und Blockierungen führen zu langsameren Anwendungen. Doch wie können Datenbankadministratoren herausfinden, ob zunehmende Sperren ein Zeichen für eine ineffiziente Datenbank oder ein Grund für langsame Anwendungsreaktionszeiten sind?

Tools wie SQL Sentry helfen DBAs, SQL Server-Sperrketten auf den Grund zu gehen, Blockierungen schnell zu lösen und alles wieder zum Laufen zu bringen.

Blockierungen zu finden ist nicht nur dann wichtig, wenn sie aktiv die Datenbankleistung beeinträchtigen. Da Sperren und Blockaden bei der Arbeit mit SQL Server zwangsläufig auftreten, müssen DBAs alles tun, um diesbezügliche Probleme proaktiv zu überwachen oder Code zu reparieren, der inaktive Sperren verursacht. Software zu nutzen, mit denen Sie Blockaden bereits im Anfangsstadium erkennen, gehört zu den wichtigsten Best Practices im Datenbankmanagement.