SQL Server-Deadlock-Überwachungstool

Deadlocks treten in SQL Server auf, wenn zwei oder mehr Transaktionen oder Sammlungen von SQL-Abfragen sich gegenseitig durch eine zyklische Abhängigkeit blockieren. Dies geschieht, wenn eine Transaktion Ressourcen blockiert, die die andere Transaktion ebenfalls benötigt, was zu einer SQL Server-Sperre für eine Tabelle führt. Die den Deadlock verursachende SQL-Abfrage kann nicht abgeschlossen werden, bis die Sperre aufgehoben wurde, ebenso wie die anderen vom Deadlock betroffenen Abfragen.

Wenn Sie eine Benachrichtigung über einen SQL Server-Deadlock erhalten, werden Sie sehen, dass eine Transaktion als „Victim“ und eine als „Survivor“ bezeichnet wird. Die Victim-Transaktion ist die Sitzung, für die ein Rollback ausgeführt wird. Sie wird von SQL Server automatisch auf Basis der Deadlock-Priorität und der Rollback-Kosten ausgewählt.

Häufige Ursachen für Deadlocks sind ein mangelhaftes Datenbankdesign, eine fehlende Indizierung, ungeeignete Isolationsebenen und schlecht geschriebene Abfragen. Immer, wenn eine SQL Server-Sperre für eine Tabelle auftritt, werden die Vorgänge verzögert oder sogar ganz gestoppt. Deadlocks können letztendlich sogar dazu führen, dass die Verarbeitung in Ihrer Datenbank-Engine gänzlich zum Erliegen kommt.

Das Lösen von SQL Server-Deadlocks kann ein frustrierender, zeitaufwändiger Prozess sein, besonders wenn Sie es mit mehreren komplexen Server-Deadlock-Szenarien zu tun haben. Eine typische Situation ist die folgende: Ein gespeicherter Vorgang hat die Ressource A (z. B. ein Objekt, eine Seite oder eine Zeile) mit einer Sperre belegt und wartet auf einen anderen Vorgang, bevor er auf Ressource B zugreifen kann. Dieser Vorgang hat jedoch die Ressource B zuerst mit einer Sperre belegt und wartet nun ebenfalls darauf, dass der erste Vorgang seine Sperre der Ressource A freigibt.

Um diese Abwärtsspirale zu durchbrechen, müssen Sie die Quelle der Sperre schnell identifizieren und Maßnahmen treffen.

Je nach Situation gibt es mehrere Möglichkeiten, um SQL Server-Deadlocks zu beheben. SQL Server kann Deadlocks mithilfe der Deadlock-Überwachung automatisch erkennen. Sobald ein Deadlock erkannt wird, wählt SQL Server wie oben beschrieben automatisch das „Opfer“ und führt einen Rollback für diese Transaktion aus. Anschließend hängt es von Ihnen und Ihrem IT-Administrationsteam ab, wie Sie Deadlocks in SQL Server beheben.

Meist werden SQL Server-Deadlocks mit einer der folgenden Methoden behoben:

