Find Objects with Most deadlocks

RofL

Hi,

I'm monitoring a Microsoft Dynamics NAV database with SQL Monitor, and whilst very happy SQL Monitor is alerting on a heck of a lot of deadlocks/blocks on the NAV database (100's a day.) I'm wondering if there is a way to query the SQLMonitor database to group the deadlocks by object? I've had a look myself but I've not really got anywhere, because the TargetObject column in the alert.alerts table doesn't specify an object that is deadlocked (see example below). However when I review deadlocks on the alert page SQL Monitor is able to identify the objects affected, so clearly SQL Monitor can infer which objects are affected at some point.

7:Cluster,1,4:Name,s21:navsql.foobar.com,9:SqlServer,1,4:Name,s0:,10:SqlProcess,2,9:LoginTime,d635779120793400000,9:SessionId,I235,

I'm kind of guessing that this is not possible, but would be good to know for sure before I move on.

Thanks for any advice.

Dan B

RofL,

Thanks for using the Redgate forums.

I have attached a script which might get you some of what you want.

You can add a line at the bottom

WHERE Cluster_SqlServer_Error_Text LIKE '%objectname%'

to filter only the lines containing the object name. The object names are held within the full message so it would require further manipulation to get what you need I think. Let me know if you need any further assistance.

USE RedGateMonitor
GO 

WITH    CteDeadlockHeads
          AS (SELECT TOP 100000
                        IdCollectionDate
                      , Cluster_SqlServer_Error_ProcessInfo
                      , CollectionDate
                      , Cluster_Name
                      , Cluster_SqlServer_Name
                      , Cluster_SqlServer_Error_LogDate
              FROM      [data].[Cluster_SqlServer_Error_Instances_View]
              WHERE     Cluster_SqlServer_Error_Text LIKE 'deadlock-list%'
                        OR Cluster_SqlServer_Error_Text LIKE 'Deadlock encountered%')
    SELECT  b.Id
          , b.IdCollectionDate
          , b.Cluster_SqlServer_Error_ProcessInfo
          , b.Cluster_SqlServer_Error_Text
          , b.CollectionDate
          , b.CollectionDate_DateTime
          , b.Cluster_Name
          , b.Cluster_SqlServer_Name
          , b.Cluster_SqlServer_Error_LogDate
          , b.Cluster_SqlServer_Error_LogDate_DateTime
          , b.Cluster_SqlServer_Error_SequenceNumber
    FROM    CteDeadlockHeads a
    INNER JOIN [data].[Cluster_SqlServer_Error_Instances_View] b
    ON      b.Cluster_Name = a.Cluster_Name
            AND b.Cluster_SqlServer_Error_LogDate = a.Cluster_SqlServer_Error_LogDate
            AND b.IdCollectionDate = a.IdCollectionDate
            AND b.Cluster_SqlServer_Error_ProcessInfo = a.Cluster_SqlServer_Error_ProcessInfo

RofL

This is brilliant thank you. I'm formatting the Cluster_SqlServer_Error_Text column using string functions to extract the names. Once I've got something I'm happy with I'll post here. Hopefully other people will find this really useful.

Thanks again!

RofL

OK I took the results from the query above and put them into a temp table. Then I have put together a script that will group the locking by table and locking mode. This gives a good starting point to show the frequency of locks. The locking mode and locking level here is not complete, it only covers the types of locks I've got in my table.

--set a start and end date to get number of locks within a date range
DECLARE @DaysBaselineStart INT = 10
DECLARE @DaysBaselineEnd INT = 0
	--only key and page locks are accounted for in the cte below
	--row locks are also possible, as are db and table locks
	--remember, row locks never happen on indexes and key locks never occur on heaps
	--there are also key range locks reported. This happens because transactions are serializable
	;

WITH keylock
AS (
	SELECT SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex('objectname=', LT.Cluster_SqlServer_Error_Text) + 11, charindex('.', 

LT.Cluster_SqlServer_Error_Text) - charindex('objectname=', LT.Cluster_SqlServer_Error_Text) - 11) AS [Database]
		,SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex('objectname', LT.Cluster_SqlServer_Error_Text) + 11, charindex('indexname', 

LT.Cluster_SqlServer_Error_Text) - charindex('objectname', LT.Cluster_SqlServer_Error_Text) - 11) AS [LockedObject]
		,SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex('indexname=', LT.Cluster_SqlServer_Error_Text) + 10, charindex(' id=', 

LT.Cluster_SqlServer_Error_Text) - charindex('indexname=', LT.Cluster_SqlServer_Error_Text) - 10) AS [LockedIndex]
		,SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex(' mode=', LT.Cluster_SqlServer_Error_Text) + 6, charindex(' 

associatedObjectId', LT.Cluster_SqlServer_Error_Text) - charindex(' mode=', LT.Cluster_SqlServer_Error_Text) - 6) AS [LockType]
		,LT.[CollectionDate_DateTime] AS [CollectionDate_DateTime]
	FROM #LockTable_2110 LT
	WHERE Cluster_SqlServer_Error_Text LIKE '%keylock%'
	)
	,pagelock
