SQL Server & BI Blog by Andreas Wolter

(DE)
Diesmal soll es um etwas gehen, das Extended Events direkt von Haus aus besser und leichter verfügbar machen, als sämtliche Techniken zuvor:
Speichern von aufgetretenen Deadlock-Ereignissen, und das Analysieren der entsprechenden Deadlock-Graphen.

(EN)
This time it’s about one of the things that Extended Events do better and more easy than all techniques before:
Saving of deadlock-events that occurred and analyzing the respective deadlock-graph.

Deadlocks, die man hier erläutert finden kann, treten zwischen mindestens 2 Arbeits-Threads auf, oft aber sind mehr als 2 beteiligt.
- Ich wähle „Thread“ absichtlich, da Prozess nicht genau genug wäre. Meistens treten Deadlocks zwar zwischen verschiedenen Sessions/Prozessen auf, aber es kann sich auch ein Prozess, der auf parallelen Threads ausgeführt wird, selbst blockieren.
Die Fehlernummer für Deadlocks ist die sicherlich allen SQL Profis bekannte „1205“.

Deadlock, explained here, occur between at least 2 threads but often more than 2 are involved.
- I am using the term “thread” on purpose, since process would not be sufficiently accurate. Mostly Deadlocks in fact do happen between different sessions/processes, but it is also possible that a process running with parallel threads blocks itself.
The error number for deadlocks is “1205” – probably known to all SQL professionals.

Um Deadlocks zu Tracen gab es vor SQL Server 2008 nur die folgenden Möglichkeiten:

1. Eine SQL Trace mit Profiler oder auch ohne GUI mit Protokollierung in trc-Datei oder live in der GUI

2. Trace Flags 1204, 1205 oder 1222, welche in das Errorlog des SQL Servers protokollieren – „wunderbar“ besonders auf „ereignisreichen“ Servern, und denkbar schwierig für die Analyse.

Manch einer mag sich noch an diese grausigen Einträge im Error-Log erinnern:

For tracing deadlocks before SQL Server 2008 there were merely the  following ways:

1. A SQL Trace with Profiler or without GUI with logging in a trace-file or live inside the GUI

2. Trace Flags 1204, 1205 or 1222, which log into SQL Server’s errorlog – „great“ especially  on “eventful” servers.

Some may remember those horrible entries in the error log:

3. Event Notifications, welche die Daten per Service Broker Queue in XML-Format zur Verfügung stellen.
Hier findet sich ein ausführliches Beispiel mit Speicherung in Tabelle und automatischem Mail-Versand mit Info über das Event.

4. Ein WMI-Alert, welcher die Daten ebenfalls per XML bereitstellt und mit einem Auftrag oder einer Benachrichtigung gekoppelt diesen protokollieren bzw. darüber informieren kann.

 3. Event Notifications which provide the data via Service Broker Queue in XML-format.
Here you find an elaborate example with storing in table and automatic email with information about the event.

4. A WMI-Alert which also provides the data via XML und tied to a job can also log respectively inform about it.

All diese Ansätze, so unterschiedliche Vorteile sie auch haben mögen haben eines gemeinsam: Sie müssen erst eingerichtet werden. Im Zweifelsfalle also nachdem die ersten Deadlocks aufgetreten sind.

Seit SQL Server 2008 jedoch werden sämtliche Deadlocks von Hause aus protokolliert.

Das geschieht durch die automatisch laufende system_health Extended Event session.

Diese speichert neben Deadlocks noch weitere wichtige Server-Ereignis- und Fehler-Informationen ab. Hier finden sich Details dazu.

Seit SQL Server 2012 protokolliert die system_health session neben dem Memory-Ziel „Ring_Buffer“ auch in die Ereignisdatei, womit Deadlocks und weitere Fehler auch nach Serverneustart noch eine ganze Weile (bis zu mehreren Tagen) zur Verfügung stehen – bis der Rollover der 5 Dateien stattgefunden hat.

Für SQL Server 2008 Instanzen empfehle ich, das Datei-Ziel hinzuzufügen.

