CPU Usage History helps me establish when a reported problem began. It gives a timeline rather than identifying the responsible query.
The query reads scheduler-monitor XML from an in-memory ring buffer. I limit it to one hour for the older DATEADD integer range. The timestamp is approximate, based on the current server clock.
DECLARE @ticks_ms bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info);
SELECT TOP (60) id,
DATEADD(millisecond, CONVERT(int, [timestamp] - @ticks_ms), GETDATE()) AS EventTime,
ProcessUtilization AS SQL_CPU_percent,
SystemIdle AS Idle_CPU_percent,
100 - SystemIdle - ProcessUtilization AS Other_CPU_percent
FROM
(
SELECT record.value('(./Record/@id)[1]', 'int') AS id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS ProcessUtilization,
[timestamp]
FROM
(
SELECT [timestamp], CONVERT(xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
AND [timestamp] >= @ticks_ms - 3600000
) AS buffer
) AS health
ORDER BY [timestamp] DESC;SQL_CPU_percent is the SQL Server process utilization reported in that sample. Other_CPU_percent is the remainder after SQL Server and idle time. These are historical samples, not a continuously recorded Windows performance counter or a permanent monitoring archive. Ring buffers overwrite older records and are lost across restarts.
I generated the historical picture with a large cross join in three connections. I wouldn’t repeat that load on a customer server. Instead, I correlate the timeline with workload and waits. The internal XML format and required permissions depend on the SQL Server version.
Reference: Ring-buffer scope and permissions.
A CPU sample is not a query diagnosis, it is a time-based clue to investigate.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
13 Comments. Leave new
I use the DMV to identify the query which kept the highest CPU more than 3 minutes within SQL Server. The Performance Analysis of logs (PAL) does also help out to find out the resources issue.
This DMV would sometimes give inaccurate result where CPU utilization is more than 100 ;-)
I am still looking for an alternative for this as this has been working for long time until recently.
Please let us know if you have one in mind. Thanks.
The query does not return correct result where the value can be negative … I hope SP1 would fix sys.dm_os_ring_buffers
I truly hope so.
select highest_cpu_queries.plan_handle,highest_cpu_queries.
plan_generation_num,highest_cpu_queries.max_worker_time,
highest_cpu_queries.total_physical_reads,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.total_elapsed_time,q.[text],q.dbid,q.objectid,q.number,q.encrypted,query_plan
from (select top 50 qs.plan_handle,
qs.plan_generation_num,qs.creation_time, qs.execution_count, qs.total_worker_time,
qs.max_worker_time, qs.total_elapsed_time,
qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads,
qs.total_physical_reads, qs.max_physical_reads from sys.dm_exec_query_stats
qs order by qs.total_worker_time DESC)
as highest_cpu_queries
cross apply sys.dm_exec_sql_text (plan_handle) as q
cross apply sys.dm_exec_query_plan (plan_handle) as qp
order by highest_cpu_queries.total_worker_time DESC
Is it possible to get current SQL server CPU usage through a query ?
you can always query perfmon to get current data.
hello sir,
good evening, I am Lokesh (working as SQL-DBA L3). Sir, I want to know that what is meaning of 100 to count the syscpu i.e. in above SQL
“100 – SystemIdle – ProcessUtilization AS ‘Others (100-SQL-Idle)'” .
why we are using 100.
in my system I have 56 CPU i.e. 112 cores and applied this script but getting syscpu < sqlcpu, which is wrong. please help me out.
Regards
Lokesh Kumar
It is % of usage so it can only go max upto 100, hope this helps.
I also have a same issue, my server has 192 cores but when I run this query it’s appear wrong figures.
It is % of usage so it can only go max upto 100.
sys.dm_os_ring_buffers only stores the last 256 minutes of data. Is there a different location where several days of cpu performance data is stored?
Hello sir
Good day
As checked above quary show latest information. But I need to get information before date (like before 4 days before information need ) any query for that information
Please post that quary