• Covering-Indizes: Bei einem Covering-Index wird die Lesezeichensuche vollständig entfernt und so das Risiko von Deadlocks durch Lesezeichensuchen reduziert.
• Fremdschlüssel-Indizes: Durch das Erstellen von Indizes, die Ihren Fremdschlüsselspalten entsprechen, werden SQL Server-Deadlocks durch kaskadierende referenzielle Integrität reduziert. Ohne Fremdschlüssel-Index dauern kaskadierende Aktionen länger und erhöhen die Deadlock-Wahrscheinlichkeit.
• Reihenfolge des Objektzugriffs: Um Deadlocks zu Testzwecken zu erzeugen, greifen Sie auf Datenbankobjekte in einer anderen Reihenfolge zu. Vermeiden Sie diese Deadlocks ansonsten, indem Sie Transaktionen so kurz wie möglich und Zugriffsobjekte in der gleichen logischen Reihenfolge halten.
• Transaktionsisolationsebenen: Die standardmäßige Transaktionsisolationsebene „READ COMMITTED“ in SQL Server kann zu Deadlocks zwischen Lese- und Schreibvorgängen für Daten führen. Vermeiden Sie dies, indem Sie „COMMITTED SNAPSHOT“ oder „SNAPSHOT“ nutzen: Hierbei sperrt ein Lesevorgang keine Objekte, sondern nutzt Zeilenversionen für Isolierungen.
• Deadlock-Priorität: Indem Sie die Priorität einer Transaktion als „High“, „Normal (Default)“ oder „Low“ festlegen, sorgen Sie dafür, dass laufende Sitzungen im Fall eines SQL Server-Deadlocks weiter verarbeitet werden.
• Anwendungsfehlerbehandlung: Mithilfe von TRY...CATCH-Logik können Sie die Deadlock-Nummer erfassen. Außerdem können Sie die Zahl der zulässigen Wiederholungsversuche auf eine feste Zahl festlegen und so die Wahrscheinlichkeit einer Endlosschleife reduzieren.

Sie können Deadlocks in SQL Server am besten vermeiden, indem Sie ein SQL-Deadlock-Überwachungstool einsetzen. Ein solches Tool erkennt und behebt Deadlocks in SQL Server mithilfe von Einblicken und Funktionen, die die Deadlock-Überwachung verbessern.

Auch ohne Deadlock-Überwachungstool gibt es Methoden, um Deadlocks in SQL Server zu erkennen. Sie erfolgen über SQL Server selbst:

• Trace-Flags: SQL Server kann über die Trace-Flags 1204 oder 1222 die Deadlock-Überwachung durchführen. Trace-Flags müssen dauerhaft ausgeführt werden, was es schwierig macht, Deadlocks proaktiv zu erkennen.
• Leistungsindikator: Ein Leistungsindikator zeigt die Zahl der Deadlocks in SQL Server an, aber keine Detailinformationen zu den beteiligten Sitzungstransaktionen.
• SQL Server-Profiler: Hierbei handelt es sich um die serverseitige Ablaufverfolgung, die Details zu SQL Server-Deadlocks erfassen und als XML-Diagramm anzeigen kann. Auch diese Methode muss dauerhaft ausgeführt werden und bietet nicht die Möglichkeit, Metriken proaktiv zu erfassen.
• Erweiterte Ereignissitzung: SQL Server führt standardmäßig eine erweiterte Ereignisverfolgung aus, doch diese Methode ist zeitaufwändig, fehleranfällig und erfordert manuellen Aufwand.

Diese Methoden können zwar etwas Abhilfe schaffen, doch sie sind nicht so schnell, einfach und effizient wie die Nutzung eines Deadlock-Überwachungstools zum Vermeiden von Deadlocks in SQL Server. Mit einem Tool zur Deadlock-Erkennung wie SolarWinds SQL Sentry können Sie aufgetretene Deadlocks schnell erkennen, ihre zugrunde liegende Ursache nachvollziehen und überblicken, mit welchen Maßnahmen Sie das wiederholte Auftreten von Deadlocks vermeiden können.

Die Deadlock-Analysefunktionen in SQL Sentry stellen Ihnen detaillierte Metriken bereit, mit denen Sie Deadlocks lösen können. So benötigen Sie weniger Zeit für die Wiederherstellung der Spitzenleistung Ihrer Datenbank. Wichtige Metriken sind:

• SQL-Server, auf dem der Deadlock aufgetreten ist
• Zeitpunkt, zu dem der Datenbank-Deadlock aufgetreten ist
• SPID der betroffenen Komponente – die Sitzungsprozess-ID der vom Deadlock betroffenen Komponente
• Host der betroffenen Komponente – Workstation, die zum Thread der betroffenen Komponente gehört
• Anwendung der betroffenen Komponente – Name der Anwendung, die zum Thread der betroffenen Komponente gehört
• Datenbank der betroffenen Komponente
• Textdaten der betroffenen Komponente
• Deadlock-XML – erfasstes Deadlock-XML

