CPU utilization trends

Track SQL Server CPU Usage for the Last 256 Minutes

Ever wonder how your SQL Server has been utilizing its CPU over time? This handy T-SQL script allows you to view the CPU utilization history for the last 256 minutes, broken down into one-minute intervals. It's a fantastic way to quickly pinpoint any performance bottlenecks or trends related to CPU usage.

The SQL Script

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);

How It Works

This script queries `sys.dm_os_ring_buffers`, specifically looking at the `RING_BUFFER_SCHEDULER_MONITOR` type. This ring buffer captures system health events, including CPU utilization. By parsing the XML output from the `record` column, we can extract details like `SystemIdle` (how much CPU is idle), `SQLProcessUtilization` (how much CPU SQL Server is using), and then calculate the CPU usage by other processes.

For more in-depth information about `sys.dm_os_ring_buffers` and how it's used, check out this great resource: Inside sys.dm_os_ring_buffers.

Give this script a try and let us know in the comments if you found it useful for your SQL Server monitoring!