Track SQL Server CPU Usage History for the Last 256 Minutes

Summary: Use this T-SQL script to extract historical CPU utilization from SQL Server ring buffers, providing a minute-by-minute breakdown of SQL usage vs. system idle time.

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 without needing external monitoring tools.

The CPU History 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 CPU %],
    SystemIdle AS [System Idle %],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU %],
    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 targeting the RING_BUFFER_SCHEDULER_MONITOR. This internal ring buffer captures system health events, including CPU utilization, directly from the SQLOS scheduler.

By parsing the XML output, we extract:

• SQLProcessUtilization: Percentage of CPU used by the SQL Server process.
• SystemIdle: Percentage of CPU that is currently unused.
• Other Process Utilization: Calculated as the remaining CPU used by Windows or other background applications.

For more in-depth information about how SQL Server manages internal tracking, check out the community resource: Inside sys.dm_os_ring_buffers.

Did this script help you find a bottleneck? Let us know in the comments below or check out our other performance tuning guides!