Populate Deadlock Summary Information

You already know that deadlock is when two or more sessions inside of the database engine end up waiting for access to locked resources held by each other. In a deadlock situation, none of the sessions can continue to execute until one of those sessions releases its locks, so allowing the other session(s) access to the locked resource. When the deadlock monitor thread in SQL Server detects a circular blocking chain, it selects one of the participants as a victim, cancels that spid’s current batch, and rolls backs his transaction in order to let the other spids continue with their work. The deadlock victim will get a 1205 error:

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There are many approaches how to troubleshoot deadlocks, i.e.:

1. T-SQL Approach

With this query you can find blocked processes, like activity monitor:

select * from sys.sysprocesses where blocked > 0

To get more detailed information you can use another query:

SELECT Blocker.text –, Blocker.*, *

FROM sys.dm_exec_connections AS Con

INNER JOIN sys.dm_exec_requests AS BlReqs

ON Con.session_id = BlReqs.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS w

ON BlReqs.session_id = w.session_id

CROSS APPLY sys.dm_exec_sql_text(Con.most_recent_sql_handle) AS Blocker

It returns the result as a line and verse and you can see the actual statement causing the resource block.

2. Using SQL Server Profiler

 

Create Trace Wizard to run the “Identify the Cause of a Deadlock” trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases.

3. Capture a SQL Server Deadlock Graph

 

A deadlock graph shows us the sessions and resources that were involved in a deadlock. Starting from SQL Server 2008 and later, we can retrieve deadlock graphs retrospectively from the extended events system_health session.

SELECT XEvent.query(‘(event/data/value/deadlock)[1]’) AS DeadLockGraph

FROM ( SELECT XEvent.query(‘.’) AS XEvent

FROM ( SELECT CAST(target_data AS XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s

ON s.address = st.event_session_address

WHERE s.name = ‘system_health’

AND st.target_name = ‘ring_buffer’

) AS Data

CROSS APPLY

TargetData.nodes

(‘RingBufferTarget/event[@name=”xml_deadlock_report”]’)

AS XEventData ( XEvent )

) AS Source;

4. Service Broker event notifications

 

Event notifications allow the capture of deadlock graph information using SQL Server Service Broker, by creating a service and queue for the DEADLOCK_GRAPH trace event.

5. Set up Trace flags

 

At this article I’m going to mention only trace flags 1204 and 1222. Setting up this trace flag allows writing the deadlock information to the error log.

To set this trace flag just run the command:

DBCC TRACEON (1222, -1)

DBCC TRACEON (1204, -1)

Or add it in startup parameters.

To turn it off:

DBCC TRACEOFF (1222, -1)

DBCC TRACEOFF (1204, -1)

More details and techniques you can find in Handling Deadlocks in SQL Server

After trace flags were set up you find a result in error log approximately like this:

errorlogresult

Today I’m going to talk about the following:

  • how to get information when deadlock has happened
  • how many deadlocks have been happened at this day
  • which tables have been affected and how many times

After enabling trace flags I’m able to read error log.

First let’s create 4 tables in my/your DBA database:

  1. For populating deadlock object information 

 

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DeadLockObjects](

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[LogDate] [datetime] NOT NULL,

[ProcessInfo] [varchar](100) NULL,

[Text] [varchar](5000) NULL,

CONSTRAINT [PK_DeadockObject] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

2. For populating deadlock victims information

 

 

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DeadLockVictims](

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[LogDate] [datetime] NOT NULL,

[ProcessInfo] [varchar](150) NULL,

[Text] [varchar](3000) NULL,

CONSTRAINT [PK_DeadLockVictims] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

3. For populating deadlock object information

 

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DeadLockTableResult](

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[LogDate] [datetime] NOT NULL,

[ProcessInfo] [varchar](100) NULL,

[Text] [varchar](2500) NULL,

[TableAffected] [varchar](200) NULL,

CONSTRAINT [PK_DeadLockTable] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

4. For populating all table names from all databases on server (instance).

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[SearchTable](

[Row] [int] NULL,

[Name] [varchar](250) NULL,

[DBName] [varchar](250) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Now I need to extract deadlock information from error log. As you could see from screen shot with information from error log, all records involved in deadlock processes started with “keylock” word.

For this purpose I created stored procedure:

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_PopulateDeadLockObjects]

AS

BEGIN

TRUNCATE TABLE [dbo].[DeadLockObjects];

INSERT INTO [dbo].[DeadLockObjects]

( [LogDate] ,

[ProcessInfo] ,

[Text]

)

EXEC master.dbo.xp_readerrorlog 0, 1, N’keylock’,

NULL, NULL, NULL, N’desc’;

INSERT INTO [dbo].[DeadLockObjects]

( [LogDate] ,

[ProcessInfo] ,

[Text]

)

EXEC master.dbo.xp_readerrorlog 1, 1, N’keylock’,

NULL, NULL, NULL, N’desc’;

INSERT INTO [dbo].[DeadLockObjects]

( [LogDate] ,

[ProcessInfo] ,

[Text]

)

EXEC master.dbo.xp_readerrorlog 2, 1, N’keylock’,

NULL, NULL, NULL, N’desc’;

INSERT INTO [dbo].[DeadLockObjects]

( [LogDate] ,

[ProcessInfo] ,

[Text]

)

EXEC master.dbo.xp_readerrorlog 3, 1, N’keylock’,

NULL, NULL, NULL, N’desc’;

INSERT INTO [dbo].[DeadLockObjects]

( [LogDate] ,

[ProcessInfo] ,

[Text]

)

EXEC master.dbo.xp_readerrorlog 4, 1,   N’keylock’,

NULL, NULL, NULL, N’desc’;

INSERT INTO [dbo].[DeadLockObjects]

( [LogDate] ,

[ProcessInfo] ,

[Text]

)

EXEC master.dbo.xp_readerrorlog 5, 1, N’keylock’,

NULL, NULL, NULL, N’desc’;

–Remove duplicates

WITH    CTE

AS (SELECT   ROW_NUMBER() OVER ( PARTITION BY [LogDate],

[ProcessInfo], [Text] ORDER BY [LogDate] ) AS RN ,

[LogDate] ,

[ProcessInfo] ,

[Text]

FROM     [DBA].[dbo].[DeadLockObjects]

)

DELETE FROM CTE

WHERE   RN > 1;

END

To populate number of deadlock victims and date when they have been happened I created another stored procedure:

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_PopulateDeadLockVictims]

AS

TRUNCATE TABLE [DBA].[dbo].[DeadLockVictims]

INSERT INTO [DBA].[dbo].[DeadLockVictims]

([LogDate]

,[ProcessInfo]

,[Text])

EXEC master.dbo.xp_readerrorlog 0, 1, N’deadlock victim=process’, NULL, NULL, NULL, N’desc’

INSERT INTO [DBA].[dbo].[DeadLockVictims]

([LogDate]

,[ProcessInfo]

,[Text])

EXEC master.dbo.xp_readerrorlog 1, 1, N’deadlock victim=process’, NULL, NULL, NULL, N’desc’

INSERT INTO [DBA].[dbo].[DeadLockVictims]

([LogDate]

,[ProcessInfo]

,[Text])

EXEC master.dbo.xp_readerrorlog 2, 1, N’deadlock victim=process’, NULL, NULL, NULL, N’desc’

INSERT INTO [DBA].[dbo].[DeadLockVictims]

([LogDate]

,[ProcessInfo]

,[Text])

EXEC master.dbo.xp_readerrorlog 3, 1, N’deadlock victim=process’, NULL, NULL, NULL, N’desc’

INSERT INTO [DBA].[dbo].[ DeadLockVictims]

([LogDate]

,[ProcessInfo]

,[Text])

EXEC master.dbo.xp_readerrorlog 4, 1, N’deadlock victim=process’, NULL, NULL, NULL, N’desc’

INSERT INTO [DBA].[dbo].[DeadLockVictims]

([LogDate]

,[ProcessInfo]

,[Text])

EXEC master.dbo.xp_readerrorlog 5, 1, N’deadlock victim=process’, NULL, NULL, NULL, N’desc’

And finally I created stored procedure which is populated information about tables involved in deadlocks with date and frequency.

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_PopulateDeadLockTableResult]

AS

BEGIN

TRUNCATE TABLE [DBA].[dbo].[DeadLockTableResult]

DECLARE @dbname   VARCHAR(150),

@tname     VARCHAR(150)

DECLARE @SQL NVARCHAR(4000)

IF OBJECT_ID (N’DBA..SearchTable’, N’U’) IS NOT NULL

BEGIN

TRUNCATE TABLE DBA..SearchTable

END

DECLARE x CURSOR FOR

select sd.name

from sys.sysdatabases sd JOIN sys.databases d ON sd.dbid = d.database_id

WHERE sd.name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’DBA’)

AND d.collation_name = ‘SQL_Latin1_General_CP1_CI_AS’

OPEN x

FETCH NEXT FROM x INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = ‘

INSERT INTO DBA..SearchTable (Row,Name,DBName)

SELECT ROW_NUMBER() OVER (ORDER BY name),Name,”’+@dbname+”’

FROM ‘+'[‘+@dbname+’]’+’.sys.tables

–PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM x INTO @dbname

END

CLOSE x

DEALLOCATE x

DECLARE @intFlag INT = 1

DECLARE @errorlogtext VARCHAR(2000)

WHILE @intFlag <= (SELECT COUNT(*) FROM DBA..SearchTable)

BEGIN

DECLARE y CURSOR FOR

SELECT DISTINCT Name,DBName

FROM DBA..SearchTable

WHERE [Row] = @intFlag

OPEN y

FETCH NEXT FROM y INTO @tname,@dbname

WHILE @@FETCH_STATUS = 0

BEGIN

 

SET @errorlogtext = ‘%’+@dbname+’.dbo.’+@tname+’%’

SET @SQL = ‘INSERT INTO [DBA].[dbo].[DeadLockTableResult] (LogDate ,ProcessInfo ,[Text] ,TableAffected)

SELECT LogDate,ProcessInfo,[Text],”’+@dbname+’.dbo.’+@tname+”’

FROM DBA..DeadLockObjects

WHERE Text LIKE ”’+@errorlogtext+”’

–PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM y INTO @tname,@dbname

END

CLOSE y

DEALLOCATE y

SET @intFlag = @intFlag + 1

END

END

Now it’s time to set up a SQL agent job for populating tables we created.

I scripted the job. Please change inside the job:

@owner_login_name = N’Your Domain\User Name’

@database_name=N’DBA’ if it’s different from DBA.

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’M_PopulateDeadLockInfo’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N'[Uncategorized (Local)]’,

@owner_login_name=N’Your Domain\User Name’, @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’PopulateDeadLockVictims’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=4,

@on_fail_step_id=4,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’EXEC usp_PopulateDeadLockVictims’,

@database_name=N’DBA’,

@flags=12

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’PopulateDeadLockObjects’,

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=4,

@on_fail_step_id=4,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’EXEC usp_PopulateDeadLockObjects’,

@database_name=N’DBA’,

@flags=12

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’PopulateDeadLockTableResult’,

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=4,

@on_fail_step_id=4,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’EXEC usp_PopulateDeadLockTableResult’,

@database_name=N’DBA’,

@flags=12

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’Schedule_1′,

@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=20161219,

@active_end_date=99991231,

@active_start_time=30000,

@active_end_time=235959,

@schedule_uid=N’0fcf05ba-88f5-416b-a7fd-608a61f72b31′

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

And after all these preparations we can get deadlock information I mentioned. For that use the following scripts. Of course you can change them and “play” with them as you would like but at this moment I have:

— Deadlocks’ Number for period

SELECT MIN(CONVERT(VARCHAR(20),[LogDate],120)) StartDate,

MAX(CONVERT(VARCHAR(20),[LogDate],120)) EndDate,

COUNT(Text) DeadlocksNumber

FROM [DBA].[dbo].[DeadLockVictims]

 

–Deadlocks’ Number by date

SELECT CONVERT(VARCHAR(20),[LogDate],120) [LogDate]

,COUNT(Text) DeadlocksNumber

FROM [DBA].[dbo].[DeadLockVictims]

GROUP BY CONVERT(VARCHAR(20),[LogDate],120)

ORDER BY CONVERT(VARCHAR(20),[LogDate],120)

 

–Deadlock Numbers per Day

SELECT CAST([LogDate] AS DATE) [LogDate]

,COUNT(Text) DeadlocksNumber

FROM [DBA].[dbo].[DeadLockVictims]

GROUP BY CAST([LogDate] AS DATE)

ORDER BY CAST([LogDate] AS DATE)

 

–Deadlocks By Date and Numbers

SELECT CONVERT(VARCHAR(20),[LogDate],120) [LogDate] ,

[TableAffected] ,

COUNT([TableAffected]) DeadlocksNumber

FROM    [DBA].[dbo].[DeadLockTableResult]

GROUP BY CONVERT(VARCHAR(20),[LogDate],120),[TableAffected]

ORDER BY CONVERT(VARCHAR(20),[LogDate],120),[TableAffected];

 

–Summary for every impacted table

SELECT  [TableAffected] ,

COUNT([TableAffected]) DeadlocksNumber

FROM    [DBA].[dbo].[DeadLockTableResult]

GROUP BY [TableAffected]

ORDER BY COUNT([TableAffected]) DESC

 

The result looks like:

queriesresult

 

Recommendation how to eliminate deadlocks

  • Deadlocks (nearly) always happened in SQL transactions. Time is a factor, so keep transactions as short as possible. Try to optimize indexes on critical tables. And never, ever, leave an uncommitted transaction waiting for user input.
  • For all operations that are atomic, keep the size of the operation to an absolute minimum, as far as your situation allows for.
  • If it’s possible use locking hints to place more specific locks. Lock all your resources as quickly as possible.
  • Make sure all your code places locks in the same order.
  • Try different isolation levels.
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s