Object creation script
First step is to create the objects to store the deadlock information. Please note, we are not doing to capture the deadlock information, but just the occurrence of deadlocks. So we need a table with DateAdded to denote the captured date and time and deadlocks column to denote the number of deadlocks occurred.
Drop Table if exists DeadlockTracker
CREATE TABLE DeadlockTracker(
DateAdded datetime NOT NULL
, Deadlocks int NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX IX_DeadlockTracker_DateAdded_U_C ON DeadlockTracker
(
DateAdded
) WITH (FillFactor = 100)
Capture Query
The next step is to define the capture process. The idea is to frequently query a dynamic management view – sys.dm_os_performance_counter and log the data in the table as defined int he first step. We need to capture these information in a defined interval, may be every 15 minutes. We can configure a SQL job to run every 15 minutes.
DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END
INSERT INTO DeadlockTracker(DateAdded, Deadlocks)
SELECT DateAdded = GetDate()
, Deadlocks = (SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE object_name like @CounterPrefix + '%'
AND instance_name IN ('', '_Total')
AND counter_name ='Number of Deadlocks/sec')
Sample Report Dashboard Queries
This is the last step which is nothing but visualization of data captured. This is absolutely depending on data analyst discretion, but a very basic report sample has provided for your reference. You can change the query to get the data group by month/days/quarter etc.
Select *, Deadlocks - Lag(Deadlocks) Over(Order by DateAdded asc) From DeadlockTracker
SQLZealots 