All those approaches, as different as their respective advantages may, have ine thing in common: They have to be set up. In case of doubt after the first deadlocks occurred.

But since SQL Server 2008 all deadlocks are being logged by default.

This is done by the automatically running system_health Extended Event session.

This, alongside deadlocks logs further important server events and error-information. Here you find details on it.

Since SQL Server 2012 the system_health session apart from the memory-target “ring_buffer” also logs into an event-file, so that deadlocks and other error stay available even after a server restart for quite a while (up to a few days) – until the rollover of the 5 files has occurred.

For SQL Server 2008 instances I recommend to add the file-target.

Wie kann man diese Daten auslesen?
Das geht per T-SQL relativ einfach, wenn man weiß, dass diese Daten über die DMV sys. dm_xe_session_targets verfügbar sind.
So liest man das Ring-Buffer-Ziel aus:

 How can you read-out that data?
This works quite easily va T-SQL once you know that this data is available via the DMV sys. dm_xe_session_targets.
This is how you read-out the ring_buffer-Target.

„Target_data“ ist XML-text gespeichert als unicode-text-Blob, welches man bei entsprechender Konvertierung zu XML sogar in SSMS direkt öffnen kann.
Um nur die Deadlock-Events dort heraus zu filtern, muss man die XQuery-Erweiterungen von T-SQL bemühen.

Das sieht dann vom Code her so aus:

 „Target_data“ is XML-text stored as unicode-text-blob which can even be opened direct in SSMS after converting to XML.
To filter out only the deadlocks you need to use the XQuery-extensions of T-SQL.
And this is how that looks like in code plus results:

Wenn man einen solchen Deadlock-Report in XML-Format in SSMS anklickt, bekommt man ihn in einem neuen Fenster als XML-Dokument angezeigt:

When klicking at one of those Deadlock-Report reports in XML-format in SSMS it will be opened in a new window as XML-document:

- im Bild habe ich zur Übersicht einige Knoten zugeklappt.

Wichtig hier sind: die victim-list, welche bei einem Multi-Victim-Deadlock dann je Victim eine Prozess-Id enthält, die man wiederum in der process-list näher untersuchen kann.

Im executionStack findet man dann denn ausgeführten SQL-Befehl und ggfl. sogar direkt den Prozedurnamen.

Darunter folgt in der resource-list eine Liste der beteiligten (gesperrten) Ressourcen.

- in the picture I collapsed some nodes for brevity.

Most important here are: the victim-list, which for a multi-victim-deadlock contains one process-id per victim, which again one can investigate more deeply in the process-list.

In the executionStack one can find the executes SQL-command and if applicable even the procedure name.

Below in the resource-list follows a list of all involved (blocked) resources.

All nodes are peppered with details such as row number, objectname, lock-mode, waiting time, spid and of course timestamps.

Wer hier direkt weiter ansetzen möchte, kann das XML natürlich weiter auseinandernehmen und einzelne Knoten versuchen relational in Spaltenform darzustellen.
Dafür muss man zunächst den Deadlock-Graphen extrahieren:

Who wants to start from here can of course take apart the XML and try to break individual nodes relationally into columns.
For that one has to first extract the deadlock-graph.

Von dort aus kann man leicht die einzelnen Knoten parsen.
An das Transaction Isolation Level kommt man z.B. mit der value-Methode, wenn man einmal weiß, auf welcher Ebene/in welchem Knoten des Graphen sie sich befindet:

From there one can parse the individual nodes easily.
The transaction isolation level for example can be reached with the value-method once you know  on which level/in which node in the graph it resides.

Auf die Weise kann man mit fast allen Attributen und Elementen verfahren.
Bei den Sperr-Informationen stößt man dabei jedoch an die Grenzen der statischen relationalen Darstellung, da dort je nach gesperrter Resource völlig andere Attribute auftauchen.

In this manner one can proceed with almost all attributes and elements.
For locking-information one will get to the limits of the static relational representation since depending on the locked resource there will be completely different attributes.

Das zweite Problem tritt dann auf, wenn man Multi-Victim-Deadlocks vor sich hat.

