Find Objects with Most deadlocks

RofLRofL Posts: 13
edited October 21, 2015 6:30AM in SQL Monitor Previous Versions
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.

Comments

  • 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
    
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • 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! :D
  • 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
    
    
Sign In or Register to comment.