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…

Advertisements
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

4 Responses to CPU Load graphs

  1. Maninder Singh says:

    Just Fantastic.. What do you eat for breakfast?

  2. Oddvar Eikli says:

    Very impressive and creative!

  3. Pingback: CPU Load Graphs with Deadlocks | The Lone DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.