Continuing on disappearing down the rabbit hole… Send help! Or coffee!
Deadlocks
I’m always being asked, sometimes being asked, somebody asked me once “what about the deadlocks? Can you show them on your CPU load graphs?”
Oh, go on.
First, find your deadlock information. Fortunately(?), the system I’m working against here experiences them regularly. I just need to find the information and figure out how to add it to this monstrosity.
Rummaging around in the source code for sp_BlitzIndex, there’s a useful-looking chunk of code that extracts some deadlock information:
/*Grab the initial set of XML to parse*/
SET @d = CONVERT(VARCHAR(40), GETDATE(), 109);
RAISERROR('Grab the initial set of XML to parse at %s', 0, 1, @d) WITH NOWAIT;
WITH xml
AS ( SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file(@EventSessionPath, NULL, NULL, NULL) ) -- parameter defaults to 'system_health*.xel'
SELECT TOP ( @Top ) ISNULL(xml.deadlock_xml, '') AS deadlock_xml
INTO #deadlock_data
FROM xml
LEFT JOIN #t AS t
ON 1 = 1
WHERE xml.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
AND xml.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') >= @StartDate
AND xml.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') < @EndDate
ORDER BY xml.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') DESC
OPTION ( RECOMPILE );
It’s a bit more than I need/want for this exercise, but it’s a quick clue to where to look…
After a bit of fiddling, we have the following chunk of code that reads the system_health file and pulls out deadlock records, the date/time of the deadlock, and converts that date/time from UTC into local time
WITH
xmlsource AS
(
SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
SELECT xmlsource.deadlock_xml,
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC. Need converting to local.
DATEADD(
mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()),
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
) AS deadlock_LocalTime
FROM xmlsource
WHERE xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
)
SELECT * FROM xmldates;
That gives us the following resultset that we can work with:

However, because we’re plotting a graph in SQL, it can only handle numbers, not dates. Given the CPU information we’ve been dealing with is a per-minute record, we need to do a bit of date arithmetic
WITH
xmlsource AS
(
SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
SELECT xmlsource.deadlock_xml,
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC. Need converting to local.
DATEADD(
mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()),
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
) AS deadlock_LocalTime
FROM xmlsource
WHERE xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
SELECT xmldates.deadlock_xml,
xmldates.deadlock_UTCtime,
xmldates.deadlock_LocalTime,
DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
FROM xmldates
)
SELECT * FROM xml;
That gives us a useful column, “MinutesAgo”, that we can use when plotting points:

and we can only get just over 4 hours of data there, we can do some other work to cut this resultset down further when we build the geometry strings.
Lines and Points
Adding our new code to our old CPU load lines code, and creating the deadlocks as a set of points to plot, gives us this code:
DECLARE @ts_now BIGINT =
(
SELECT TOP (1)
cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK)
ORDER BY cpu_ticks DESC
);
DECLARE @CPUSQLOut AS VARCHAR(8000),
@CPUIdleOut AS VARCHAR(8000),
@CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockPoints AS VARCHAR(8000),
@DeadlockRecords AS INT;
WITH
CPULoad AS
(
SELECT TOP (256)
y.SQLProcessUtilization AS [SQL Server Process CPU Utilization],
y.SystemIdle AS [System Idle Process],
100 - (y.SystemIdle + y.SQLProcessUtilization) AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
FROM
(
SELECT x.record.value('(./Record/@id)[1]', 'int') AS record_id,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
x.timestamp
FROM
(
SELECT timestamp,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY y.timestamp DESC
)
SELECT @CPUSQLOut
= STUFF(
(
SELECT ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUIdleOut
= STUFF(
(
SELECT ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUOtherOut
= STUFF(
(
SELECT ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPURecords = (SELECT COUNT(*) FROM CPULoad);
WITH
xmlsource AS
(
SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
SELECT xmlsource.deadlock_xml,
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC. Need converting to local.
DATEADD(
mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()),
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
) AS deadlock_LocalTime
FROM xmlsource
WHERE xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
SELECT xmldates.deadlock_xml,
xmldates.deadlock_UTCtime,
xmldates.deadlock_LocalTime,
DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
FROM xmldates
)
SELECT @DeadlockPoints = STUFF(
(
SELECT ', (' + CAST((@CPURecords - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
FROM xml
WHERE xml.MinutesAgo <= @CPURecords
ORDER BY xml.MinutesAgo
FOR XML PATH('')
),
1,
2,
''
),
@DeadlockRecords = (SELECT COUNT(*) FROM xml WHERE xml.MinutesAgo <= @CPURecords);
SELECT geometry::STGeomFromText('LINESTRING(' + @CPUSQLOut + ')', 0) AS CPULoad, 'CPU-SQL' AS Caption
UNION ALL
SELECT geometry::STGeomFromText('LINESTRING(' + @CPUOtherOut + ')', 0) AS CPULoad, 'CPU-Other' AS Caption
UNION ALL
SELECT geometry::STGeomFromText('LINESTRING(' + @CPUIdleOut + ')', 0) AS CPULoad, 'CPU-Idle' AS Caption
UNION ALL
SELECT geometry::STGeomFromText('MULTIPOINT(' + @DeadlockPoints + ')', 0) AS Deadlocks, 'Deadlocks' AS Caption;
And the results:

Am I going blind? Or have they disappeared? Using the zoom, we still can’t really see them…

Not really surprising. A line of no width and no length – never going to be easy to see…
Lines and Blobs
Instead of using MULTIPOINT, let’s have a go with some filled circles. This is using the CURVEPOLYGON / CIRCULARSTRING combination (if you omit the CIRCULARSTRING bit you end up with, in this case, little diamonds…). The code below has a few extra bits thrown in – the blobradius is configurable, for example.
DECLARE @ts_now BIGINT =
(
SELECT TOP (1)
cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK)
ORDER BY cpu_ticks DESC
);
DECLARE @CPUSQLOut AS VARCHAR(8000),
@CPUIdleOut AS VARCHAR(8000),
@CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockBlobs AS VARCHAR(8000),
@DeadlockBlobRadius AS INT = 1, -- seems to be enough
@DeadlockRecords AS INT; -- not used, but might come in handy later
WITH
CPULoad AS
(
SELECT TOP (256)
y.SQLProcessUtilization AS [SQL Server Process CPU Utilization],
y.SystemIdle AS [System Idle Process],
100 - (y.SystemIdle + y.SQLProcessUtilization) AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
FROM
(
SELECT x.record.value('(./Record/@id)[1]', 'int') AS record_id,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
x.timestamp
FROM
(
SELECT timestamp,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY y.timestamp DESC
)
SELECT @CPUSQLOut
= STUFF(
(
SELECT ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUIdleOut
= STUFF(
(
SELECT ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUOtherOut
= STUFF(
(
SELECT ',' + CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPURecords = (SELECT COUNT(*) FROM CPULoad);
WITH
xmlsource AS
(
SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
SELECT xmlsource.deadlock_xml,
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC. Need to convert to local.
DATEADD(
mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()),
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
) AS deadlock_LocalTime
FROM xmlsource
WHERE xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
),
xml AS
(
SELECT DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo FROM xmldates -- only interested in the minutes
)
SELECT
@DeadlockBlobs
= STUFF(
(
-- SQL Prompt formatting off
SELECT ', CURVEPOLYGON(CIRCULARSTRING('
+ CAST((@CPURecords - xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
+ CAST((@CPURecords - xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
+ CAST((@CPURecords - xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
+ CAST((@CPURecords - xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
+ CAST((@CPURecords - xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
+ '))'
-- SQL Prompt formatting on
FROM xml
WHERE xml.MinutesAgo <= @CPURecords
ORDER BY xml.MinutesAgo
FOR XML PATH('')
),
1,
2,
''
),
@DeadlockRecords = (SELECT COUNT(*) FROM xml WHERE xml.MinutesAgo <= @CPURecords);
SELECT geometry::STGeomFromText('LINESTRING(' + @CPUSQLOut + ')', 0) AS CPULoad, 'CPU-SQL' AS Caption
UNION ALL
SELECT geometry::STGeomFromText('LINESTRING(' + @CPUOtherOut + ')', 0) AS CPULoad,
'CPU-Other' AS Caption
UNION ALL
SELECT geometry::STGeomFromText('LINESTRING(' + @CPUIdleOut + ')', 0) AS CPULoad,
'CPU-Idle' AS Caption
UNION ALL
SELECT geometry::Parse('GEOMETRYCOLLECTION (' + @DeadlockBlobs + ')') AS DeadlockBlobs,
'Deadlocks' AS caption;
Run it

That looks better
I probably ought to clean up the code a bit, change the x-axis, and a few other odds and sods, before I take this much further, but this already proving useful, even if it’s only to give me some fun learning the Geometry stuff!