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
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.
