SQL Server – Get CPU Usage History for last 256 minutes

The other day, I was asked by our Application Team Lead to check why the CPU usage is very high in the Production DB server. It remained consistently high throughout the day. He asked whether it is coming from SQL Server or other processes.

 

 

Below is a useful script to review the CPU usage history for last 256 minutes (This will work on SQL Server 2008 and up)​​ 

 

 

DECLARE​​ @ts_now​​ bigint​​ =​​ (SELECT​​ cpu_ticks/(cpu_ticks/ms_ticks)​​ FROM​​ sys.dm_os_sys_info​​ WITH​​ (NOLOCK));​​ 

 

SELECT​​ TOP(256)​​ SQLProcessUtilization​​ AS​​ [SQL Server Process CPU Utilization],​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ SystemIdle​​ AS​​ [System Idle Process],​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 100​​ -​​ SystemIdle​​ -​​ SQLProcessUtilization​​ AS​​ [Other Process CPU Utilization],​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ DATEADD(ms,​​ -1​​ *​​ (@ts_now​​ -​​ [timestamp]),​​ GETDATE())​​ AS​​ [Event Time]​​ 

FROM​​ (​​ 

  ​​​​ SELECT​​ record.value('(./Record/@id)[1]',​​ '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​​ WITH​​ (NOLOCK)

WHERE​​ ring_buffer_type​​ =​​ N'RING_BUFFER_SCHEDULER_MONITOR'​​ 

AND​​ record​​ LIKE​​ N'%<SystemHealth>%')​​ AS​​ x​​ 

  ​​​​ )​​ AS​​ y​​ 

ORDER​​ BY​​ record_id​​ DESC​​ OPTION​​ (RECOMPILE);

 

 

Now, if the value in “Other Process CPU Utilization (%)” column is higher​​ than​​ “SQL Server Process CPU”, then you may​​ ask​​ your sys admins to investigate the cause. But in our case, it​​ was indicated​​ clearly that the cause of high CPU usage was coming from SQL Server Process.​​ Below is the output of the​​ script.

 

 

 

After seeing the results, I investigated from the SQL queries perspective and found out that there are few​​ expensive queries that are​​ causing high CPU usage. ​​ How did I find out? That would be the subject of another blog.​​ 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
Bookmark the permalink.

Leave a Reply

One Comment

  1. Awesome article!

Leave a Reply

Your email address will not be published. Required fields are marked *