Deadlocks are usually detected and resolved automatically by SQL Server by killing one of the SPIDs involved in it. You may have an alert setup to notify you and/or users may approach you with the issue.
But this post is not about finding the root cause and fix the issue for good.
Ideally, you don't want to see dead locks occur at all and especially not on a regular basis. But situations could arise where a database starts experiencing deadlocks. In this post I would like to share a code I have used to capture the daily count of deadlocks for a trending report.
Note: I am using the DMV sys.dm_os_performance_counters in this code so it will not work if the SQL Server performance counters are disabled for some reason.
You can use this query to check if the performance counters are enabled or disabled.
SELECT COUNT(*) FROM sys.dm_os_performance_counters;
If the return value is 0 rows, the performance counters have been disabled.
Step 1: Create empty table to store the deadlock counts
USE [AdminDBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[deadlock_counter](
[ID] [int] IDENTITY(1,1)NOTNULL,
[record_created_date] [datetime] NOTNULL,
[SQLStartedOn] [datetime] NOTNULL,
[object_name] [nchar](128)NULL,
[counter_name] [nchar](128)NULL,
[instance_name] [nchar](128)NULL,
[cntr_value] [bigint] NULL,
[cntr_type] [int] NULL,
[AveragePerDay] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]
)ON [PRIMARY]
GO
Step 2: Create a SQL Server Agent Job
/*
Please update email operator value value for @notify_email_operator_name parameter
Please feel free to update any of the settings including the schedule you would like to use.
*/
USE [msdb]
GO
BEGINTRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IFNOTEXISTS(SELECTnameFROM msdb.dbo.syscategories WHEREname=N'[Uncategorized (Local)]'AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category@class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job@job_name=N'DBA - Deadlock Counter',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Populate deadlock performance counter values',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep@job_id=@jobId, @step_name=N'deadlock performance counter values',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'set nocount on
set transaction isolation level read uncommitted
go
use AdminDBA
go
-- the counter values are cumulative since SQL Server service was started, not per second
insert into deadlock_counter
SELECT getdate() record_created_date,
d.create_date SQLStartedOn, p.*, AveragePerDay = CONVERT(BIGINT, (( 1.0 * p.cntr_value / NULLIF(Datediff(dd, d.create_date,CURRENT_TIMESTAMP), 0 ))))
-- INTO deadlock_counter
FROM sys.dm_os_performance_counters p
INNER JOIN sys.databases d ON d.NAME = ''tempdb''
WHERE Rtrim(p.counter_name) = ''Number of Deadlocks/sec''
-- AND cntr_value > 0
AND Rtrim(p.instance_name) = ''_Total''
ORDER BY cntr_value DESC
-- select * from deadlock_counter',
@database_name=N'master',
@flags=0
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job@job_id = @jobId, @start_step_id = 1
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule@job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190228,
@active_end_date=99991231,
@active_start_time=80000,
@active_end_time=235959
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver@job_id = @jobId, @server_name =N'(local)'
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
COMMITTRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT> 0)ROLLBACKTRANSACTION
EndSave:
GO
Step 3: Query to extract deadlock counts for each day/interval
-- Note: the counter values are cumulative since SQL Server service was started, not per second
setnocounton
settransactionisolationlevelreaduncommitted
select*,cntr_value -coalesce(lag(cntr_value)over (orderby id), cntr_value) new_deadlocks
from AdminDBA..deadlock_counter
orderby ID desc
