Wenn ein Problem mit der Berichtsanwendung auftritt und Benutzer sich darüber beschweren, dass ihre Bildschirme eingefroren sind, kann es sich um ein Blockierungsproblem handeln. Dies ist besonders wahrscheinlich, wenn das Problem nicht durch eine überaktive Klimaanlage verursacht wird und wenn Benutzer ein SQL-bezogenes Zeitüberschreitungsproblem melden.
Als Datenbankadministrator müssen Sie reaktive Ermittlungsarbeit leisten, wenn Sie von Blockierungsproblemen überrascht wurden. Auf diese Weise können Sie verstehen, welche Prozesse blockiert werden, welche Sessions für die Blockierung verantwortlich sind, welcher SQL Server zu der Zeit ausgeführt wurde und welche Sperren betroffen waren.
Idealerweise haben Sie bereits SQL Server-Überwachungspraktiken eingeführt oder robuste SQL Server-Überwachungstools implementiert. Wenn Sie ein SQL Server-Überwachungstool verwenden, sollten Sie eine Benachrichtigung erhalten haben, die Ihnen Zugriff auf relevante Diagnosedaten gibt. Wenn Ihnen ein effektives Tool zur Verfügung steht, können Sie Probleme lösen, bevor ein Helpdesk-Ticket überhaupt registriert ist.
In dieser Anleitung erfahren Sie, was Blockierungen verursacht, wie Sie Blockierungen in SQL Server finden und wie Sie Blockierungsprobleme beheben können. Es werden auch Lösungen empfohlen, die Ihnen bei der Fehlerbehebung und Behebung von SQL Server-Blockierungskettenproblemen helfen.
Dieser Leitfaden hilft Ihnen zu verstehen, was Blockierungen verursacht, wie Sie Blockierungen in SQL Server finden und Blockierungsprobleme lösen. Außerdem werden Lösungen empfohlen, die Ihnen bei der Fehlerbehebung und Behebung von Problemen mit der SQL Server-Blockchains helfen.
- Was verursacht SQL Server Blockchain Probleme?
- Der ACID-Test
- Auswirkungen von SQL Server Blockchain Probleme
- Ursachen für schwerwiegende Blockierungsprobleme
- Blockierungsprobleme und Lösungen
- Auswahl des richtigen SQL Server-Blockchain-Tools
- So finden Sie Blockierungen in SQL Server mit SolarWinds DPA
- Erste Schritte mit der SQL Server-Blockchain-Analyse
Was verursacht SQL Server Blockchain Probleme?
Wenn Sie sich fragen, wie Sie die Blockierung in SQL Server finden können, sollten Sie zunächst verstehen, warum Blockchain Probleme mit SQL Server verursacht.
In stark ausgelasteten Datenbanken konkurrieren häufig mehrere Benutzertransaktionen, um auf dieselben Indizes und Tabellen zuzugreifen. Typischerweise vermittelt SQL Server den Zugriff auf freigegebene Ressourcen mit Sperren. Blockierungen treten auf, wenn eine oder mehrere Sitzungen um eine Ressourcensperre (z. B. eine Seite, Tabelle oder Zeile) bitten, aber SQL Server diese nicht gewähren kann, weil eine andere Sitzung bereits eine Ressourcensperre enthält.
Blöcke sind das logische Ergebnis von Sperren, die gegen verschiedene Datenbankobjekte ausgegeben werden. Wenn eine Anfrage an ein Objekt gerichtet wird, für das bereits eine Sperre ausgestellt wurde, wird die Anfrage so lange auf Eis gelegt, bis die ursprüngliche Sperre entfernt wurde. Unter Sperrung versteht man die Verzögerung oder Verhinderung einer Transaktion.
Angenommen, Anfrage A versucht, eine Zeile in eine bestimmte Tabelle einzufügen. Bevor Anforderung A jedoch abgeschlossen ist, übermittelt Anforderung B eine Aktualisierung derselben Tabelle. Wenn die von Anforderung A angeforderte Zeile von Anforderung B beeinflusst wird, tritt eine Blockierung auf. Dies liegt daran, dass für die von Anforderung A betroffene Zeile für die gesamte Dauer der Transaktion eine exklusive Sperre ausgestellt wurde.
Sperr- und Blockierungsvorgänge dauern meist nicht lange und sind völlig normal. Manchmal sind Sperren und Blockierungen sogar wünschenswert, da sie zur Integrität der Datenbank beitragen. Das Blockieren hilft beispielsweise sicherzustellen, dass eine Transaktion keine Daten liest, die als im Fluss befindlich gelten. Mit anderen Worten, Blockierungen verhindert etwas, das oft als „Dirty Reads“ bezeichnet wird. Es stellt sicher, dass zwei verschiedene Transaktionen nicht in der Lage sind, dieselbe Datenzeile zu ändern. Ohne Blockierung könnte dies zu Datenbeschädigung führen.
Sperrungen sind ein wesentlicher Bestandteil einer erfolgreichen Transaktionsverarbeitung für jedes relationale Datenbankverwaltungssystem (RDBMS), da es die Datenintegrität aufrechterhält. Ein RDBMS bewahrt die Datenintegrität, indem es sicherstellt, dass jede Transaktion den ACID-Test besteht, der das Sperren von Objekten adressiert.
Der ACID-Test
Die vier Kriterien für den ACID-Test sind:
- Atomic: Die Transaktion muss nach dem Alles-oder-Nichts-Prinzip durchgeführt werden.
- Konsistent: Transaktionen müssen einheitlich und konsistent abgewickelt werden.
- Isoliert: Transaktionen müssen entsprechend isoliert werden, bis sie abgeschlossen sind.
- Dauerhaft: Das RDBMS muss eine Aufzeichnung aller unvollständigen Transaktionen führen, um die Wiederherstellung zu erleichtern, falls ein Fehler auftritt.
Das Isolationselement des ACID-Tests wird durch Objektsperre adressiert. Dabei werden in der Regel eine oder mehrere Datenzeilen gesperrt, bis die zugehörigen Transaktionen abgeschlossen sind. Die Objektsperre verhindert, dass alle anderen Prozesse die Objekte verändern können, bis die Sperre aufgehoben wurde.
Auswirkungen von SQL Server Blockchain Probleme
Obwohl sie notwendige und wünschenswerte betriebliche Merkmale einer Datenbank sind, können Sperren und Blockierungen die Leistung vieler Benutzerprozesse negativ beeinflussen. Ein hohes Maß an Blockierung kann zu einem großen Problem werden, wenn sie ungelöst bleiben, da es die Fähigkeit eines Unternehmens beeinträchtigen kann, seine Daten aktuell zu halten. Es kann sich auch auf Endnutzer auswirken, z. B. auf Kunden, die die Website eines Unternehmens besuchen.
Eine der häufigsten Folgen von hohen Blockierungen ist die hohe Wartezeit für SQL Server. Diese hohen Wartezeiten führen zu langsamen Reaktionszeiten auf Datenbankanfragen.
In den schlimmsten Fällen können zahlreiche Sitzungen an unterschiedlichen Stellen einem langen Blockchain blockiert werden, wodurch die Reaktionsfähigkeit des SQL Servers drastisch sinkt. Diese Situation wird oft fälschlicherweise als „Sackgasse“ bezeichnet. Allerdings gibt es einen entscheidenden Unterschied zwischen Deadlock und Blockierung. Während Deadlock einen bestimmten Fehler verursacht (mit einem Rollback einer der Transaktionen), verursacht das Blockieren keine Fehler, egal wie schwerwiegend es ist. Die Sitzung an der Spitze eines Blockchains wartet nicht auf eine Sperre. Stattdessen wartet es möglicherweise auf Speicherzuweisung, E/A oder einen Riegel. Blockchain wird gelöscht, wenn die Ressource verfügbar wird, und der Hauptblocker kann dann seine Prozesse abschließen.
Ursachen für schwerwiegende Blockierungsprobleme
In SQL Server ist eine Sperre unvermeidbar. Dies geschieht, wenn eine Sitzung eine Sperre für eine Ressource beibehält, während andere Sitzungen gleichzeitig versuchen, widersprüchliche Sperren für die Ressource zu erfassen. Die zweite Sitzung muss auf einen der LCK_M-Wartetypen warten. Unter bestimmten Voraussetzungen kann dies die Leistung stark beeinträchtigen. Schwerwiegende Verriegelungsprobleme können durch einen oder beide der folgenden Umstände verursacht werden:
- Eine Sitzung hält Sperren für einen langen Zeitraum aufrecht, bevor sie freigegeben werden. Dies tritt normalerweise auf, wenn eine Sitzung Sperren erwirbt und versucht, verschiedene, ineffiziente SQL-Anweisungen auszuführen, bevor die Sperren freigegeben werden. Im Allgemeinen behebt sich dieser Blockierungstyp im Laufe der Zeit selbst, kann aber abhängig von den Ausführungszeiten der ineffizienten SQL-Anweisungen zu langen Wartezeiten führen.
- Eine Sitzung verwaltet Sperren und gibt sie nicht frei. Dies kann sich nicht von selbst lösen und kann den Zugriff auf Ressourcen auf unbestimmte Zeit verhindern. Um diese Sperrtypen freizugeben, müssen Sie die Sitzung möglicherweise komplett beenden.
Blockierungsprobleme und Lösungen
Eines der häufigsten Blockierungsprobleme wird durch lange und ineffiziente SQL-Anweisungen verursacht, die während der Wartung von Sperren ausgeführt werden. Um dies zu korrigieren, gibt es zwei Schritte:
- Optimieren Sie die schlecht durchführenden SQL-Anweisungen, so dass Sperren so kurz wie möglich gehalten werden.
- Legen Sie fest, ob die Sperren freigegeben werden können, bevor die ineffiziente SQL-Anweisung von der Sitzung ausgeführt wird.
Wenn beispielsweise Sperren erfasst werden, weil eine DELETE-Anweisung ausgeführt wird und eine SELECT-Anweisung dann versucht, einen vollständigen Tabellenscan durchzuführen, sollten Sie festlegen, ob eine COMMIT-Anweisung zwischen ihnen ausgeführt werden kann. Dies sollte dazu beitragen, dass die Sperren früher freigegeben werden.
Schlafsitzungen, die den Überblick über die Verschachtelungsebene der Transaktion verloren haben, sind ein weiteres Beispiel für ein häufiges Blockierungsproblem. Wenn eine Anwendung eine SQL-Anweisung annulliert oder eine Zeitüberschreitung hat, aber keine COMMIT- oder ROLLBACK-Anweisung ausgibt, können Ressourcen auf unbestimmte Zeit gesperrt bleiben. Die Lösung dazu lautet wie folgt:
- Senden Sie nach einem Fehler eine Anweisung IF@@TRANCOUNT > 0 ROLLBACK TRAN über die Fehlerbehandlungsroutine der Clientanwendung.
- Verwenden Sie für die Verbindung SET XACT_ABORT ON. Dies sollte auch in allen gespeicherten Prozeduren verwendet werden, die Transaktionen starten und nach einem Fehler nicht bereinigt werden. Wenn ein Laufzeitfehler auftritt, stellt diese Einstellung sicher, dass alle offenen Transaktionen abgebrochen werden und die Kontrolle an den Client zurückgegeben wird.
- Wenn Verbindungspooling in einer Anwendung verwendet wird, die die Verbindung öffnet und eine begrenzte Anzahl von Abfragen ausführt, bevor die Verbindung an den Pool zurückgegeben wird, sollten Sie das Verbindungspooling vorübergehend deaktivieren. Dies kann bei webbasierten Anwendungen der Fall sein. Das Deaktivieren des Verbindungspools kann das Problem beheben, bis die Clientanwendung neu konfiguriert wurde, um Fehler besser zu behandeln.
- Wenn das Verbindungspooling deaktiviert ist, wird die SQL Server-Verbindung physisch abgemeldet, was dazu führt, dass der Server alle offenen Transaktionen zurückrollt.
Wenn das Verbindungspooling aktiviert ist und SQL Server 2000 der Zielserver ist, sollten Sie versuchen, den Clientcomputer auf MDAC 2.6 oder höher zu aktualisieren. Diese Version von MDAC fügt dem OLE DB-Provider und dem ODBC-Treiber Code hinzu und setzt die Verbindung vor der Wiederverwendung zurück. - Beachten Sie, dass die Verbindung nicht zurückgesetzt wird, bis sie wiederverwendet wird. Wenn ein Benutzer eine Transaktion öffnet und die Verbindung zum Pool freigibt, kann sie mehrere Sekunden lang nicht wiederverwendet werden.
Wenn die Hauptursache des Problems ineffiziente SQL-Anweisungen sind, die zu einem Zeitablauf der Abfragen führen, dann stimmen Sie die entsprechenden SQL-Anweisungen ab.
Ein Problem vieler SQL Server-Benutzer ist, dass Anwendungen nicht alle Ergebniszeilen in ihrer vollständigen Form abrufen. Es gibt eine einfache, einstufige Lösung für dieses Problem:
- Wenn eine Abfrage an den Server gesendet wurde, müssen alle Anwendungen in der Lage sein, alle Ergebniszeilen bis zur Fertigstellung abzurufen. Wenn dies nicht geschieht, können Sperren auf Tabellen beibehalten werden, was zur Blockierung für andere Benutzer führt.
Ein weiteres bekanntes Blockierungsproblem für SQL Server ist das Zurückrollen von Daten in Sitzungen. Wenn eine Datenänderungsabfrage zurückgesetzt werden muss, kann dies eine erhebliche Zeit in Anspruch nehmen. Häufig können Datenänderungsanfragen nicht schneller zurückgesetzt werden als die ursprünglich angewendeten Änderungen. Angesichts der Bedeutung des Rollbacks markiert SQL Server die Sitzung in einem Rollback-Zustand, sodass sie nicht als Deadlock-Opfer oder „beendet“ ausgewählt werden kann. In diesem Fall wird der ROLLBACK-Status in der Statusspalte von sysprocesses angezeigt. Dies erscheint auch in der Ausgabe von sp_who.
- Leider gibt es keine sichere Lösung für dieses Problem. Sie müssen auf die Sitzung warten, um das Zurückrollen der implementierten Änderungen zu beenden.
Verwaiste Sitzungen sind ein häufiges Blockierungsproblem. Wenn die Client-Workstation neu gestartet wird oder die Client-Anwendung abstürzt, wird die Netzwerksitzung zum Server nicht unbedingt sofort abgebrochen. Soweit es dem Server bekannt ist, kann der Client immer noch vorhanden sein, und alle Sperren bleiben erhalten.
- Wenn die Clientanwendung in diesem Fall die Verbindung getrennt hat, ohne ihre Ressourcen zu bereinigen, verwenden Sie den Befehl KILL, um die Sitzung zu beenden.
Auswahl des richtigen SQL Server-Blockchain-Tools
SolarWinds Datenbank Performance Analyzer (DPA)
SolarWinds Database Performance Analyzer (DPA) ist eine Datenbankverwaltungssoftware, die für die Überwachung, Analyse und Optimierung der SQL-Abfrageleistung entwickelt wurde. Es ist ein hoch skalierbares, benutzerfreundliches Tool mit einer Reihe von anspruchsvollen Funktionen. Es ermöglicht eine einfache Einarbeitung und Kontexteinstellung und bietet eine konsistente Navigation, sodass nur minimale Schulung oder Erfahrung mit IT-Software erforderlich ist.
DPA gibt Ihnen Einblick in das, was blockiert wird, und die Ursache der Blockierung, und es liefert Ihnen die Informationen, die Sie zum Optimieren Ihrer Indizes, Datenbank und Abfragen benötigen. Es warnt Sie vor SQL-Deadlocks und identifiziert fehlerhafte Prozesse, so dass Sie vollständige Transparenz erhalten. DPA erfasst Deadlock-Daten als XML-Dateien und ermöglicht es Ihnen, diese herunterzuladen, damit Sie sie im SQL Server Management Studio anzeigen können. Dies verschafft Ihnen eine umfassende und leicht zugängliche Sichtbarkeit.
Sie können auch leicht die Root-Blocker und die kumulativen Auswirkungen auf die Reaktionszeiten identifizieren. DPA berechnet die Gesamtauswirkungen eines Root-Blocker entlang des Blockchain, sodass Sie Ihre Optimierungsbemühungen auf die Anfragen konzentrieren können, die die unmittelbarste Aufmerksamkeit erfordern. Das spart Zeit und hilft Ihnen, Blockierungen schneller zu beseitigen. DPA bietet vollständige Transparenz, sodass Sie Abfragen und Programme sehen können, die auf Blocker warten und Transaktionen verlangsamen.
Eine der besten Funktionen von DPA ist die erweiterte Antwortzeitanalyse, mit der Sie SQL-Anweisungen schnell analysieren können. Diese Funktion gibt Ihnen Einblicke in die Warte- und Reaktionszeit und hilft Ihnen, problematische SQL-Abfragen und Indexempfehlungen so schnell wie möglich zu identifizieren. DPA sammelt die mit Ihren SQL-Anweisungen verknüpften Daten und ordnet sie nach Antwortzeit.
Diese Daten werden in Grafiken und Diagrammen dargestellt und lassen sich so auf einen Blick interpretieren. Die Diagramme sind farbkodiert nach der Art der Wartezeit, und DPA bietet Ihnen die Möglichkeit, detailliertere Informationen und mögliche Lösungsstrategien zu sehen.
Zu den weiteren DPA-Funktionen gehören:
- Echtzeit-Überwachung von SQL Server
- Leistungsoptimierung des SQL-Datenbankservers
- Funktionen zur SQL-Leistungsanalyse
- SQL Server-Indizierung
- Optimierung der SQL Server-Speichernutzung
- MySQL-Berichtslösung
- MySQL-Abfrageanalysator und -optimierer
Diese Liste ist keineswegs erschöpfend. DPA ist eines der effektivsten Tools zur Behebung von Blockierungs- und Sperrproblemen. Um das Beste aus der Software zu machen, befolgen Sie die folgenden Richtlinien.
SolarWinds® SQL Sentry
SQL Sentry wurde entwickelt, um Ihnen dabei zu helfen, Leistungsprobleme leichter anzugehen und SQL Server effektiv auf Sperrprobleme zu überwachen. Es kann schwierig sein, die Probleme zu beheben, nachdem sie aufgetreten sind. SQL Sentry ermöglicht es Benutzern jedoch, die Leistung über einen bestimmten Zeitraum zu überprüfen und zu erkennen, wann das Problem aufgetreten ist, und sofort zu beheben.
Zu den weiteren Funktionen von SQL Sentry gehören:
- Top SQL- und SQL-Server-Blockierungsansichten – Sehen Sie sich bestimmte Zeitpunkte an, welche Abfragen ausgeführt werden, und überprüfen Sie die historische Blockierung
- Baselining – Legen Sie individuelle Basislinien fest und vergleichen Sie das, was auf dem Bildschirm angezeigt wird, mit dem, was erwartet wird
- Schreibgeschützte Ansichten – Gewähren Sie Benutzern schreibgeschützten Zugriff, damit Support-Teams auch Dashboards überprüfen können
Eine kostenlose 14-tägige Testversion von SQL Sentry steht zum Download bereit.
So finden Sie Blockierungen in SQL Server mit SolarWinds DPA
SolarWinds DPA überwacht kontinuierlich SQL Server-Instanzen und erfasst Daten zu Sperrproblemen. Blockierungsprobleme äußern sich normalerweise in Sitzungen und SQL-Anweisungen, die hohe Wartezeiten für LCK_M-Wartetypen akkumulieren. Sie können DPA verwenden, um Details zu Blockierungssitzungen und Wartesitzungen zu sammeln – dies umfasst Informationen über die ausgeführten SQL-Anweisungen und die beim Blockieren anderer Sitzungen akkumulierten Wartezeiten sowie die blockierten SQL-Anweisungen und die damit verbundenen Wartezeiten.
Wenn Sie versuchen, Blockierungsprobleme zu beheben, können Sie DPA verwenden, um wichtige Informationen zu erfassen, indem Sie die folgenden Schritte ausführen:
- Identifizieren Sie, wo die Blockierungsinstanz aufgetreten ist, und bohren Sie den Tag und den Zeitrahmen.
- Gehen Sie zur Registerkarte „Blocker“, wo Sie die Session/SPID oben in der Blockierungsstruktur sehen. Wenn Sie mehrere Top-Level-Blocker sehen, bedeutet dies, dass während des ausgewählten Zeitraums mehr als eine Sitzung als Blocker fungiert hat. Der Link „Details“ für die Sperrsitzungen zeigt die obersten SQL-Anweisungen an, die ausgeführt wurden, während andere Sitzungen blockiert wurden.
Wenn für eine oder mehrere Anweisungen zu lange Wartezeiten aufgetreten sind, sollten Sie die betroffenen Anweisungen anpassen. Dies kann dazu beitragen, die Zeit zu verkürzen, die die Sitzung an den Sperren festhält. Je schneller die Anweisungen ausgeführt werden können, desto früher werden die Sperren durch die Sperrsitzung freigegeben. Es ist auch möglich, dass die Sperrsitzung im Leerlauf ist, was bedeutet, dass sie während des gewählten Zeitrahmens geschlafen hat. Möglicherweise müssen Sie die Sitzung beenden, um die Sperren freizugeben. - Erweitern Sie schließlich die Hauptsperrsitzung und Sie werden eine Liste aller Sitzungen sehen, die während des Zeitrahmens darauf warten. Der SQL-Link neben jeder Sitzung zeigt die Anweisungen an, die versuchen, Sperren zu erwerben, die von der Hauptblockierungssitzung gehalten werden.
Erste Schritte mit der SQL Server-Blockchain-Analyse
Sperren und Blockierungen sind eine normale (und wünschenswerte) Funktionalität von SQL Server-Datenbanken. Leider kann es eine Herausforderung sein, Blockaden davon abzuhalten, die Leistung zu beeinträchtigen – und die Ursache einer Blockade zu ermitteln. Um Zeit und Frustration zu sparen, empfehle ich SolarWinds DPA zu verwenden, um Blockchain Probleme im SQL Server zu finden und zu lösen.
Dieses Tool wurde entwickelt, um mit Ihrem Unternehmen zu skalieren, so dass es für kleine und große Unternehmen geeignet ist. Mit minimalem Training können Sie sofort loslegen; es gibt sogar einezum Download.