Potentiell kann es ja eine Unmenge an gesperrten und gekillten Victim-Prozessen geben, so dass man da kaum ein immer ausreichendes Schema aufbauen kann.

Hier ein Beispiel eines Multi-Victim-Deadlocks, dargestellt mit der XEvents GUI (Profiler beherrschte das, wie so vieles, nicht)

The second problem arises once you are dealing with a multi-victim-deadlock.

Potentially there can be a vast number of locked and killed victim-processes, so that one can hardly prepare an ever sufficient schema.

Here an example of a multi-victim-deadlock, displayed with the XEvents GUI (Profiler could not handle that as many other things)

Letzten Monat habe ich meinen SQL Deadlock-Collector & Parser bei codeplex frei downloadbar veröffentlicht.
- hier der direkte Link: SQL Deadlock Collector & Parser

- Hier zum Artikel mit ausführlicher Beschreibung:

freier Deadlock-Sammler & -Parser basierend auf Erweiterten Ereignissen

Dort habe ich mich so entschieden, immer die beiden ersten Prozesse gegenüberzustellen, was bei den meisten Deadlocks schon sehr weiterhilft.

Das sieht dann so aus:

Last month I published my SQL Deadlock-Collector & Parser freely downloadable at codeplex.
- here the direct link: : SQL Deadlock Collector & Parser

- here the article with detailed explanaition:

free Deadlock-Collector & -Parser based on Extended Events

There I decided always contrast the first two processes , which helps for most deadlocks a lot already.

This is how it looks like:

Wie kann man nun die ja ebenfalls von er system_health-session geschriebenen Event-Dateien einlesen?

Dafür gibt es die Systemfunktion sys.fn_xe_file_target_read_file.
Diese benötigt den Pfad zur xel-Datei und den Namen der ersten Datei, welche eingelesen werden soll.
Die Dateien liegen übrigens im Log-Verzeichnis der SQL Server Instanz.
Wenn man den Dateinamen nicht angibt, werden alle Dateien im Verzeichnis eingelesen.
Was die Funktion jedoch wirklich praktisch macht, ist, dass man bereits den Pfad mit einer Wildcard für den Dateinamen versehen kann, dass man den Dateinamen praktisch kaum noch benötigt.

So kann das dann aussehen:

How is it possible to read-in the eventfiles which are written to as well by the system_health-session?

For that the system-function sys.fn_xe_file_target_read_file is a provided.
It needs the path to the xel-file and the name of the first file which is to be read-in.
The files are located in the log-directory of the SQL Server instance by the way.
If the filename is not specified all files in the directory will be read-in.
What makes the function really handy is, that one can already arm the path with a wildcard, so that practically one does not even need the filename any more.

This is how that can look like:

Wer bereits den Deadlock-Collector zum Parsen und Speichern seiner Deadlocks verwendet, kann damit ebenfalls manuell Deadlocks von X-beliebigen Systemen in die SQL_Analysis_Data-Datenbank importieren.
Dafür verwendet man einfach die Prozedur Locking.ins_DeadLock in der Datenbank SQL_Analysis_Code mit dem Parameter @XESource.

Dieser ist standardmäßig auf die Verwendung des Ring_Buffers geschaltet.
Aber man kann auch einfach den Pfad + Dateinamen der zu importierenden xel-Datei übergeben:

Who is already using the Deadlock-Collector to parse and save ones deadlocks can also use it to import deadlocks from any system at will into the SQL_Analysis_Data-database.

For that simply use the procedure Locking.ins_DeadLock in the database SQL_Analysis_Code with the parameter @XESource.

It is set to use the ring_buffer by default.
But one can simply pass in the path + filename of the xel-file to import:

Anschließend kann man die Deadlocks wie hier beschrieben über Sichten oder Ad-Hoc T-SQL bequem halbwegs in relationaler Struktur analysieren.

Afterwards one can comfortably analyse the deadlocks via views or ad-hoc T-SQL in a half-way relational structure as wie described here.

Hinweis:

Note:

