Deadlock Detection and Reporting part-II

November 21, 2014

This is a continuation of the series about deadlock. The first part was how you detect deadlock in SQL Server and collect that information.  This part will be about reporting from the collected information.

On the 2nd step of the job the code below will parse the XML and send .xdl file along with information in tabular format.

2nd step on the job:

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
IF EXISTS ( SELECT *
FROM tempdb.sys.tables
WHERE name LIKE '##tmpDeadlockEvents%' )
DROP TABLE ##tmpDeadlockEvents
CREATE TABLE ##tmpDeadlockEvents
(
    [deadlock-list] XML
)
DECLARE @subjectText VARCHAR(250) ,
@bodyText NVARCHAR(MAX)
DECLARE @x XML
DECLARE @dataspaces TABLE
(
    name VARCHAR(50) ,
    data_space_id INT ,
    type VARCHAR(10)
)
DECLARE @allocationunit TABLE
(
    allocation_unit_id BIGINT ,
    container_id BIGINT ,
    data_space_id INT
)
DECLARE @processes TABLE
(
    spid INT ,
    dbid VARCHAR(5) ,
    ProcName VARCHAR(50) ,
    SQL VARCHAR(MAX) ,
    Process VARCHAR(50) ,
    WaitResource VARCHAR(50) ,
    TranStarted DATETIME ,
    BatchComplete DATETIME ,
    WaitTime VARCHAR(50) ,
    Result VARCHAR(50)
)
DECLARE @resource TABLE
(
    TableName VARCHAR(50) ,
    IndexName VARCHAR(100) ,
    hobt VARCHAR(50) ,
    OwnerProcess VARCHAR(50)
)
DECLARE @fileAtt VARCHAR(50) ,
@qrysyscat VARCHAR(250) ,
@dbid INT ,
@lockType VARCHAR(50)

SELECT TOP 1
    @x = [DeadlockGraph].query('/TextData/deadlock-list/deadlock')
FROM DBA..DeadlockEvents
ORDER BY Id DESC

INSERT INTO ##tmpDeadlockEvents
VALUES ( @x )

SELECT TOP 1
    @fileAtt = 'DeadLockGraph_' + CAST(ID AS VARCHAR) + '.xdl'
FROM DBA..DeadlockEvents
ORDER BY ID DESC
--LEFT(@a, charindex(':', @a, 1)-1)
INSERT INTO @processes
SELECT d.item.value('../../@spid', 'INT') AS spid ,
    d.item.value('../../@currentdb', 'VARCHAR(5)') AS dbid ,
    d.item.value('@procname', 'varchar(50)') ,
    d.item.value('.', 'varchar(max)') AS text ,
    d.item.value('../../@id', 'varchar(50)') AS process ,
    LEFT(d.item.value('../../@waitresource', 'varchar(50)'),
CHARINDEX(':',
d.item.value('../../@waitresource',
'varchar(50)'), 1) - 1) AS waitresource ,
    d.item.value('../../@lasttranstarted', 'datetime') AS transtarted ,
    d.item.value('../../@lastbatchstarted', 'datetime') AS batchcompleted ,
    d.item.value('../../@waittime', 'varchar(50)') AS waittime ,
    CASE WHEN ( d.item.value('../../../../@victim', 'varchar(20)') ) = d.item.value('../../@id',
'varchar(50)')
THEN 'DEADLOCK_VICTIM'
END
FROM @x.nodes('//deadlock[.]/process-list[.]/process[.]/executionStack[.]/frame[.]')
AS d ( item )
SELECT @dbid = CAST(dbid AS INT) ,
    @lockType = WaitResource
FROM @processes
IF @lockType = 'KEY'
BEGIN
    INSERT INTO @resource
    SELECT r.item.value('../../@objectname', 'varchar(50)') AS tablename ,
        r.item.value('../../@indexname', 'varchar(50)') AS indexname ,
        r.item.value('../../@hobtid', 'varchar(50)') AS hobt ,
        r.item.value('@id', 'varchar(50)') AS ownerprocess
    FROM @x.nodes('//deadlock[.]/resource-list[.]/keylock/owner-list/owner')
