Check SQL CPU Utilisation vs Other
Published: Nov 20, 2015
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
begin
   create table
cpuUtilisation
  
(
      
runDate smalldatetime,
      
SQLProcessUtilisation tinyint,
      
SystemIdle tinyint,
      
OtherProcessUtilisation as (100 - SystemIdle - SQLProcessUtilisation),
      
constraint pk_cpuUtilisation primary key clustered(runDate)
   )
end

insert into
cpuUtilisation(runDate, SQLProcessUtilisation, SystemIdle)
select convert(smalldatetime, current_timestamp) eventTime,
      
SQLProcessUtilization, SystemIdle
from
(
  
select top 1
           record.value
('(./Record/@id)[1]' style="color:gray">, 'int') as record_id,
          
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
          
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
          
timestamp
   from
  
(
      
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>%'
  
) 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:

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron