SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Generate Deadlock Summary Information

By Carolyn Richardson,

Sometimes you may find yourself supporting an application written by a third party and you need to present information about priority objects in the database that need focussed attention. One thing I had noticed in my situation was the high number deadlocks we were finding. So I chose to find out which the main tables affected were.

Firstly I enabled trace flags 1204, which returns the resources and types of locks participating in a deadlock and also the current command affected and 1222 which returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. So that now gives the error logs a lot more information that I can work with. I then needed to extract the information from the logs, so in my DBA database I created 3 tables:

  CREATE TABLE [dbo].[DeadlockTable](
         [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) ON [PRIMARY]
  ) ON [PRIMARY]

  CREATE TABLE [dbo].[DeadlockVictim](
         [ID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
         [LogDate] [DATETIME] NOT NULL,
         [ProcessInfo] [VARCHAR](100) NULL,
         [Text] [VARCHAR](2500) NULL,
   CONSTRAINT [PK_DeadlockVictim] 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]

  CREATE TABLE [dbo].[DeadockObject](
         [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) ON [PRIMARY]
  ) ON [PRIMARY]
 

I then created 3 stored procedures:

CREATE PROCEDURE [dbo].[usp_RefreshDeadockObject]
AS
BEGIN
     TRUNCATE TABLE [dbo].[DeadockObject];

      INSERT  INTO [dbo].[DeadockObject]
              ( [LogDate] ,
                [ProcessInfo] ,
                [Text]
              )
              EXEC master.dbo.xp_readerrorlog 0, 1, 'objectlock lockPartition=0',
                  NULL, NULL, NULL, N'desc';
      INSERT  INTO [dbo].[DeadockObject]
              ( [LogDate] ,
                [ProcessInfo] ,
                [Text]
              )
              EXEC master.dbo.xp_readerrorlog 1, 1, 'objectlock lockPartition=0',
                  NULL, NULL, NULL, N'desc';
      INSERT  INTO [dbo].[DeadockObject]
              ( [LogDate] ,
                [ProcessInfo] ,
                [Text]
              )
              EXEC master.dbo.xp_readerrorlog 2, 1, 'objectlock lockPartition=0',
                  NULL, NULL, NULL, N'desc';
      INSERT  INTO [dbo].[DeadockObject]
              ( [LogDate] ,
                [ProcessInfo] ,
                [Text]
              )
              EXEC master.dbo.xp_readerrorlog 3, 1, 'objectlock lockPartition=0',
                  NULL, NULL, NULL, N'desc';
      INSERT  INTO [dbo].[DeadockObject]
              ( [LogDate] ,
                [ProcessInfo] ,
                [Text]
              )
              EXEC master.dbo.xp_readerrorlog 4, 1, 'objectlock lockPartition=0',
                  NULL, NULL, NULL, N'desc';
      INSERT  INTO [dbo].[DeadockObject]
              ( [LogDate] ,
                [ProcessInfo] ,
                [Text]
              )
              EXEC master.dbo.xp_readerrorlog 5, 1, 'objectlock lockPartition=0',
                  NULL, NULL, NULL, N'desc';

  --remove any duplicates created
      WITH    CTE
                AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY [LogDate],
                                                  [ProcessInfo], [Text] ORDER BY [LogDate] ) AS RowNumber ,
                              [LogDate] ,
                              [ProcessInfo] ,
                              [Text]
                     FROM     [DBA].[dbo].[DeadockObject]
                   )
          DELETE  FROM CTE
          WHERE   RowNumber > 1;
  END
  GO
CREATE PROCEDURE [dbo].[usp_RefreshDeadockVictim]
AS
TRUNCATE TABLE [dbo].[DeadlockVictim]
INSERT INTO [dbo].[DeadlockVictim]
           ([LogDate]
           ,[ProcessInfo]
           ,[Text])
EXEC master.dbo.xp_readerrorlog 0, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc' 
INSERT INTO [dbo].[DeadlockVictim]
           ([LogDate]
           ,[ProcessInfo]
           ,[Text])
EXEC master.dbo.xp_readerrorlog 1, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc' 
INSERT INTO [dbo].[DeadlockVictim]
           ([LogDate]
           ,[ProcessInfo]
           ,[Text])
EXEC master.dbo.xp_readerrorlog 2, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc' 
INSERT INTO [dbo].[DeadlockVictim]
           ([LogDate]
           ,[ProcessInfo]
           ,[Text])