It is possible that deadlocks occur which do not appear in the system_health-session targets. Neither in the ring_buffer not in the filetarget.
This happens when the deadlock-graph is too big for the buffer-memory.

In order to make sure that larger deadlock graphs are also captured, I recommend to increase the maximal work memory of the system_health session.

Und wer jetzt immer noch auf Profiler & SQL Trace setzt (seit SQL Server 2012 aus gutem Grunde deprecated), hier noch ein Tipp:
So sieht ein MultiVictim-Deadlock im Profiler aus:

And who now is still using Profiler & SQL trace (deprecated since SQL Server 2012 for good reasons), here a tip:
This is how a multi-victim-deadlock looks in Profiler:

Das sieht eher wie ein „Multi-Winner-Deadlock“ aus ;-)

Und so stellt sich dieser in der XEvents GUI dar:

Looks more like a „multi-winner-deadlock” ;-)

And this is how the XEvents GUI displays it:

(en)
Today I redeemed a promise made long ago: To provide the deadlock-collector framework, which I developed for use at my customers’, as a Codeplex project.

Until now I have only given it out to attendees of my SQL Server Master-Classes and a few fellow Microsoft Certified Masters. From today on it is freely available to everyone.

(de)
Heute habe ich ein Versprechen eingelöst, das ich vor langer Zeit gemacht habe: Und zwar das Framework für den Deadlock-Collector, das ich zum Einsatz bei meinen Kunden entwickelt habe, als Codeplex-Projekt bereitzustellen.

Bisher habe ich es nur an Teilnehmer meiner SQL Server Master-Classes und einige Microsoft Certified Master-Kollegen herausgegeben. Ab heute ist es für jeden frei verfügbar.

If you just want to get it real quick, here is the link to the Codeplex project where you can download the necessary objects:

>> sqldeadlockcollector.codeplex.com <<

For more information on how to set up the solution and what kind of analysis it enables read below.

Wenn Sie ihn einfach schnell haben möchten, ist hier der Link zum Codeplex-Projekt, wo Sie die nötigen Objekte herunterladen können:

>> sqldeadlockcollector.codeplex.com <<

Weitere Informationen dazu, wie man die Lösung aufstellt und welche Art von Analyse sie ermöglicht, finden Sie im Folgenden.

Contents

1. Description
2. Technical Implementation
3. Supported SQL Server Versions & Editions
4. Setup High-level
5. Setup the Jobs
6. Integration of the Reports
7. Usage for Analysis
8. The Reports
9. Availability
10. Notes & Recommendations
11. Call to Action: Quick Poll

 Inhalte

1. Beschreibung
2. Technische Umsetzung
3. Unterstützte SQL Server Versionen & Editionen
4. Setup High-level
5. Setup der Aufträge
6. Integration der Berichten
7. Nutzung zur Analyse
8. Die Berichte
9. Verfügbarkeit
10. Hinweise & Empfehlungen
11. Call to Action: Kurze Umfrage

Description

The SQL Server Deadlock-Collector collects information which can be read out from the ring buffer in the memory of the SQL Server, and stores this information in a database-table.

Aloof from the pure deadlock graph, the data is parsed, and the first 2 respective processes of the deadlock graph are contrasted in 2 columns, respectively, with information such as “process ID, SQL/procedure text, host name, application name” etc. (procedure name is only available from SQL Server 2012 onwards because of incomplete xml in the former version).

Beschreibung

Der SQL Server Deadlock-Collector sammelt Informationen, die aus dem Ring-Buffer im Speicher des SQL Server ausgelesen werden können und speichert diese Informationen in einer Datenbanktabelle.

Abseits vom reinen Deadlock-Graphen werden die Daten zerlegt und die ersten 2 jeweiligen Prozesse des Deadlock-Graphs werden in jeweils 2 Spalten gegenübergestellt, mit Informationen wie z.B. „Prozess-ID, SQL/Prozedur Text, Hostname, Anwendungsname“ etc. (Prozedur-Name ist aufgrund unvollständigem xml in der vorherigen Version erst ab SQL Server 2012 verfügbar).

