Upgrading SQL Server using Availability Groups – Checklist

This is a checklist put together and followed for an upgrade of a pair of physical SQL Server 2012 servers which hosted a single availability group of several terabytes of data with minimal downtime.

The availability group was configured with synchronous commit and automatic failover.

List of Steps

• If possible, stop any ETL processes that put data into the affected databases – less traffic here means less traffic to catch up with when you resume the AG HADR.
• Make sure all AGs hosted by the servers are on the same server
• Put servers, clusters, listeners, everything into Maintenance Mode in SCOM / SQLMonitor / Solarwinds / whatever other monitoring system you use.
• Disable Transaction Log backup jobs on all servers hosting the Availability Group(s)
• Take Log backups
• Configure the Availability Group(s) to manual failover
• Suspend data movement for each database on the secondary server
• Backup the system databases on the secondary server
• Upgrade the secondary server and reboot if required (it might not be required, but it is recommended to do this anyway)
• Resume data movement on the secondary, and allow the servers to synchronise.
• Failover the availability group(s) to the upgraded server with no data loss
• Check that data movement is suspended to the new secondary / unupgraded server due to version incompatibility
• Upgrade other server and then reboot
• Resume data movement on secondary, and allow to synchronise
• (optional) failover AG with no data loss to test all is OK (this isn’t really required, but can be useful for extra peace of mind)
• Reconfigure availability group(s) back to automatic failover (if that’s how it was)
• Re-enable transaction log backup jobs
• Take servers out of maintenance mode in SCOM/SQLMonitor/Solarwinds/Whatever

A couple of useful SQL queries

Suspend / Resume HADR for all databases on the server

This is the suspend version – change suspend to resume and rerun later

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = @sql + 'ALTER DATABASE ' + QUOTENAME(DB_NAME(hars.database_id)) + ' SET HADR SUSPEND;' + CHAR(10) + CHAR(13)
FROM       sys.dm_hadr_database_replica_states AS hars
INNER JOIN sys.dm_hadr_availability_group_states AS hags
        ON hags.group_id = hars.group_id
WHERE hars.is_local = 1
ORDER BY DB_NAME(hars.database_id);
PRINT @sql; -- optional
EXEC (@sql);

Check health of replicas in the AG (run on primary)

SELECT ag.name,
ar.replica_server_name,
hars.is_local,
hars.role_desc,
hars.operational_state_desc,
hars.connected_state_desc,
hars.recovery_health_desc,
hars.synchronization_health_desc,
hars.last_connect_error_number,
hars.last_connect_error_description,
hars.last_connect_error_timestamp,
ag.failure_condition_level,
ag.health_check_timeout,
ar.replica_server_name,
ar.owner_sid,
ar.endpoint_url,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.session_timeout,
ar.primary_role_allow_connections_desc,
ar.secondary_role_allow_connections_desc,
ar.backup_priority,
ag.automated_backup_preference_desc,
ar.read_only_routing_url
FROM sys.dm_hadr_availability_replica_states AS hars
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = hars.group_id
INNER JOIN sys.availability_replicas AS ar
ON ar.group_id = ag.group_id
AND ar.replica_id = hars.replica_id;

Monitor progress of catchup/synchronisation (run on primary)

SELECT ag.name,
adc.database_name,
ar.replica_server_name,
hdrs.is_local,
hdrs.synchronization_state,
hdrs.synchronization_state_desc,
hdrs.is_commit_participant,
hdrs.synchronization_health_desc,
hdrs.database_state_desc,
hdrs.is_suspended,
hdrs.suspend_reason,
hdrs.suspend_reason_desc,
hdrs.last_sent_time,
hdrs.last_received_time,
hdrs.last_hardened_time,
hdrs.last_redone_time,
hdrs.log_send_queue_size,
hdrs.log_send_rate,
hdrs.redo_queue_size,
hdrs.redo_rate,
hdrs.filestream_send_rate,
CEILING(hdrs.redo_queue_size / hdrs.redo_rate) / 60.0 AS est_redo_completion_minutes, -- approximately
hdrs.last_commit_time,
hdrs.low_water_mark_for_ghosts,
hdrs.secondary_lag_seconds -- for SQL 2016 and above
FROM sys.dm_hadr_database_replica_states AS hdrs
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = hdrs.group_id
INNER JOIN sys.availability_databases_cluster AS adc
ON adc.group_database_id = hdrs.group_database_id
INNER JOIN sys.availability_replicas AS ar
ON ar.replica_id = hdrs.replica_id
WHERE hdrs.log_send_queue_size <> 0
OR hdrs.redo_queue_size <> 0;

