SQL Server CPU Utilization History Report

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

When a CPU alarm fires on a SQL Server host, the first question is whether the database engine caused it or something else on the machine did. The scheduler monitor ring buffer keeps a short history of exactly that split, and a single query turns it into one row per sample: SQL Server CPU, everything-else CPU and total load. The data comes from an area Microsoft does not support for general use, so the limits matter as much as the query.

Purpose and Overview

sys.dm_os_ring_buffers returns one row per record in a ring buffer of a given type. A ring buffer is a memory structure inside the Database Engine that is limited to a fixed number of records, and when new records arrive the older ones are removed. The records carry diagnostic data for the engine. Most ring buffer types are used internally and are not supported unless the product documentation describes them, and the record column is informational only, supported only where Microsoft documents it or directs its use for troubleshooting, with no guarantee of future compatibility.

The CPU report reads the RING_BUFFER_SCHEDULER_MONITOR type. No reference page describes that type, and none describes the XML element names the script parses. The query reads an unsupported interface, so treat the output as a triage aid: good enough to see that CPU was high at about 14:10 and that SQL Server was or was not the cause, not something to build a service-level report on. Nothing guarantees the record layout in a later version.

Three numbers come out of each sample:

  • sql_cpu_utilization: percentage of CPU taken by the SQL Server process (the ProcessUtilization element).
  • other_cpu_utilization: what is left after SQL Server and the idle share are removed, which covers antivirus scans, backup agents, a second instance and anything else on the host.
  • total_cpu_utilization: 100 minus the idle percentage (the SystemIdle element).

The element names, and the meaning given to them here, are not defined in the product reference, so verify the labels against Performance Monitor on a test instance before trusting them on a production incident.

Permissions follow the view. On SQL Server 2019 and earlier the caller needs VIEW SERVER STATE. On SQL Server 2022 and later the requirement is VIEW SERVER PERFORMANCE STATE. That is a real difference when a monitoring login was created under the older model and the instance has since been upgraded.

Code Breakdown

The script converts the ring buffer timestamp to a readable time, parses the XML in each record and derives the three CPU figures. It reads server-scope views, so it can run from any database.

 1DECLARE @ts_now BIGINT;
 2
 3SELECT @ts_now = ms_ticks
 4FROM   sys.dm_os_sys_info;
 5
 6SELECT
 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 N'%<SystemHealth>%'
41) AS ring
42ORDER BY
43    [timestamp] DESC;

Converting the timestamp

The timestamp column holds the time a record was added, in milliseconds since the computer started. It is not a clock time. ms_ticks in sys.dm_os_sys_info is also a count of milliseconds since the computer started, so both values sit on the same scale and @ts_now - [timestamp] is how many milliseconds ago the record was written. DATEADD(ms, -1 * offset, GETDATE()) subtracts that gap from the current local time.

Deriving "now" from cpu_ticks / (cpu_ticks / ms_ticks) is a common variant, but it only approximates ms_ticks through integer division, and cpu_ticks is a raw processor counter (read from the RDTSC counter), so the script reads ms_ticks directly. The result is still an estimate, because GETDATE() is evaluated at query time.

SQL Server 2025 adds a create_time column to sys.dm_os_ring_buffers that holds the record time in the local time of the instance. On that version the conversion is unnecessary, but the query above also runs on older releases, where the column does not exist.

Parsing the record column

record is nvarchar(max) and nullable. The inner query converts it to the xml type once, so the outer query can call .value() with an XPath expression for each field. The LIKE N'%<SystemHealth>%' predicate keeps only rows whose text contains a SystemHealth element and drops rows with a null record, so the XPath is only evaluated against records that have the elements it asks for.

Each .value() call takes the first match ([1]) and casts it to int. The derived columns are plain arithmetic on the two parsed values: total load is 100 minus idle, and the other-process share is total load minus the SQL Server share.

Ordering and volume

ORDER BY [timestamp] DESC returns the newest sample first. The script has no TOP, because the buffer already bounds the row count. The buffer size and the sampling interval for this record type are not documented, so the number of rows and the gap between them are things to measure on your own instance: run the query twice a few minutes apart and compare event_time values. Do not hard-code an assumption such as one row per minute into an alert.

Key Benefits and Use Cases

  • Post-incident triage. After a slow period, the report answers whether SQL Server or another process was using the CPU at that time, using only data already in memory.
  • Separating engine load from host load. A high other_cpu_utilization points at antivirus scanning, backup agents, ETL tools or a neighboring instance rather than at query workload.
  • No collection job required. The data already exists, so the query works on an instance where nobody set up monitoring before the problem began.
  • Evidence for the next step. A confirmed SQL Server-driven spike justifies pulling plan-level CPU from sys.dm_exec_query_stats; a confirmed other-process spike sends the investigation to the host.
  • Trend capture. Inserted into a table on a schedule, the output builds a history much longer than the buffer holds.

Performance Considerations

  • Not a supported interface. The record format, the element names and the retention are not documented for this ring buffer type. A new version can change the XML and break the query.
  • Fixed-size, memory-resident history. The buffer is a memory structure with a fixed record limit, so the oldest samples are discarded as new ones arrive. No reference page says the contents survive a restart, so do not rely on the report for an incident that ended with a service restart or a failover.
  • Coarse resolution. The sampling interval is undocumented, so a spike lasting a few seconds may not appear. The Processor: % Processor Time counter in Performance Monitor is another way to watch CPU usage.
  • Approximate times. event_time is derived from a tick count and the current clock. Treat times as accurate to about a sample interval, not to the second.
  • Instance CPU, not query CPU. The report shows one number for the whole SQL Server process. It cannot say which session or query was responsible.
  • Permission drift. The permission needed changes at SQL Server 2022, so a job that worked on 2019 may fail with a permission error after an upgrade until the login holds the new permission.

Practical Tips

  • Establish the sampling gap first. Run the query, wait a few minutes, run it again and subtract the oldest event_time from the newest. That measured window tells you how far back this instance can look before an incident and whether a scheduled copy is needed.
  • Persist the output. Insert the result into a history table from a SQL Agent job at a cadence shorter than the window you measured, and add a NOT EXISTS check on event_time so overlapping runs do not duplicate rows.
  • Compare with the Windows counter. A consistent 80 to 90 percent on the Processor: % Processor Time counter might indicate the need for more or faster processors. Use the ring buffer figure to locate the period and the counter to confirm it.
  • Follow a SQL Server spike with plan statistics. In sys.dm_exec_query_stats, total_worker_time is the CPU consumed by a cached plan since it was compiled, in microseconds but accurate only to milliseconds. Sort by it to find the heavy statements.
  • Follow an other-process spike to the host. If other_cpu_utilization is high while sql_cpu_utilization is flat, look at what ran on the machine at that time: scheduled scans, backup agent windows, patching or a second service.
  • Alert on the persisted table, not the buffer. A threshold query against the history table is stable even if the ring buffer layout changes, and only the collection step needs repair after a version change.

Conclusion

The scheduler monitor ring buffer gives a quick, tool-free split between SQL Server CPU and everything else on the host. Because the interface is unsupported and the history is short and memory-resident, use it to locate an incident and decide where to look next, and keep a persisted copy if the answer needs to survive the next restart.

References

Posts in this series