This way, one obtains a tabular representation of the deadlock processes. Since there may be more than 2 involved processes, the complete graph is always also present. (red in the image below)

Auf diese Weise erhält man eine tabellarische Darstellung der Deadlock-Prozesse. Da es mehr als 2 beteiligte Prozesse geben kann, ist der komplette Graph auch immer präsent. (rot in der Abbildung unten)

With the aid of a few sample reports, administrators will obtain a quick overview on the frequency of the occurring deadlocks in the respective databases and be able to pass on the data for further analysis to the developers or external analysis even by simply providing a copy/backup of the database.

Developers will thus obtain a valuable and quickly interpretable version of the occurring deadlock combination and be able to implement prevention strategies with this information.

Mithilfe einiger Beispielberichte erhalten Administratoren eine schnelle Übersicht über die Häufigkeit der auftretenden Deadlocks in den jeweiligen Datenbanken, und können die Daten darüber sogar durch das einfache Bereitstellen einer Kopie/eines Backups der Datenbank an die Entwickler oder externe Analysten weitergeben.

Entwickler erhalten damit eine wertvolle und schnell interpretierbare Version der auftretenden Deadlock-Kombinationen, und können mit diesen Informationen Vermeidungsstrategien umsetzen.

Technical Implementation

 The complete SQL Deadlock Collector & Parser -solution consists of 3 (small) databases, a SQL Server Agent job and 3 example Reporting Services Reports for Management Studio integration.

Through the utilization of the information already existing in memory by the Extended Events system_health-session, the SQL Deadlock Collector & Parser is absolutely lightweight and has scarcely any impact on a productive system. Solely at execution time of the data collection job (which is configurable at free will, every 6-24 hours are common), minimal resources are required.

Technische Umsetzung

Die komplette SQL Deadlock Collector & Parser – Lösung besteht aus 3 (kleinen) Datenbanken, einem SQL Server Agent Auftrag und 3 Beispiel- Reporting Services Berichten Management Studio Integration.

Durch die Nutzung der durch die system_health-Extended Events Session bereits im Arbeitsspeicher existierenden Informationen ist der SQL Deadlock Collector & Parser absolut leichtgewichtig und hat kaum eine Auswirkung auf ein produktives System. Lediglich zur Ausführungszeit des Datensammlungsauftrags (der frei konfigurierbar ist, alle 6-24 Stunden sind dabei gängig) werden minimale Ressourcen erfordert.

Supported SQL Server Versions & Editions

The SQL Deadlock Collector & Parser is designed to run on all SQL Server versions and editions from SQL Server 2008 SP2 onwards.

The provided example reports for Management Studio Integration only work with Management Studio 2012 onwards. Since SSMS is available in a free Express Edition, being mostly backwards compatible to SQL Server 2008 / R2, this is a minor constraint.

Here you can get SSMS Express in Version 2014:

www.microsoft.com/en-US/download/details.aspx?id=42299

Unterstützte SQL Server Versionen & Editionen

Der SQL Deadlock Collector & Parser ist so ausgelegt, dass er auf allen SQL Server Versionen und Ausgaben ab SQL Server 2008 SP2 läuft.

Die bereitgestellten Beispielberichte für Management Studio Integration funktionieren nur mit Management Studio 2012 und höher. Da SSMS als kostenlose Express Edition verfügbar ist, und dabei rückwärts kompatibel mit SQL Server 2008 / R2 ist, ist dies eine geringe Einschränkung.

Hier können Sie die SSMS Express als Version 2014 erhalten:

www.microsoft.com/de-DE/download/details.aspx?id=42299

Setup High-level

The SQL Deadlock Collector & Parser is available in the form of backup files of the respective databases which are 3 in total:

• SQL_Analysis_Data
• SQL_Analysis_Code
• SQL_Analysis_Reporting

As can be seen by means of the denominations, in this relatively small project, too, it is being proceeded according to proven Sarpedon Quality Lab methodology of separating data, code and reporting. Thus, updates are being facilitated. 

The 3 databases must be set up on the same server.

 Setup High-level

