Shredding the Deadlock graph from the WMI Deadlock alert

I use the WMI alert to persist the deadlock graph to a table. This way I can query it later. The deadlock graph is in the form of an XML document and is difficult to read in the New Query Window of SQL Server Management Studio.

​At the end of the post is a script I use to shred the XML document to make it more readable.

Here is the table structure I use:

CREATE TABLE [dbo].[DeadLockEvents](
 [PK] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [AlertTime] [datetime] NULL,
 [DeadLockGraph] [xml] NULL
)
GO

Here is my WMI Alert - this is modified from something I got from Bob Beauchemin(https://www.sqlskills.com/blogs/bobb/using-wmi-and-sql-agent-alerts-part-1/).

​Here is my WMI Alert:

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock  Graph Report',
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=5,
  @category_name=N'[Uncategorized]',
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
  @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
  @job_name ='Capture Deadlock Graph'

Here is my SQL Server job:

use msdb
GO
if not exists(select * from sysjobs where name='Capture Deadlock Graph')
EXEC sp_add_job @job_name=N'Capture Deadlock Graph',
  @description=N'Job for responding to DEADLOCK_GRAPH events',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'sa'
GO
declare @jobId BINARY(16)
select @jobId  = job_id from msdb.dbo.sysjobs where
name='Capture Deadlock Graph'
if not exists(select * From sysjobsteps where job_id=@jobId and step_name='Insert graph into LogEvents')
EXEC sp_add_jobstep @job_id=@jobId,
@step_name=N'Insert graph into LogEvents', @step_id=1,
  @command=N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_NONE(WMI(TextData)))'')',
  @database_name=N'master' 
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
  @alert_replace_runtime_tokens=1,
  @use_databasemail=1
GO
  
Here is the shredding script I use to render the XML deadlock graph more readable. Here I am looking at the top 100 most recent deadlock events.

set rowcount 100
select Deadlockgraph.value('(/TextData/deadlock-list/deadlock/@victim)[1]','sysname')[Blocked Process HostName],
X.x.value('(../@waitresource)','sysname')[waitresource],
X.x.value('(../@status)','sysname')[status],
X.x.value('(../@spid)','sysname')[spid],
X.x.value('(../@clientapp)','sysname')[clientapp],
X.x.value('(../@lastbatchstarted)','sysname')[lastbatchstarted],
X.x.value('(../@lastbatchcompleted)','sysname')[lastbatchcompleted],
X.x.value('(../@loginname)','sysname')[loginname],
X.x.value('(../@isolationlevel)','sysname')[isolationlevel],
X.x.value('(../@lockTimeout)','sysname')[locktimeout],
X.x.value('(../@clientoption1)','sysname')[clientOption1],
X.x.value('(../@clientoption2)','sysname')[clientOption2],
X.x.value('(frame/@procname)[1]','sysname')[procname],
X.x.query('data(frame)')[frame],
X.x.query('data(../inputbuf)')[inputnbuffer],
Deadlockgraph.value('count(/TextData/deadlock-list/deadlock/process-list/process)', 'int') as Count,
DeadlockGraph
from DeadlockEvents
CROSS APPLY Deadlockgraph.nodes('/TextData/deadlock-list/deadlock/process-list/process/executionStack') AS X(x)
order by  6 desc
​
​Here is a link to the script.

deadlock_shredder.sql
File Size:	1 kb
File Type:	sql

Download File

---