Overview:

The complete SQL Server deadlock collector-solution consists of 3 (small) databases, a SQL Server Agent job and 2 sample 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 deadlock collector 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, usually every 6-24 hours), minimal resources are required.

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

Setup:

The deadlock collector is available in the form of backup files of the respective databases which are 3 in total:

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 analysis/reporting. Thus, updates are being facilitated.

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

Setup instructions:

  1. Recover the 3 databases in the respective version 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

Detailed instructions for 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.

Integrating the example reports (separate download):

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

Custom Reports integration

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

Application:

When deadlocks are collected, these can be analyzed in different ways with the help of sample views located in the database SQL_Analysis_Reporting. This ranges from general statistics up to detailed statements, resources and implementation plans (provided that they are available in the cache at the moment of collection).

Supported SQL Server Versions

The deadlock collector has been tested on the basis of SQL Server 2008 SP3, SQL Server 2008 R2 SP2, SQL Server 2012 SP1/SP2 and SQL Server 2014 RTM

The custom reports only work with Management Studio 2012 onwards. For SQL Server 2008/R2 you can still use the newer version of SSMS free of cost (Express Edition).

Notes:

The time specification in SQL Server 2008 may strongly deviate (hours and days!) due to a mistake in the provided XML. Please also see the following Connect Item: http://connect.microsoft.com/SQLServer/feedback/details/649362/extended-event-session-system-health-diferent-timestamp-datetime-and-datetime-on-server-getdate

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 as one sees fit.

The scope of the data-database may greatly expand with time. Use the clean-up procedure to remove old deadlock entries.