Der SQL Deadlock Collector & Parser ist in Form von Backup-Dateien der jeweiligen Datenbanken verfügbar, insgesamt 3:

• SQL_Analysis_Data
• SQL_Analysis_Code
• SQL_Analysis_Reporting

Wie anhand der Bezeichnungen zu erkennen ist, wird auch in diesem relativ kleinen Projekt nach bewährter Sarpedon Quality Lab Methodik der Trennung von Daten, Code und Reporting vorgegangen. So werden Updates erleichtert.

Die 3 Datenbanken müssen auf demselben Server eingerichtet werden.

Setup instructions:

1. Recover the 3 databases in the respective version (2008 or 2012) onto your Server
1. The “2008”-Version targets all Systems from SQL Server 2008 SP2 up to SQL Server 2008 R2 SP2
2. The “2012”-Version targets all Systems from SQL Server 2012 RTM CU3 up to SQL Server 2014 RTM CU6
3. All databases have to reside on the same monitored SQL Server Instance. If multiple Instances are target of collection, the deadlock collector’s databases and jobs have to be installed on each single instance. Multi-Instance-collection is not implemented
2. Set up the collection Job
3. Add a schedule for the collection
4. Repeat the same for the maintenance job that removes old collected deadlocks after a specified timeframe
5. Optional: integrate sample custom reports in SSMS for Database ”SQL_Analysis_Reporting”

If multiple Instances are to be monitored, repeat each step for each instance

 Setup Anweisungen:

1. Stellen Sie die 3 Datenbanken in der jeweiligen Version (2008 oder 2012) auf Ihrem Server wieder her
1. Die “2008”-Version ist für alle Systeme ab SQL Server 2008 SP2 bis zum SQL Server 2008 R2 SP2 da
2. Die “2012”-Version ist für alle Systeme ab SQL Server 2012 RTM CU3 bis zum SQL Server 2014 RTM CU6 da
3. Alle Datenbanken müssen auf derselben überwachten SQL Server Instanz liegen. Wenn mehrere Instanzen Ziel der Datenerfassung sind, müssen die Datenbanken und Aufträge des Deadlock Collectors auf jeder einzelnen Instanz installiert werden. Multi-Instance-Collection ist nicht implementiert
2. Richten Sie den Sammler-Auftrag ein
3. Fügen Sie einen Zeitplan für die Datenerfassung hinzu
4. Wiederholen Sie das gleiche für den Maintenance Job, der alte erfasste Deadlocks nach einem festgesetzten Zeitrahmen entfernt
5. Optional: Integrieren der Beispiel-Custom Berichte in SSMS für die Datenbank ”SQL_Analysis_Reporting”

Wenn mehrere Instanzen überwacht werden sollen, wiederholen Sie alle Schritte je Instanz

Setup the Jobs

Subsequently, two SQL Server Agent Jobs should be installed which will be configured as follows:

Einrichten der Jobs

Danach sollten zwei SQL Server Agent Aufträge eingerichtet werden, die wie folgt konfiguriert werden:

Please note that the instructions in bold must be used exactly as stated above; all others are suggestions and can be changed to your according naming styles.

In addition to that, set up a time schedule (as always I recommend a dedicated but shared schedule). For instance, every 6-12 hours, depending on the number of errors in the system_health session which works in FIFO-mode.

Bitte beachten Sie, dass die fettgedruckten Anweisungen genau wie oben angegeben verwendet werden müssen; alle anderen sind Vorschläge und können Ihren Namensrichtlinien angepasst werden.

Stellen Sie zusätzlich dazu einen Zeitplan (ich empfehle wie immer einen dedizierten aber Shared Schedule). Zum Beispiel alle 6-12 Stunden, je nach Anzahl der Fehler in der system_health Session, die im FIFO-Modus arbeitet.

After installing the databases the first time, you will find a few rows of example deadlocks in the Date-Database – also accessible via the views from the Reporting-Database.
The sole purpose of those is to help understand how the SQL Deadlock Collector & Parser works.

