This is a simple piece of code pinched from the Microsoft Performance Dashboard, but it’s useful and needs highlighting on its own as I tend to use it extensively.
Effectively it shows not only the CPU being used by SQL Server but also the CPU consumed by other processes.
If you’re trying to prove whether or not your SQL Server is to blame for the CPU suddenly hitting the roof and grinding things to a halt, then this is something you want to use.
I’m sure you’ve all been in the situation where someone says “it has to be SQL Server… you need to fix it”. But this may, as happened to me the other day, be your saving grace by highlighting another process… in the case I was working on it happened to be Anti-Virus.
You can use this to give you the last X minutes of CPU usage, but to be honest the calculation is fiddly when dealing with different fields and values across different SQL versions, and therefore I tend to use this as either a point in time view, or something that I will log every minute into a table over time and therefore be able to produce a nice history as required.
The code is as simple as follows:
if object_id('cpuUtilisation') is null
create table cpuUtilisation
OtherProcessUtilisation as (100 - SystemIdle - SQLProcessUtilisation),
constraint pk_cpuUtilisation primary key clustered(runDate)
insert into cpuUtilisation(runDate, SQLProcessUtilisation, SystemIdle)
select convert(smalldatetime, current_timestamp) eventTime,
select top 1
record.value('(./Record/@id)' style="color:gray">, 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int') as SQLProcessUtilization,
select timestamp, convert(xml, record) as record
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%'
) as x
order by record_id desc
) as y
Here’s a brief sample from my system of some output taken from the resulting table: