How to capture deadlock occurrences in SQL Server using sys.dm_os_performance_counters

Today, let us quickly see how to monitor deadlocks in your SQL Server. There are multiple ways to get the deadlock information.

But, this blog post details an approach to capture the number of deadlocks using performance monitor dynamic management view in a SQL Server.

This approach does not really cover the deadlock graph capturing or analyzing the deadlock graph etc. This enables you to just understand if there are deadlocks in your system and it captures the data as you configured. This data can be used to generate nice reports as per your requirement.

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

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s