Nachdem Sie die Datenbanken das erste Mal eingerichtet haben, werden Sie einige Zeilen an Beispiel-Deadlocks in der Data-Datenbank finden – ebenfalls erreichbar über die Sichten aus der Reporting-Datenbank.
Der alleinige Zweck dieser ist es, besser zu verstehen zu helfen, wie der SQL Deadlock Collector & Parser funktioniert.

You can delete those manually or by using the maintenance-procedure Locking.del_DeadLock as shown in the SQL Agent job-example.

Sie können diese manuell oder mithilfe der Wartungs-Prozedur Locking.del_DeadLock, wie im SQL-Agent Auftragsbeispiel gezeigt, löschen.

Integration of Reports

In SQL Server management Studio navigate to the database “SQL_Analysis_Reporting” and there in the menu go to Reports – Custom reports, to choose the 3 provided rdl-files (separate download) for integration.

Integration der Berichte

Navigieren Sie im SQL Server Management Studio zur Datenbank “SQL_Analysis_Reporting” und dort im Menü zu Berichte – Benutzerdefinierte Berichte, um die 3 bereitgestellten rdl-Dateien (gesonderter Download) zur Integration auszuwählen. 

When the warning pops up you need to say “run” in order to execute the reports.

Wenn die Warnung eingeblendet wird, müssen Sie „Ausführen“ auswählen, um die Berichte auszuführen. 

After that the 3 reports will show up in the list. For more information on Custom Reports in Management Studio read here: Custom Reports in Management Studio  

Danach werden die 3 Berichte in der Liste angezeigt. Für mehr Informationen zu benutzerdefinierten Berichten in Management Studio können Sie hier weiterlesen: Custom Reports in Management Studio  

Usage for Analysis

When deadlocks are collected, these can be analyzed in different ways with the help of sample views located in the database SQL_Analysis_Reporting. These range from general statistics down to detailed statements, resources and execution plans.

Verwendung zur Analyse

Wenn Deadlocks gesammelt werden, können diese auf verschiedenen Wegen mithilfe der Beispielsichten in der Datenbank SQL_Analysis_Reporting analysiert werden. Diese reichen von allgemeinen Statistiken bis zu detaillierten Statements, Ressourcen und Ausführungsplänen.

A special feature is the inclusion of the original query plans (provided that they are available in the cache at the moment of collection) of the first 2 involved processes – very handy for analysis of cause.

Ein besonderes Feature ist die Einbeziehung des originalen Ausführungsplanes (vorausgesetzt, dass diese zum Zeitpunkt der Sammlung im Cache verfügbar sind) der ersten beiden involvierten Prozesse – sehr praktisch bei der Ursachenanalyse.

A SQLHandle of 0xFF will be shown when the handle from the deadlock-graph cannot be resolved any more. This happens for trivial plans or when the plan has been evicted from cache since the deadlock occurred.

The complete deadlock graph can also be used to get a visual representation of the deadlock graph.

Ein SQLHandle von 0xFF wird angezeigt, wenn das Handle aus dem Deadlock-Graphen nicht mehr aufgelöst werden kann. Das geschieht bei trivialen Plänen oder wenn der Plan seit Auftreten des Deadlock aus dem Cache geworfen wurde.
Der komplette Deadlock-Graph kann auch verwendet werden, um eine visuelle Repräsentation des Deadlock Graphen zu erhalten.

Example of a simple deadlock with 2 processes.

 Beispiel eines einfachen Deadlocks mit 2 Prozessen.

Example of a multi-victim-deadlock with 4 processes.
Since a deadlock can involve an unforeseeable number of processes, but a database table by nature has a strict design, I not only keep the original complete deadlock graph but also extract the list of victims, processes and execution stacks into one xml-field each for simplified analysis.

Beispiel eines Multi-Victim-Deadlocks mit 4 Prozessen.
Da ein Deadlock eine unvorhersehbare Anzahl an Prozessen involvieren kann, aber eine Datenbanktabelle von Natur aus ein striktes Design hat, behalte ich nicht nur den originalen kompletten Deadlock-Graphen sondern extrahiere auch die Liste der Victims/Opfer, Prozesse und Execution Stacks in jeweils ein XML-Feld zur vereinfachten Analyse.

