Custom counter – deadlocks in the past X hours

In response to a couple of requests, here’s a custom counter that allows you to alert on the total number of deadlocks which happen in a timeframe. Set the value of @lookBackHours for the length of time you want to consider.

One way to use this would be to set the built-in deadlock alert to Informational and use this alert to raise Critical alerts when a large number of deadlocks appear to be happening in a short period of time.

declare @prevValue int, @curValue int, @curTime datetime, @lookBackHours int
								
								-- Set how many hours to count deadlocks over
								set @lookBackHours = -1
								
								-- Save current time
								select @curTime = getdate()
								
								-- Sticking a real table in temp - this can be put wherever, so I went ahead and added some server restart logic
								if (select isnull(object_id('tempdb..DeadlockCount'), 0)) = 0 
								begin 
								create table tempdb..DeadlockCount (ServerStartTime datetime, RefreshTime datetime, CountValue int )
								end
								
								-- If you don't keep this table in tempdb, here's the logic to clear out records if the server has restarted
								-- Not actually necessary in tempdb
								--delete
								--from
								--     tempdb..DeadlockCount
								--where       
								--     ServerStartTime <> (select create_date from sys.databases where name = 'tempdb')
								
								-- Clear out values older than the lookback time
								delete from tempdb..DeadlockCount
								where RefreshTime < dateadd(hh,@lookBackHours,@curTime)
								
								-- Get the minimum counter value since the lookbacktime
								select @prevValue = min(CountValue)
								from tempdb..DeadlockCount
								
								-- Get the current counter value
								select @curValue = cntr_value
								from sys.dm_os_performance_counters
								where counter_name = 'Number of Deadlocks/sec' and instance_name = '_Total'
								
								-- Save the current counter value for future reference
								insert into  tempdb..DeadlockCount 
								select
								ServerStartTime = create_date,
								RefreshTime = @curTime,
								CountValue = @curValue
								from sys.databases 
								where name = 'tempdb'
								
								-- Select out the deadlock count over past {lookback} hours
								-- Note that you want a null prev value to fall back to 0 because otherwise you'll get
								-- extraneous alerts when starting monitoring
								select
								DeadlockCount = isnull( @curValue - @prevValue, 0)