Free SQL Deadlock Collector & Parser published at codeplex
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.
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. 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
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).
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)
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.
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.
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 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
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 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 the Jobs
Subsequently, two SQL Server Agent Jobs should be installed which will be configured as follows:
1)
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:];
2)
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.
After installing the databases for the first time, you will find a few rows of example deadlocks in the Data-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.
You can delete those manually or by using the maintenance-procedure Locking.del_DeadLock as shown in the SQL Agent job-example.
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.
When the warning pops up you need to say “run” in order to execute the reports.
Subsequently, 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
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.
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.
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.
Example of a simple deadlock with 2 processes.
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.
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.
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)
Availability
The SQL Deadlock Collector & Parser is available as open source project under Microsoft Public License (Ms-PL) at codeplex:
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:
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.
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.
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!
I hope you find my tool useful, and good luck with the analysis of your hopefully not too many deadlocks.
Andreas
Leave a Reply
Want to join the discussion?Feel free to contribute!