Clicking on the victim-list for a deadlock with 4 victims opens a new xml-window containing just the node of victims with their respective process-id for further, manual matching with the execution stack, for example

Klickt man auf die Victim-Liste eines Deadlocks mit 4 Victims öffnet sich ein neues xml-Fenster, welches nur den Knoten der Victims mit ihrer jeweiligen Prozess-ID für weiteres manuelles Verknüpfen mit zum Beispiel dem Execution Stack.

The Reports

On top of the views one can implement custom reports for reoccurring analysis. 3 example reports are included in the project and can be integrated into Management Studio directly. (Version 2012 and 2014)

Die Berichte

Basierend auf den Sichten kann man nutzerdefinierte Berichte für wiederholte Analysen implementieren. 3 Beispielberichte sind in dem Projekt enthalten und können direkt in Management Studio integriert werden. (Version 2012 und 2014)

Availability

The SQL Deadlock Collector & Parser is available as open source project under Microsoft Public License (Ms-PL) at codeplex:

sqldeadlockcollector.codeplex.com

Verfügbarkeit

Der SQL Deadlock Collector & Parser ist als Open Source Project unter Microsoft Public License (Ms-PL) auf Codeplex verfügbar:

sqldeadlockcollector.codeplex.com

Notes & Recommendations

1) The event-time in SQL Server 2008 may strongly deviate (hours and days!) due to a mistake in the XML-code of the system_health session. Please also see the following Connect Item:

Hinweise & Empfehlungen

1) Die Event-Zeit unter SQL Server 2008 kann aufgrund eines Fehlers im XML-Code der system_health session stark abweichen (Stunden und Tage!). Siehe dazu das folgende Connect Item:

2) In order to make sure that larger deadlock graphs are also captured, I recommend increasing the maximal memory of the system_health session as one sees fit.

2) Um sicherzustellen, dass auch größere Deadlock-Graphen erfasst werden, empfehle ich, den maximalen Speicher der system_health session nach eigenem Ermessen zu erhöhen.

3) Der Umfang der Data-Datenbank kann mit der Zeit stark wachsen. Verwenden Sie die Aufräum-Prozedur (Locking.del_DeadLock) in der Code-Datenbank, um alte Deadlock Einträge zu entfernen.

Call to Action: Quick Poll

I absolutely invite you to use my code free of charge and comment on any problems you may have or improvements you would like to see.

All I would love for you to do is send me a high level statistics of what kind of deadlocks you see on your systems.

Simply run the following query after you have collected new deadlocks, and send me the result, purely consisting of the Number of affected processes and number of deadlocks.

Call to Action: Kurze Umfrage

Ich lade Sie dazu ein, meinen Code kostenlos einzusetzen und Probleme, die Sie antreffen sollten zu kommentieren.

Alles, worüber ich mich freuen würde, wäre, wenn Sie mir eine allgemeine Statistik darüber zusenden, welche Art von Deadlock Sie auf Ihren Systemen sehen.

Dafür können Sie einfach, nachdem Sie neue Deadlocks gesammelt haben, die folgende Abfrage ausführen, und mir das Ergebnis zusenden, welches lediglich aus der Anzahl der betroffenen Prozesse und Anzahl Deadlocks besteht.

You can use comments or also send me an Email if you have access to it. (due to spam bots I will not post it here openly though)

Thank you for all sendings!

Sie können Kommentare verwenden, oder mir eine E-Mail schicken, wenn Sie auf diese Zugriff haben (aufgrund der Spam-Bots werde ich meine Email-Adresse hier jedoch nicht öffentlich posten).

Ich bedanke mich für alle Zusendungen!

I hope you find my tool useful, and good luck with the analysis of your hopefully not too many deadlocks.

Ich hoffe, Sie können mein Tool gut verwenden und wünsche Ihnen viel Erfolg bei der Analyse Ihrer hoffentlich nicht allzu vielen Deadlocks.