Deadlock Detection and Reporting part-II

This is a continuation of the series about deadlock.  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 purse 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 DeadlockEvents
ORDER BY Id DESC
INSERT INTO ##tmpDeadlockEvents
VALUES ( @x )
SELECT TOP 1
@fileAtt = 'DeadLockGraph_' + CAST(ID AS VARCHAR) + '.xdl'
FROM 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 = 'email@yourDomain.com',
@profile_name = 'AlertProfileName', @body_format = 'HTML',
@body = @bodyText, @subject = @subjectText,
@attach_query_result_as_file = 1,
@query = 'SET NOCOUNT ON; SELECT * FROM ##tmpDeadlockEvents for xml path(""), type',
@query_attachment_filename = @fileAtt, @query_result_width = 32767,
@query_no_truncate = 1, @query_result_header = 0

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 which 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 a SSRS dashboard with this count and Operations/Product team can use it.

 

FacebookTwitterGoogle+Share