EXEC master.dbo.xp_readerrorlog 3, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc' 
INSERT INTO [dbo].[DeadlockVictim]
           ([LogDate]
           ,[ProcessInfo]
           ,[Text])
EXEC master.dbo.xp_readerrorlog 4, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc' 
INSERT INTO [dbo].[DeadlockVictim]
           ([LogDate]
           ,[ProcessInfo]
           ,[Text])
EXEC master.dbo.xp_readerrorlog 5, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc' 

GO CREATE PROCEDURE [dbo].[usp_RefreshDeadlockTable] AS BEGIN TRUNCATE TABLE[dbo].[DeadlockTable] SELECT ROW_NUMBER() OVER (ORDER BY name) AS Row,Name INTO #table FROM sys.tables DECLARE @intFlag INT = 1 DECLARE @stringtosearchfor nVARCHAR(200) DECLARE @stringtosearchfor1 nVARCHAR(200) DECLARE @SQL NVARCHAR(4000) WHILE @intFlag <= (SELECT COUNT(*) FROM sys.tables) BEGIN SET @stringtosearchfor = (SELECT Name FROM #table WHERE Row = @intFlag) SET @stringtosearchfor1 = '%'+@stringtosearchfor+'%' SET @SQL = 'INSERT INTO [dbo].[DeadlockTable] (LogDate ,ProcessInfo ,[Text] ,TableAffected) SELECT LogDate,ProcessInfo,[Text],'''+@stringtosearchfor+''' FROM DeadockObject WHERE Text LIKE '''+@stringtosearchfor1+'''' --PRINT @SQL EXEC (@SQL) SET @intFlag = @intFlag + 1 END DROP TABLE #table END

I then scheduled an overnight job to populate the tables from the stored procedures:

Job properties

Once the tables were populated I could run some queries against the tables to give me more info:

  --Deadlocks by Number
  SELECT RIGHT([LogDate],19) [LogDate]
        ,COUNT(Text) No
  FROM [DBA].[dbo].[DeadlockVictim]
  GROUP BY RIGHT([LogDate],19)
  ORDER BY CAST(RIGHT([LogDate],19) AS DATETIME) 

  --Deadlocks by Day
  SELECT CAST([LogDate] AS DATE) [LogDay]
        ,COUNT(Text) No
  FROM [DBA].[dbo].[DeadlockVictim]
  GROUP BY CAST([LogDate] AS DATE)
  ORDER BY CAST([LogDate] AS DATE) 

  --Deadlocks By Day and No
  SELECT  CAST([LogDate] AS DATE) [LogDay] ,
          [TableAffected] ,
          COUNT([TableAffected]) No
  FROM    [DBA].[dbo].[DeadlockTable]
  GROUP BY CAST([LogDate] AS DATE),[TableAffected]
  ORDER BY CAST([LogDate] AS DATE),[TableAffected]; 

  --Summary of impacted tables
  SELECT  [TableAffected] ,
          COUNT([TableAffected]) No
  FROM    [DBA].[dbo].[DeadlockTable]
  GROUP BY [TableAffected]
  ORDER BY COUNT([TableAffected]) DESC

From this is made it very clear which the top tables were that we needed to get the vendor to concentrate on to have the greatest impact in our environment. This was because we could provide detailed evidence of the deadlocks we were having. This could also be expanded to give evidence on the times of day of day, days of week etc. if needed.

In our case all the application code was within the application not in the database so we were limited, but we could supply the deadlock graphs and logs to the vendor an indeed we did get a fix for 2 of the 3 major tables involved with deadlock events, we await the 3rd fix as I write, but it’s reduced numbers by a significant amount.

 

Resources:

Deadlocks.jpg
Total article views: 2359 | Views in the last 30 days: 13
 
Related Articles
FORUM

master.dbo.xp_smtp_sendmail ( sql 2000)

master.dbo.xp_smtp_sendmail ( sql 2000)

FORUM

Insert rows

insert

FORUM

Inserting foreign key value

Insert

FORUM

master.dbo.xp_cmdshell Error

[b][/b]I need to create a stored procedure that would perform an automatic database restore from 1 s...

FORUM

INSERT INTO FROM RETRIVED DATA

INSERT

Tags
deadlock graph    
deadlocks    
 
Contribute