SQL Server Configuration on Windows Server Core Edition

You can’t run SQL Server Configuration Manager on a Windows Core server – no UI.

You can’t run SQL Server Configuration Manager on another server and direct it to point to your server running Windows Core.

But what you can do is run Computer Management from your full-fat server, and connect that to your Core server to do stuff there. And one of the things you can do is run the SQL configuration tools…

How has it taken me so long to find that out? Oh yes – I hardly ever *see* a Windows server core edition installation. Probably in part because of this sort of thing. Heigh ho.

Postcode Data and the Geography of a Drunkard’s Walk in SQL Server

I was thinking about postcode data, as it might come in handy for some reporting / planning / analysis work later.

There’s a set of scripts to create tables and populate with postcode data in SQL Server, which was my starting point.

A note for those from outside the UK – the UK postcode system breaks down into a few letters and numbers, generally of the form AAXX XAA (where A is a letter, and X is a digit). The first part is known as the outbound code – the letters are (generally) related to a major city in the area – NE for Newcastle, LS for Leeds, OX for Oxford, for examples); the one or two digits that follow represent an area in that town or, in more rural areas, this could be a “post town” – for example the IP postcode area. Here, the IP represents Ipswich, in Suffolk, but the whole postcode area covers most of Suffolk as well as some parts of Norfolk…

The code for that is relatively straightforward:

DECLARE @Line VARCHAR(MAX);

SELECT  @Line = STUFF(
                (
                    SELECT      ', ' + CAST(lng AS VARCHAR(20)) + ' ' + CAST(lat AS VARCHAR(20))
                    FROM        dbo.outcodepostcodes
                    WHERE       outcode LIKE 'IP%' and lat <> 0
                    ORDER BY    CAST(STUFF(outcode, 1, 2, '') AS INT)
                    FOR XML PATH('')
                ),
                1,
                2,
                ''
                     );

SELECT  geography::Parse('LINESTRING(' + @line + ')');

Something to note – the lat <> 0 bit – The UK has “non-geographic” postcodes as well as the normal ones. These, obviously, have no positional information, and, in this data source, that’s represented by 0 values in the lat and lng fields.
Also, I’ve switched to the Mercator projection. If you use the default (equirectangular) projection option, it’s going to look wrong unless the data you’re mapping is very close to the equator.

Now, knowing most of the above, I wondered what it would look like if you drew a line from the center of each postcode outbound code to the next one for each post town. In numerical order. It turns out that the IP code looks like this:

A bit of a drunkard’s walk… and the other areas are similar, and don’t really look much like anything. Until you come to put them all on one result set:

Looks familiar?

UK Postcode Map Query Code

You want the code for this, because of course you do…

DECLARE @outcodemap TABLE (outcode VARCHAR(9), map VARCHAR(MAX));

DECLARE @oc AS VARCHAR(9);
DECLARE @map VARCHAR(MAX);

DECLARE o CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT  DISTINCT
            LEFT(outcode, CASE WHEN outcode LIKE '[A-Z][0-9]%' THEN 1 ELSE 2 end)
    FROM    dbo.outcodepostcodes
    WHERE
            lat <> 0
            AND
            (
                outcode LIKE '[A-Z][A-Z][0-9]'
                OR  outcode LIKE '[A-Z][A-Z][0-9][0-9]'
                OR  outcode LIKE '[A-Z][0-9]'
                OR  outcode LIKE '[A-Z][0-9][0-9]'
            );

OPEN o;

FETCH NEXT FROM o
INTO @oc;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT  @map = STUFF(
                   (
                       SELECT   ', ' + CAST(lng AS VARCHAR(20)) + ' ' + CAST(lat AS VARCHAR(20))
                       FROM     dbo.outcodepostcodes
                       WHERE
                                lat <> 0
                                AND (outcode LIKE @oc + '[0-9]' OR outcode LIKE @oc + '[0-9][0-9]')                                
                       ORDER BY CASE
                                    WHEN outcode LIKE '[A-Z][0-9]%' THEN
                                        CAST(STUFF(outcode, 1, 1, '') AS INT)
                                ELSE
                                    CAST(STUFF(outcode, 1, 2, '') AS INT)
                                END
                       FOR XML PATH('')
                   ),
                   1,
                   2,
                   ''
                        );

    INSERT INTO @outcodemap (outcode, map) VALUES ( @oc, @map );

    FETCH NEXT FROM o
    INTO @oc;
END;

CLOSE o;
DEALLOCATE o;

SELECT  outcode,
        CASE
            WHEN CHARINDEX(',', map) > 0 THEN
                geography::Parse('LINESTRING(' + map + ')')
            WHEN DATALENGTH(TRIM(map)) > 1 THEN
                geography::Parse('POINT(' + map + ')') -- just in case...
        END
FROM    @outcodemap;

Again, there are a couple of things to note – the code could probably do with being tidied up, and I’m ignoring those odd postcodes which have AAXA or AAXXA as the outbound. Yeah, the UK postcode system is kinda messed up in places!

Wrapping up CPU Load Graphs into a Stored Procedure

After the fun of the last few days, I thought it might be a better idea if I wrapped this all up into a single stored procedure. So I did that.

Parameters

I have implemented the following parameters:

• @ShowCPUAsLines (BIT, default 1) – show line graphs rather than polygons. Set to 0 if you want polygons
• @ShowDeadlock (BIT, default 1) – show Deadlock information. Set to 0 to suppress
• @DeadlockBlobRadius (TINYINT, default 1) – radius of deadlock circles on the graph. If zero, then will be displayed as a point rather than as a circle, but that’s pretty much unreadable…
• @OutputDeadlockTable (BIT, default 1) – outputs the deadlock information table used internally. Note that this will return information about all the deadlocks that have been identified from the logs, not just the ones shown
• @ShowEndBar (BIT, default 1) – if you don’t like that “Here’s where we are now” bar, set to zero
• @EndBarWidth (TINYINT, default 1) – width of that now / end bar. If zero, then a line is drawn rather than a bar

The code

IF OBJECT_ID('dbo.PerformanceGraph', 'P') IS NULL
BEGIN
    EXEC ('CREATE PROCEDURE dbo.PerformanceGraph AS BEGIN SELECT 1 END');
END;
GO


