Free Deadlock-Collector & -Parser, based on Extended Events session system_health / freier Deadlock-Sammler & -Parser basierend auf Erweiterten Ereignissen

(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

Description
Technical Implementation
Supported SQL Server Versions & Editions
Setup High-level
Setup the Jobs
Integration of the Reports
Usage for Analysis
The Reports
Availability
Notes & Recommendations
Call to Action: Quick Poll

Inhalte

Beschreibung
Technische Umsetzung
Unterstützte SQL Server Versionen & Editionen
Setup High-level
Setup der Aufträge
Integration der Berichten
Nutzung zur Analyse
Die Berichte
Verfügbarkeit
Hinweise & Empfehlungen
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:

Recover the 3 databases in the respective version (2008 or 2012) onto your Server

The “2008”-Version targets all Systems from SQL Server 2008 SP2 up to SQL Server 2008 R2 SP2
The “2012”-Version targets all Systems from SQL Server 2012 RTM CU3 up to SQL Server 2014 RTM CU6
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

Set up the collection Job
Add a schedule for the collection
Repeat the same for the maintenance job that removes old collected deadlocks after a specified timeframe
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:

Stellen Sie die 3 Datenbanken in der jeweiligen Version (2008 oder 2012) auf Ihrem Server wieder her

Die “2008”-Version ist für alle Systeme ab SQL Server 2008 SP2 bis zum SQL Server 2008 R2 SP2 da
Die “2012”-Version ist für alle Systeme ab SQL Server 2012 RTM CU3 bis zum SQL Server 2014 RTM CU6 da
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

Richten Sie den Sammler-Auftrag ein
Fügen Sie einen Zeitplan für die Datenerfassung hinzu
Wiederholen Sie das gleiche für den Maintenance Job, der alte erfasste Deadlocks nach einem festgesetzten Zeitrahmen entfernt
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:

Name: SQL_Analysis_Collect_Deadlocks

Description: Job that collects Deadlock-Graphs from system_health to store in SQL_Analysis_Data.

Step 1, Type T-SQL, Name: Exec Proc Locking ins_DeadLock

Database context: SQL_Analysis_Code

Command: DECLARE @DeadlocksCollected int;

EXECUTE Locking.ins_DeadLock

@DeadlocksCollected = @DeadlocksCollected OUTPUT;

SELECT @DeadlocksCollected AS [Number of new Deadlocks collected:];

Name: SQL_Analysis_Remove_Old_Deadlocks

Description: Job that removes collected Deadlocks older than X days from SQL_Analysis_Data

Step 1, Type T-SQL, Name: Exec Proc Locking del_DeadLock

Database context: SQL_Analysis_Code

Command: DECLARE @DeleteOlderThanDate date, @DeadlocksRemoved int;

SET @DeleteOlderThanDate = DATEADD(dd, -60, SYSDATETIME())

SELECT @DeleteOlderThanDate AS [LatestDateToKeep:]

Execute Locking.del_DeadLock

@DeleteOlderThanDateX = @DeleteOlderThanDate

, @DeadlocksRemoved = @DeadlocksRemoved OUTPUT;

SELECT @DeadlocksRemoved AS [Number of removed deadlocks/rows from table:];

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.

<victim-list>

</victim-list>

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:

http://connect.microsoft.com/SQLServer/feedback/details/649362/extended-event-session-system-health-diferent-timestamp-datetime-and-datetime-on-server-getdate

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.

3) The size of the Data-database (SQL_Analysis_Data) may greatly expand with time. Use the clean-up procedure (Locking.del_DeadLock) inside the Code-database to remove old deadlock entries.

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.

SELECT
        [AffectedProcesses]
      , COUNT(*) AS [Number of Deadlocks]
    FROM
        [SQL_Analysis_Reporting].[Locking].[v_DeadlockInfos]
    GROUP BY
        [AffectedProcesses]
    ORDER BY
        [AffectedProcesses] ASC

-->

AffectedProcesses Number of Deadlocks
----------------- -------------------
2 3
3 1

(2 row(s) affected)

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.

Andreas