Der Bereich mit den Sperrdetails schlüsselt den Deadlock nach bestimmten Sperrtypen auf, einschließlich der an jeder Sperre beteiligten Eigentümer und Wartenden. Unter anderem werden die folgenden Informationen angezeigt:

• SPID – Sitzungs-Prozess-ID des verknüpften Eigentümers/Wartenden
• Plan – öffnet eine Plan Explorer-Sitzung, um den zugehörigen Abfrageplan anzuzeigen
• Host
• Anwendung
• Datenbank
• Protokollnutzung – Umfang des vom Prozess verwendeten Protokollspeichers
• Deadlock-Priorität – die Standardpriorität ist null (0) oder „normal“
• Wartezeit – Zeit in Millisekunden, die auf die Ressource gewartet wird
• Uhrzeit des Transaktionsbeginns
• Uhrzeit des Beginns des letzten Batches
• Uhrzeit des Abschlusses des letzten Batches
• Modus/Typ der Ressourcensperre
• Status der Aufgabe
• Aktueller Isolationsgrad der Transaktion
• Anmeldename für die Sitzung

SolarWinds^® SQL Sentry erfasst und untersucht Deadlocks in SQL Server schnell und detailgenau. Mit den SQL-Deadlock-Überwachungsfunktionen des Tools können Sie Deadlocks erkennen und beheben, bevor sie Ihren Datenbankbetrieb beeinträchtigen. Sie erhalten handlungsrelevante Einblicke, mit denen Sie ähnliche Deadlocks in Zukunft verhindern können.

SQL Sentry verwendet eine modifizierte erweiterte Ereignissitzung, um Überwachungsdetails zu erfassen. SQL Sentry zeigt erkannte Deadlocks unten auf der Registerkarte „Trends“ zusammen mit weiteren wichtigen SQL Server-Deadlock-Informationen an:

• Zeitpunkt des Auftretens des Deadlocks
• Zahl der am Deadlock beteiligten Sitzungen
• Beteiligte Objekte
• Computer- und Benutzername

Eine weitere SQL Sentry-Metrik ist der sogenannte „Victim Impact“, der anzeigt, wie lange eine durch einen Deadlock betroffene Transaktion ausgeführt wurde und Ressourcen beansprucht hat. Die Transaktionszeit eines SQL-Deadlock-„Opfers“ bedeutet eine Verschwendung von Ressourcen und ist daher ein guter Indikator dafür, welche Auswirkungen ein Deadlock auf die SQL-Server und Endnutzer hat.

Auf den weiteren Seiten neben der Registerkarte „Trends“ finden Sie zusätzliche Informationen und Aktionen zu SQL Server-Deadlocks. Sie können mit SQL Sentry beispielsweise nicht-standardmäßige Sitzungen über die Seite „Advanced Properties“ (Erweiterte Eigenschaften) konfigurieren. Die „Deadlocks“-Seite in SQL Sentry zeigt eine Zusammenfassung des Deadlocks mit den Zeitstempeln von „Victim“ und „Survivor“ sowie Objekten, Programmen und Zahl der Sitzungen an. Hier finden Sie auch Daten zu den vom Deadlock betroffenen Ressourcen einschließlich Indexname, Datenbank-ID und Sperr-ID.

Sie können mit SQL Sentry wichtige Metriken und Details exportieren und in SQL Server Management Studio (SSMS) nutzen. SSMS ist eine von Microsoft bereitgestellte Software, die eine Migration zu SQL Sentry und anderen Datenbanken ermöglicht. Durch die Unterstützung der SSMS-Integration kann SQL Sentry die Kommunikation verbessern und die Deadlock-Behebung optimieren. Sie können die Deadlock-Details auch über SQL Sentry als E-Mail versenden und die exportierte Datei als Anhang beifügen.

Nutzen Sie die vollständigen Funktionen von SQL Sentry zum Überwachen mehrerer Bereiche des SQL Server-Betriebs, um einen umfassenden Überblick über die Elemente zu erhalten, die sich auf die Datenbankleistung auswirken.