SQL Server CPU utilization in Graphical form

Finally I can present something useful I've made with the help of my Spatial Data knowledge.
I will use Spatial Data to represent SQL Server CPU utilization.

How many times you connected to a SQL Server instance with complains to its performance?

What would you do the first?
I know that some people, instead of connecting to the instance open Remote Desktop Connection, which utilizes very important resources at the most critical time of the server, and simply run PerfMon.


I'd say it is not a bad idea, but it would be better to get these data remotely. Even better, if you look not at live counter, but load the file with counter's data, that would give you the historical perspective of your counters.
However, you must have these counters set. If you want to know how to do it, see my earlier post: "How to Automate collecting Perfmon Counters of Interest".

What if you did not know that and did not set your performance counters? Are you completely lost?

Not completely.
Benjamin Nevarez described in his blog on how to "Get CPU Utilization Data from SQL Server" and he inspired me to draw a diagram of the CPU utilization.

I've slightly changed his script and got the following:

DECLARE @gc VARCHAR(MAX), @gi VARCHAR(MAX);
WITH BR_Data as (
 SELECT timestamp, CONVERT(XML, record) as record
 FROM sys.dm_os_ring_buffers
 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%<SystemHealth>%'
), Extracted_XML as (
 SELECT timestamp, record.value('(./Record/@id)[1]', 'int') as record_id,
  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'bigint') as SystemIdle,
  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'bigint') as SQLCPU
 FROM BR_Data
), CPU_Data as (
 SELECT record_id, ROW_NUMBER() OVER(ORDER BY record_id) as rn,
    dateadd(ms, -1 * ((SELECT ms_ticks  FROM sys.dm_os_sys_info) - [timestamp]), GETDATE()) as EventTime,
    SQLCPU, SystemIdle, 100 - SystemIdle - SQLCPU as OtherCPU
 FROM Extracted_XML )
SELECT @gc = CAST((SELECT  CAST(d1.rn as VARCHAR) + ' ' + CAST(d1.SQLCPU as VARCHAR) + ',' FROM CPU_Data as d1 ORDER BY d1.rn FOR XML PATH('')) as VARCHAR(MAX)),
@gi = CAST((SELECT  CAST(d1.rn as VARCHAR) + ' ' + CAST(d1.OtherCPU as VARCHAR) + ',' FROM CPU_Data as d1 ORDER BY d1.rn FOR XML PATH('')) as VARCHAR(MAX))
OPTION (RECOMPILE);

SELECT CAST('LINESTRING(' + LEFT(@gc,LEN(@gc)-1) + ')' as GEOMETRY), 'SQL CPU %' as Measure
UNION ALL
SELECT CAST('LINESTRING(1 100,2 100)' as GEOMETRY), ''
UNION ALL
SELECT CAST('LINESTRING(' + LEFT(@gi,LEN(@gi)-1) + ')' as GEOMETRY), 'Other CPU %'; 

I've got following as a result:

Then I've selected the second tab and got my SQL Server CPU utilization diagram:

That diagram provides you about 260 last minutes of Server CPU usage and measured in percents.

As you can see my SQL Server is mostly doing nothing and only during that blog-post writing in the last half and hour or so it is heavily running test scripts to over-utilize my CPU, but it still barely goes more than 60% of CPU (Blue line).

The Red line represents all other processes besides of SQL Server and you can tell if anything else from outside is impacting your performance.

Hope you'll like it.