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, more likely than not, it indicates that
the performance counters have been disabled.
USE [AdminDBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[deadlock_counter](
[ID] [int] IDENTITY(1,1) NOT NULL,
[record_created_date] [datetime] NOT NULL,
[SQLStartedOn] [datetime] NOT NULL,
[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
/*
Please
update email operator 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
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=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
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- Note: the counter
values are cumulative since SQL Server service was started, not per second
set nocount on
set transaction isolation level read uncommitted
select * ,cntr_value - coalesce(lag(cntr_value) over (order by id), cntr_value) new_deadlocks
from AdminDBA..deadlock_counter
order by ID desc
