Infrastructure at your Service

Stéphane Haby

SQL Server Extended Event: Eine Deadlock-Event aus der “system_health” Überwachung

Wie Sie vielleicht wissen, gibt es eine Überwachung über Extended Events: system_health.
Kürzlich fragt mich ein Kunde an, um ein zufälliges Deadlock-Problem zu analysieren…


Ich hatte im September in Lausanne einen Vortrag zum Thema Performance-Monitoring und Extended Events präsentiert…
Viele Leute kennen das nicht, dass in den system_health des SQL Servers die Informationen zu den Deadlock-Events abgelegt sind.

Wo sind die „system_health“ Extended Event?

xe_deadlock_00
Wie Sie sehen können, unter Management – Extended Events finden wir den „system_health mit 2 Einträgen:

  • Package0.event_file: Das Ereignisdateiziel ist ein Ziel, das den vollständigen Puffer auf  den Datenträger schreibt. Der Msdn Link hier
  • Package0.ring_buffer: Das Ringpufferziel speichert kurzzeitig Ereignisdaten im Arbeitsspeicher. Der Msdn Link hier

Diese Überwachung ist direkt nach der Installatio n aktiv.

Erste Analyse

Ein Doppelklick auf das Ereignisdateiziel, öffnet ein Fenster mit alle Events.
In meinem Beispiel, haben wir 7655 Events.
xe_deadlock_01
Um die Deadlock-Events zu sehen, erstelle ich ein Filter auf den Event: xml_deadlock_report.
Jetzt habe ich nur noch 541 Einträge in meiner Liste für den Event „xml_deadlock_report“.
Sie haben bemerkt dass für jeden Event, 2 Tabs vorhanden sind:

  • Details
  • Deadlock

Das „Detail“ Tab gibt Auskunft in einem XML File zu diesem Event.
xe_deadlock_05
Das „DeadLock“ Tab zeigt ein Schema
xe_deadlock_03
Und auf meiner Seite, ist das einfacher zu verstehen…

Zweite Analyse

xe_deadlock_06
Wir haben zwei Prozesse mit der id 68 und 75.
SQL Server stoppt den Prozess mit der id 68… Warum?
SQL Server hat eine System-Task REQUEST_FOR_DEADLOCK_SEARCH die alle fünf Sekunden, überwacht ob irgendwelche Deadlocks auftreten. Wenn er eine Deadlock findet, beendet diese System-Task ein von den zwei aktuelle Prozesse.
Die Frage ist, welscher Prozess wird beendet?
Normalerweise, der System-Task beendet den Prozess der den wenige „Log used“ hat.
In meinem Fall, sind die „Log used“ gleich (864), dann beendet er den zweiten Prozess!

In diesem Schema analysieren wir auch noch die „locks“ Typen.

xe_deadlock_08

Wir sehen das es ein shared (S) lock gibt ,ein exclusive(X) lock angefordert oder zugeteilt worden ist und auch noch ein shared (S) lock angefordert wurde.

xe_deadlock_09

Source: https://technet.microsoft.com/en-us/library/ms186396%28v=sql.105%29.aspx

Ich denke, dass ist ein „Lese-Schreib“ Deadlock zwischen einer Anweisung die am Lesen ist und eine andere Anweisung die am Schreiben ist.
Aber in diesem Schema, sehe ich die Anfragen die der den „Deadlock“ generiert, hat nicht.

Dritte Analyse

Meine letzte Analyse ist mit den XML File.
xe_deadlock_07
Der Deadlock bezieht sich auf eine SELECT Anfragen.

Was können wir verbessern?

Die erste Idee ist, die NO LOCK Option. Mit dieser, den SELECT zu erstellen, das habe ich in anderen Blogs gesehen.
In diesem Fall, eines Deadlock mit SELECT, ist diese Option nie die Lösung! 🙁
Da müsste man noch weiter nachforschen…

Der SQL Server hält ein shared (S) lock bis zum Ende der Transaktion mit sein Standardisolationsstufe (READ COMMITTED).
Um dieses Problem zu beheben, ist mein Vorschlag, die Isolationsstufe zu ändern, auf READ COMMITTED SNAPSHOT oder SNAPSHOT.
In dieser zeilenbasierten Isolationsstufen, können die Leser nicht Lock verwenden und müssen stattdessen die zeilenbasiert Versionen für die Isolierung verwenden.
Und in der Theorie, keine shared (S) locks bedeutet keine Deadlock.

Um zum Schluss das Beste: Um diese Performance Probleme besser zu verstehen, besuchen Sie unseren neuen „Workshop SQL Server Performance Tuning“! 😉

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant