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.
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 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
Comments
Post a Comment