Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.