AS (
	SELECT SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex('objectname=', LT.Cluster_SqlServer_Error_Text) + 11, charindex('.', 

LT.Cluster_SqlServer_Error_Text) - charindex('objectname=', LT.Cluster_SqlServer_Error_Text) - 11) AS [Database]
		,SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex('objectname', LT.Cluster_SqlServer_Error_Text) + 11, charindex(' id=', 

LT.Cluster_SqlServer_Error_Text) - charindex('objectname', LT.Cluster_SqlServer_Error_Text) - 11) AS [LockedObject]
		,'No Index - page lock' AS [LockedIndex]
		,SUBSTRING(LT.Cluster_SqlServer_Error_Text, charindex(' mode=', LT.Cluster_SqlServer_Error_Text) + 6, charindex(' 

associatedObjectId', LT.Cluster_SqlServer_Error_Text) - charindex(' mode=', LT.Cluster_SqlServer_Error_Text) - 6) AS [LockType]
		,LT.[CollectionDate_DateTime] AS [CollectionDate_DateTime]
	FROM #LockTable_2110 LT
	WHERE Cluster_SqlServer_Error_Text LIKE '%pagelock%'
	)
SELECT K.[Database]
	,K.[LockedObject]
	,K.[LockedIndex]
	,CASE K.[LockType]
		WHEN 'S'
			THEN 'SHARED'
		WHEN 'U'
			THEN 'UPDATE'
		WHEN 'IX'
			THEN 'INTENT EXCLUSIVE'
		WHEN 'X'
			THEN 'EXCLUSIVE'
		WHEN 'IS'
			THEN 'INTENT SHARED'
		WHEN 'SIX'
			THEN 'SHARED INTENT EXCLUSIVE'
		WHEN 'UIX'
			THEN 'UPDATE INTENT EXCLUSIVE'
		WHEN 'IU'
			THEN 'INTENT UPDATE'
		WHEN 'RangeS-U'
			THEN 'SERIALIZABLE UPDATE SCAN'
		WHEN 'RangeI-N'
			THEN 'INSERT RANGE, NULL RESOURCE LOCK'
		WHEN 'RangeX-X'
			THEN 'EXCLUSIVE RANGE, EXCLUSIVE RESOURCE LOCK'
		ELSE K.[LockType]
		END AS [LockType]
	,COUNT(1) AS Recurrence
FROM keylock K
WHERE K.[CollectionDate_DateTime] > DATEADD(DAY, - @DaysBaselineStart, GETDATE())
	AND K.[CollectionDate_DateTime] < DATEADD(DAY, - @DaysBaselineEnd, GETDATE())
GROUP BY [Database]
	,[LockedObject]
	,[LockedIndex]
	,[LockType]

UNION ALL

SELECT P.[Database]
	,P.[LockedObject]
	,P.[LockedIndex]
	,CASE P.[LockType]
		WHEN 'S'
			THEN 'SHARED'
		WHEN 'U'
			THEN 'UPDATE'
		WHEN 'IX'
			THEN 'INTENT EXCLUSIVE'
		WHEN 'X'
			THEN 'EXCLUSIVE'
		WHEN 'IS'
			THEN 'INTENT SHARED'
		WHEN 'SIX'
			THEN 'SHARED INTENT EXCLUSIVE'
		WHEN 'UIX'
			THEN 'UPDATE INTENT EXCLUSIVE'
		WHEN 'IU'
			THEN 'INTENT UPDATE'
		WHEN 'RangeS-U'
			THEN 'SERIALIZABLE UPDATE SCAN'
		WHEN 'RangeI-N'
			THEN 'INSERT RANGE, NULL RESOURCE LOCK'
		WHEN 'RangeX-X'
			THEN 'EXCLUSIVE RANGE, EXCLUSIVE RESOURCE LOCK'
		ELSE P.[LockType]
		END AS [LockType]
	,COUNT(1) AS Recurrence
FROM pagelock P
WHERE P.[CollectionDate_DateTime] > DATEADD(DAY, - @DaysBaselineStart, GETDATE())
	AND P.[CollectionDate_DateTime] < DATEADD(DAY, - @DaysBaselineEnd, GETDATE())
GROUP BY [Database]
	,[LockedObject]
	,[LockedIndex]
	,[LockType]
ORDER BY [Database]
	,[LockedObject]
	,[LockedIndex]
	,[Recurrence] DESC