Description
This metric tracks the number of deadlocks from the system_health extended event session.
Metric Definition
To create the metric, click on Options > Custom Resource Metrics and configure similar to below.
Database Versions: 9.0(SQL 2008 and SQL 2008R2)
Display Name: Deadlocks
Category: Sessions
Units: Deadlocks
Metric Type: Single Value
Frequency: 600 (once every 10 minutes)
Timeout: 20
SQL Script:
DECLARE @XMLData xml
SELECT TOP 1 @XMLData = CAST(target_data AS xml)
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health'
SELECT count(1)
FROM @XMLData.nodes('//event[@name="xml_deadlock_report"]') AS X (Col)
WHERE col.value('(data/value)[1]', 'VARCHAR(MAX)') LIKE '<deadlock%'
AND DATEDIFF(MINUTE, col.value('(./@timestamp)', 'DATETIME'), GETDATE()) < 10.0