ALTER PROCEDURE PerformanceGraph
    @ShowCPUAsLines      BIT     = 1,
    @ShowDeadlock        BIT     = 1,
    @DeadlockBlobRadius  TINYINT = 1,
    @OutputDeadlockTable BIT     = 1,
    @ShowEndBar          BIT     = 1,
    @EndBarWidth         TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    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),
            @DeadlockBlobs   AS VARCHAR(8000),
            @DeadlockRecords AS INT;

    DECLARE @OutputGeometry TABLE (ShapeInfo GEOMETRY, Caption VARCHAR(20));

    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]) - COUNT(*) OVER (PARTITION BY 1) 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]) - COUNT(*) OVER (PARTITION BY 1) 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])
                                               - COUNT(*) OVER (PARTITION BY 1) 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);

    IF @ShowCPUAsLines = 1
    BEGIN
        INSERT INTO @OutputGeometry (ShapeInfo, Caption)
        VALUES
        (geometry::Parse('LINESTRING(' + @CPUSQLOut + ')'), 'CPU-SQL'),
        (geometry::Parse('LINESTRING(' + @CPUOtherOut + ')'), 'CPU-Other'),
        (geometry::Parse('LINESTRING(' + @CPUIdleOut + ')'), 'CPU-Idle');
    END;
    ELSE
    BEGIN
        INSERT INTO @OutputGeometry (ShapeInfo, Caption)
        VALUES
        (geometry::STGeomFromText(
                                     'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUSQLOut + ', '
                                     + '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
                                     0
                                 ), 'CPU-SQL'),
        (geometry::STGeomFromText(
                                     'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUOtherOut + ', '
                                     + '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
                                     0
                                 ), 'CPU-Other'),
        (geometry::STGeomFromText(
                                     'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUIdleOut + ', '
                                     + '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
                                     0
                                 ), 'CPU-Idle');
    END;

    DECLARE @XMLDeadlocks TABLE ([XML] XML, UTCTime DATETIME, LocalTime DATETIME, MinutesAgo INT);

    IF @ShowDeadlock = 1
    BEGIN;
        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.
                    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'
        )
        INSERT INTO @XMLDeadlocks ([XML], UTCTime, LocalTime, MinutesAgo)
        SELECT  xmldates.deadlock_xml,
                xmldates.deadlock_UTCtime,
                xmldates.deadlock_LocalTime,
                DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
        FROM    xmldates;

        SELECT  @DeadlockPoints  = STUFF(
                                   (
                                       SELECT   DISTINCT
                                                ', (' + CAST((0 - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
                                       FROM     @XMLDeadlocks xml
                                       WHERE    xml.MinutesAgo <= @CPURecords
                                       FOR XML PATH('')
                                   ),
                                   1,
                                   2,
                                   ''
                                        ),
                @DeadlockBlobs
                                 = STUFF(
                                   (
-- SQL Prompt formatting off
                                       SELECT       DISTINCT ', CURVEPOLYGON(CIRCULARSTRING('
                                                    + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, ' 
                                                    + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
                                                    + CAST((0-xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
                                                    + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
                                                    + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
                                                    + '))'
-- SQL Prompt formatting on
                                       FROM     @XMLDeadlocks xml
                                       WHERE    xml.MinutesAgo <= @CPURecords
                                       FOR XML PATH('')
                                   ),
                                   1,
                                   2,
                                   ''
                                        ),
                @DeadlockRecords =
        (
            SELECT  COUNT(*) FROM   @XMLDeadlocks xml WHERE xml.MinutesAgo <= @CPURecords
        );

        IF @DeadlockBlobRadius = 0
        BEGIN -- show points
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('MULTIPOINT(' + @DeadlockPoints + ')'), -- ShapeInfo - geometry
                'Deadlocks'                                             -- Caption - varchar(20)
                );
        END;
        ELSE
        BEGIN -- show blobs
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('GEOMETRYCOLLECTION(' + @DeadlockBlobs + ')'), -- ShapeInfo - geometry
                'Deadlocks'                                                 -- Caption - varchar(20)
                );
        END;

        IF @OutputDeadlockTable = 1
        BEGIN
            SELECT  UTCTime, LocalTime, MinutesAgo, XML FROM @XMLDeadlocks xd ORDER BY  xd.MinutesAgo;
        END;
    END;

    --End Bar
    IF @ShowEndBar = 1
    BEGIN
        IF @EndBarWidth = 0
        BEGIN
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('LINESTRING(0 0,0 100)'), -- ShapeInfo - geometry
                'Now'                                   -- Caption - varchar(100)
                );
        END;
        ELSE
        BEGIN
            INSERT INTO @OutputGeometry (ShapeInfo, Caption)
            VALUES
            (   geometry::Parse('POLYGON((0 0,0 100, ' + CAST(@EndBarWidth AS CHAR(3)) + ' 100, '
                                + CAST(@EndBarWidth AS CHAR(3)) + ' 0, 0 0))'
                               ),   -- ShapeInfo - geometry
                'Now'               -- Caption - varchar(100)
                );
        END;
    END;

    -- Final Output
    SELECT  ShapeInfo, Caption FROM @OutputGeometry;
END;
GO

What now?

There may be other functionality that can be added to this – but I can’t think of anything right now.

But I probably won’t be doing any more on this in the near future anyway, as my Redgate SQL Monitor licence has arrived, so I can get back to doing things properly.

CPU Load Graphs & Deadlocks 2 – Tweaks

Following on from the earlier post CPU Load Graphs and Deadlocks

Bug fix

Oops. Of course, I wasn’t expecting this system to have multiple deadlocks in the same minute, but, if you do have that situation and you use the code I posted earlier, you won’t see the duplicates as the polygons are implemented in such a way that overlaps / intersections are not shown – see examples on Simple-Talk’s Introduction to Spatial Data. So I’ve tweaked the statement to only return DISTINCT values of MinutesAgo.

Axis Shift

Previously, the x axis (time) was running from 0 to 256 – where the 256 value represented the most recent data. Although this preserved the left-to-right timeline feel, the actual numbers felt wrong, so I’ve moved the x axis so zero is at the right, rather than the left, and the x axis is properly showing “minutes ago”.

I’ve also added a bar at the right hand end as a more visual cue to “this is where we are now”

The new 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),
        @DeadlockBlobs      AS VARCHAR(8000),
        @DeadlockBlobRadius AS INT          = 1,
        @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]) - COUNT(*) OVER (PARTITION BY 1) 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]) - COUNT(*) OVER (PARTITION BY 1) 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]) - COUNT(*) OVER (PARTITION BY 1) 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  DISTINCT -- <== That's the bug fix, right there.  That one word.  Oh yeah.
            DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
    FROM    xmldates
)

SELECT  @DeadlockPoints  = STUFF(
                           (
                               SELECT       ', (' + CAST((0 - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
                               FROM         xml
                               WHERE        xml.MinutesAgo <= @CPURecords
                               ORDER BY     xml.MinutesAgo
                               FOR XML PATH('')
                           ),
                           1,
                           2,
                           ''
                                ),
        @DeadlockBlobs
                         = STUFF(
                           (
-- SQL Prompt formatting off
                               SELECT       ', CURVEPOLYGON(CIRCULARSTRING('
                                            + CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, ' 
                                            + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
                                            + CAST((0-xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
                                            + CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
                                            + CAST((0-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::STGeomFromText('MULTIPOINT(' + @DeadlockPoints + ')', 0) AS Deadlocks,
--        'Deadlocks'                                                        AS Caption
UNION ALL
SELECT  geometry::Parse('GEOMETRYCOLLECTION (' + @DeadlockBlobs + ')') AS DeadlockBlobs,
        'Deadlocks'                                                    AS caption
UNION ALL
SELECT  geometry::Parse('POLYGON ((0 0, 2 0, 2 100, 0 100, 0 0))') AS NowBar, 'NOW' AS caption; -- NEW: "Now" bar

And the output:

And, for comparison, without that DISTINCT:

Like I said, oops… Oh well, at least I spotted it early.

CPU Load Graphs with Deadlocks

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!

CPU Load graphs

Following on from my recent post about CPU load discrepancies in the ring buffer, and in conjunction with some other stuff I’m doing at the moment, I found myself trying to draw graphs of the CPU load in SQL.

Line graphs

I (eventually) came up with this:

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);

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,
              ''
                   );

SELECT  geometry::Parse('MULTILINESTRING( (' + @CPUSQLOut + '), (' + @CPUOtherOut + '),(' + @CPUIdleOut + ') )') AS CPULoad;


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;

I’ve left two versions for display, and they look like this:

The first version is a MultilineString, consisting of multiple lists of x y pairs. Unfortunately (for my purposes) this causes them to be rendered as if they belong to a single shape, and they share the same colour and would share the same caption if I had bothered to submit a field of labels in the query resultset

The second version is the one I prefer – it has the lines in different colours (based on the order that the line appears in the resultset), the lines can be labelled individually. The downside is that it is (in my case) a bit more of a faff to build. But I suspect there may have been different options there – this is my first foray into this.

Solid graphs

What if we want solids rather than lines? The polygon geometry type is our friend. There’s a quick mod needed to the query in order to generate the remaining points needed to complete the polygon, though – it needs the start & end points to match, and in order to make some sense, it would be good to have them with a solid baseline on the x axis… Oh, and the polygon points need to be bracketed further… …and the server I’m looking at is mostly idle, so wouldn’t it be better to have the shading for the idle bar to start at the top?

With that in mind, the query now looks like this:

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;             -- this is new

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);    -- this is new

SELECT  geometry::STGeomFromText(
                                    'POLYGON((0 0, ' + @CPUSQLOut + ', ' + CAST(@CPURecords AS VARCHAR(10))
                                    + ' 0, 0 0 ))',
                                    0
                                ) AS CPULoad,
        'CPU-SQL'                 AS Caption
UNION ALL
SELECT  geometry::STGeomFromText(
                                    'POLYGON((0 0, ' + @CPUOtherOut + ', ' + CAST(@CPURecords AS VARCHAR(10))
                                    + ' 0, 0 0 ))',
                                    0
                                ) AS CPULoad,
        'CPU-Other'               AS Caption
UNION ALL
SELECT  geometry::STGeomFromText(
                                    'POLYGON((0 0, ' + @CPUIdleOut + ', ' + CAST(@CPURecords AS VARCHAR(10))
                                    + ' 0, 0 0 ))',
                                    0
                                ) AS CPULoad,
        'CPU-Idle'                AS Caption;

SELECT  geometry::STGeomFromText(
                                    'POLYGON((0 0, ' + @CPUSQLOut + ', ' + CAST(@CPURecords AS VARCHAR(10))
                                    + ' 0, 0 0 ))',
                                    0
                                ) AS CPULoad,
        'CPU-SQL'                 AS Caption
UNION ALL
SELECT  geometry::STGeomFromText(
                                    'POLYGON((0 0, ' + @CPUOtherOut + ', ' + CAST(@CPURecords AS VARCHAR(10))
                                    + ' 0, 0 0 ))',
                                    0
                                ) AS CPULoad,
        'CPU-Other'               AS Caption
UNION ALL
SELECT  geometry::STGeomFromText(
                                    'POLYGON((0 100, ' + @CPUIdleOut + ', ' + CAST(@CPURecords AS VARCHAR(10))
                                    + ' 100, 0 100 ))',
                                    0
                                ) AS CPULoad,
        'CPU-Idle'                AS Caption;

And the output from this, with a little bit of caption-selection and resizing of windows in SSMS:

I’m not sure which of these I prefer. The big block of idle makes it a bit harder to read the first graph; the idle top-hanging looks a bit odd. Oh well.

But…

The obvious question, though, is why haven’t I got SQL Monitor to do this for me? I’m working on it…