SQL Server CPU Utilization History Report

calendar Apr 17, 2026 / · 5 min read · sql server cpu utilization performance monitoring sys.dm_os_ring_buffers ring buffer dba scripts cpu history sqlserver process utilization system idle process server monitoring  ·
Share on: linkedin copy

SQL Server records CPU utilization data internally every minute and stores the last 256 readings in a ring buffer. This T-SQL script reads that buffer to produce a CPU history report showing SQL Server CPU usage, other process CPU usage, and total server CPU load — without requiring any external monitoring tool.

Purpose and Overview

When a CPU spike occurs on a SQL Server host, the first question is whether SQL Server caused it or whether another process did. The sys.dm_os_ring_buffers DMV stores CPU statistics that SQL Server collects from the Windows performance counters every 60 seconds, retaining the last 256 minutes of data.

This script parses the XML stored in the ring buffer and returns one row per minute with three CPU columns:

  • sql_cpu_utilization — percentage of total CPU consumed by the SQL Server process
  • other_cpu_utilization — percentage consumed by all other processes on the host
  • total_cpu_utilization — combined SQL Server plus other processes (100 minus the idle percentage)

Use this report after an incident to reconstruct what happened, or run it proactively to detect trending CPU pressure before it becomes a problem.

Code Breakdown

 1DECLARE @ts_now BIGINT;
 2
 3SELECT @ts_now = cpu_ticks / (cpu_ticks / ms_ticks)
 4FROM   sys.dm_os_sys_info;
 5
 6SELECT TOP 256
 7    DATEADD(
 8        ms,
 9        -1 * (@ts_now - [timestamp]),
10        GETDATE()
11    )                                       AS event_time,
12    xmldata.value(
13        '(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
14        'int'
15    )                                       AS sql_cpu_utilization,
16    xmldata.value(
17        '(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
18        'int'
19    )                                       AS system_idle,
20    100
21    - xmldata.value(
22        '(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
23        'int'
24    )
25    - xmldata.value(
26        '(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
27        'int'
28    )                                       AS other_cpu_utilization,
29    100
30    - xmldata.value(
31        '(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
32        'int'
33    )                                       AS total_cpu_utilization
34FROM (
35    SELECT
36        [timestamp],
37        CONVERT(XML, record) AS xmldata
38    FROM   sys.dm_os_ring_buffers
39    WHERE  ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
40    AND    record LIKE '%<SystemHealth>%'
41) AS ring
42ORDER BY
43    [timestamp] DESC;

Reading the Ring Buffer

sys.dm_os_ring_buffers stores diagnostic data that SQL Server collects for internal monitoring. The ring_buffer_type column identifies what kind of data each row contains. Filtering to RING_BUFFER_SCHEDULER_MONITOR selects the scheduler monitor records, which include CPU statistics. Each row's record column contains an XML document; the subquery converts that raw text to typed XML so the outer query can extract values using .value().

Calculating the Timestamp

The ring buffer stores a timestamp column in CPU tick units, not wall-clock time. The script converts ticks to milliseconds using the ratio from sys.dm_os_sys_info:

1@ts_now = cpu_ticks / (cpu_ticks / ms_ticks)

@ts_now represents "now" in milliseconds. For each ring buffer row, (@ts_now - [timestamp]) gives the number of milliseconds in the past that row was recorded. DATEADD(ms, -1 * offset, GETDATE()) converts that back to a readable datetime.

Extracting CPU Values from XML

Each XML record contains three values used by this script:

  • ProcessUtilization — CPU percentage used by the SQL Server process (sqlservr.exe)
  • SystemIdle — CPU percentage that was idle
  • other_cpu_utilization is derived: 100 - SystemIdle - ProcessUtilization
  • total_cpu_utilization is derived: 100 - SystemIdle

The .value() XQuery function extracts each field by XPath and casts it to int.

Filtering Valid Records

The WHERE record LIKE '%<SystemHealth>%' predicate ensures only scheduler monitor health records are included. Other record types share the same ring_buffer_type and would cause XML parsing errors if included.

Result Order

ORDER BY [timestamp] DESC returns the most recent minute first. The ring buffer never holds more than 256 rows of this type, so TOP 256 is the natural limit.

Key Benefits and Use Cases

  • Post-incident analysis — reconstruct a CPU spike from up to four hours ago without needing external monitoring data
  • Blame attribution — determine whether high CPU was caused by SQL Server or another process on the host
  • Trend detection — spot gradual CPU growth that may indicate a query plan regression or data growth problem
  • No agent required — reads live in-memory data; no SQL Agent job or third-party tool needed
  • Lightweight — reads only DMV memory structures with no disk I/O or table scans

Performance Considerations

  • Data is lost on restart — the ring buffer is in-memory only. A SQL Server restart or failover clears all history. If an incident caused a crash, this data will not be available.
  • 256-minute limit — the ring buffer holds exactly 256 one-minute samples. Events older than roughly four hours are overwritten.
  • 1-minute granularity — the report cannot show sub-minute CPU spikes. For sub-second precision, use Performance Monitor or Extended Events.
  • Tick arithmetic on busy systems — on systems with very high CPU tick rates, the tick-to-millisecond conversion is an approximation. Event times are accurate to within a few seconds.
  • SQL Server process only — this report shows the aggregate CPU for the entire SQL Server process. It does not break down CPU by individual query or session. Use sys.dm_exec_query_stats or Query Store for per-query CPU.

Practical Tips

  • Compare against sys.dm_exec_requests — if sql_cpu_utilization was high at a specific minute, cross-reference with job history or Query Store to identify which queries were running then.
  • Set a threshold alert — wrap this query in a SQL Agent job that sends an alert if sql_cpu_utilization exceeds 80 for three consecutive minutes.
  • Capture to a history table — insert results into a permanent table on a schedule to build longer-term CPU trend history beyond the 256-minute window.
  • Check other_cpu_utilization — if this value is consistently high, investigate non-SQL processes on the host such as antivirus scans, backup agents, or ETL tools.
  • Pair with wait statistics — high SQL Server CPU combined with high SOS_SCHEDULER_YIELD wait type is a strong indicator of CPU pressure from query execution.

Conclusion

The sys.dm_os_ring_buffers CPU history script gives you an immediate, tool-free view of CPU utilization over the past four hours. It clearly separates SQL Server CPU from other process CPU, making it straightforward to determine the source of a spike. Add it to your incident response toolkit alongside wait statistics and query performance queries for a complete picture of server health.

References

Posts in this series