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?
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)', 'int') as record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'bigint') as SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', '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.