AS r ( item )
--for now only works on keylocks
END
IF @lockType = 'PAGE'
BEGIN
    INSERT INTO @resource
    SELECT r.item.value('../../@objectname', 'varchar(50)') AS tablename ,
        r.item.value('../../@fileid', 'varchar(50)') AS indexname ,
        r.item.value('../../@pageid', 'varchar(50)') AS hobt ,
        r.item.value('@id', 'varchar(50)') AS ownerprocess
    FROM @x.nodes('//deadlock[.]/resource-list[.]/pagelock/owner-list/owner')
AS r ( item )
--for now only works on keylocks
END
SET @qrysyscat = 'SELECT name, data_space_id, type FROM ' + DB_NAME(@dbid)
+ '.sys.data_spaces'
INSERT INTO @dataspaces
EXEC ( @qrysyscat
)
SET @qrysyscat = 'SELECT allocation_unit_id, container_id, data_space_id FROM '
+ DB_NAME(@dbid) + '.sys.allocation_units'
INSERT INTO @allocationunit
EXEC ( @qrysyscat
)
SET @subjectText = 'DeadLock Detected in [' + @@SERVERNAME + '] On Database: '
+ ISNULL(DB_NAME(@dbid), ' ')
SET @bodyText = '<html>;<head>;<style type="text/css">;
p { font: 11px/24px Verdana, Arial, Helvetica, sans-serif;}
table {
font: 11px/24px Verdana, Arial, Helvetica, sans-serif;
border-collapse: collapse;
}
th.top{
padding:0 0.6em;
text-align: center;
background: #FFC;
}
th {
padding: 0 0.5em;
text-align: left;
background: #FFC;
}
tr{
border-top: 1px solid #FB7A31;
border-bottom: 1px solid #FB7A31;
}
td {
border-bottom: 1px solid #CCC;
padding: 0 0.5em;
}
</style>;
</head>;<body>;' + '<p>A Deadlock has been detected on ' + @@SERVERNAME + '.'
+ DB_NAME(@dbid) + ' of the type: ' + @lockType
+ '. Details are as follows:</p> <br>' + +'<table>'
+ '<tr><th class="top"; colspan="10";>DEADLOCK DETAILS</th></tr>' + '<tr>'
+ CASE @lockType
WHEN 'KEY' THEN ' <th>SPID</th>
<th>RESULT</th>
<th>Lock Type</th>
<th>Proc Name</th>
<th>Table Name</th>
<th>Index Name</th>
<th>Keylock on Partition</th>
<th>Transaction Start</th>
<th>Batch Complete</th>
<th>Wait Time</th>
</tr>' WHEN 'PAGE' THEN ' <th>SPID</th>
<th>RESULT</th>
<th>Lock Type</th>
<th>Proc Name</th>
<th>Table Name</th>
<th>File ID</th>
<th>Page ID</th>
<th>Transaction Start</th>
<th>Batch Complete</th>
<th>Wait Time</th>
</tr>'
END
+ CAST(( SELECT td = ISNULL(p.spid, '') ,
    '' ,
    td = ISNULL(p.Result, '') ,
    '' ,
    td = ISNULL(p.WaitResource, '') ,
    '' ,
    td = ISNULL(p.ProcName, '') ,
    '' ,
    td = ISNULL(r.TableName, '') ,
    '' ,
    td = ISNULL(r.indexName, '') ,
    '' ,
    td = CASE @lockType
WHEN 'KEY' THEN ISNULL(saf.name, '')
WHEN 'PAGE' THEN ISNULL(r.hobt, '')
END ,
    '' ,
    td = CONVERT(VARCHAR, p.TranStarted, 121) ,
    '' ,
    td = CONVERT(VARCHAR, p.BatchComplete, 121) ,
    '' ,
    td = p.WaitTime  
FROM @processes p
    LEFT JOIN @resource r ON p.process = r.OwnerProcess
    LEFT JOIN @allocationunit au ON r.hobt = au.container_id
    LEFT JOIN @dataspaces ds ON au.data_space_id = ds.data_space_id
    LEFT JOIN sys.sysaltfiles saf ON au.data_space_id = saf.groupid
        AND saf.dbid = @dbid
FOR
XML PATH('tr') ,
TYPE
) AS NVARCHAR(MAX)) + '</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail @recipients = 'emailaddress@domainName.com',
@profile_name = 'AlertProfileName', @body_format = 'HTML',
@body = @bodyText, @subject = @subjectText,
@attach_query_result_as_file = 1,
@query = 'SET NOCOUNT ON; SELECT * FROM tempdb.dbo.##tmpDeadlockEvents for xml path(''''), type',
@query_attachment_filename = @fileAtt, @query_result_width = 32767,
@query_no_truncate = 1, @query_result_header = 1

For some environment, this should be good enough. But if you want to keep a count of your deadlock and report based on a set threshold continue reading here.

Set up another SQL agent job that can run at a frequent interval and do a count of deadlocks.  Based on the threshold it can generate alerts with diff profile (info, non-critical, critical, etc).

Code for the 2nd SQL Agent job.

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
USE tempdb
GO
DECLARE @aboveThreshold SMALLINT;
DECLARE @previousThreshold SMALLINT;
DECLARE @CurrentTime DATETIME= GETDATE();
DECLARE @hourback DATETIME = DATEADD(HOUR, -1, @currentTime)
DECLARE @EmailRecipient VARCHAR(1000) ,
@SubjectText VARCHAR(1000) ,
@ProfileName VARCHAR(1000) ,
@Body VARCHAR(MAX)
SELECT @aboveThreshold = COUNT(0)
FROM dba..DeadlockEvents WITH ( NOLOCK )
WHERE AlertTime BETWEEN @hourback AND @CurrentTime
IF ( @aboveThreshold > 25 )
BEGIN
SELECT @previousThreshold = COUNT(0)
FROM DBA..DeadlockEvents WITH ( NOLOCK )
WHERE AlertTime BETWEEN DATEADD(MINUTE, -15, @hourback)
AND DATEADD(MINUTE, -15, @CurrentTime)
--Only send another alert out if the deadlock occurance rate increased from the previous alert, otherwise do not.
IF ( @aboveThreshold > @previousThreshold )
BEGIN
SELECT TOP 1
@EmailRecipient = 'dba@yourCompany.com'
SELECT @SubjectText = 'INFORMATION: ' + @@SERVERNAME
+ ' DEADLOCK OCCURRENCE AVOVE THRESHOLD!!!'
SELECT TOP 1
@ProfileName = name
FROM msdb.dbo.sysmail_profile
WHERE name = 'SQL Server Critical Alerts'
SET @Body = N'<H3 style="font-family:verdana">'
+ CAST(@@SERVERNAME AS VARCHAR)
+ ' Deadlock Above Threshold</H3>'
+ N'<table border="3"; style="font-family:verdana; text-align:left; font-size:8pt">'
+ N'<tr><th>Time</th><th>Server</th><th>Deadlock_lastHour</th>'
+ CAST(( SELECT td = GETDATE() ,
'' ,
td = @@servername ,
td = @aboveThreshold
FOR
XML PATH('tr') ,
TYPE
) AS NVARCHAR(MAX)) + N'</table>';
EXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName,
@recipients = @EmailRecipient, @body_format = 'HTML',
@body = @Body, @subject = @SubjectText;
END
ELSE
PRINT 'DO NOTHING'
END

You can even create an SSRS dashboard with this count and the Operations/Product team can use it.

7 replies on “Deadlock Detection and Reporting part-II”

I almost have this worked out. I am still getting the error Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147024809.

This error is documented here. I am going to test further and check if I can change the value of either one of the parameters.

The following error may occur when setting @query_result_header to 0 and setting @query_no_truncate to 1:
Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147024809.

If you download my code as-is and still getting the error, you have other issues. Check the permission of the Agent service account. Check if @alert_replace_runtime_tokens=1 agent property is set correctly.

By Post Author

Hi Taiob,

Thanks a lot for such a prompt response.
Regarding the issue, xp_cmdshell is kept disabled on our Prod Servers.
Agent permission and Alert token were already in place.Service broker, is_trusthworthly features were also enabled.
Just curious to know if cmdshell is the culprit.
Just for your reference – i followed below stakexchange blog but yours is more resourceful and elaborative.
::https://gist.github.com/TheRockStarDBA/c48912abfcc176cf77b0
Thanks

Leave a Reply to David Waller Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.