How to find SQL Server and system CPU usage history :
Posted by Karthick P.K on November 30, 2010
SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers using below query
Note: For troubleshooting high CPU usage in SQL Server follow https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/
SQL Server 2005
DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
SELECT
record.value('(Record/@id)[1]', 'int') AS record_id,
DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
FROM (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc
SQL Server 2008
DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
SELECT
record.value('(Record/@id)[1]', 'int') AS record_id,
DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
FROM (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc
MSSQLWIKI 
Jude said
We’re a bunch of volunteers and opening a new scheme in our community. Your site provided us with valuable info to work on. You’ve done
an impressive process and our entire group will be thankful to you.
icandy pushchair reviews said
That is very interesting, You are an overly skilled blogger.
I’ve joined your feed and stay up for searching for extra of your wonderful post. Also, I’ve shared your web site in my social networks
jubi rev scam said
You ought to be a part of a contest for one of the best sites on the internet.
I’m going to highly recommend this website!
basic accounting equation assets liabilities owners equity said
Good day! I just wish to give a huge thumbs up for the good information you have here on
this post. I will be coming back to your weblog for more soon.
kranthi said
Hi Karthi
What are the counters we need to look into from the script output and what are the thresholdvalues
srinivas d said
Hey Karthick,
what does the column record_id refer to ?
Would it be possible to get the utilization history per database ?
/srinivas
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] How to find SQL Server and system CPU usage history […]
Margherita said
Nice respond in return of this issue with real arguments and explaining everything regarding that.
Nancy said
is there a way you can get cpu utilization from ring buffer for past few weeks?
How To Find Cpu Utilization In Sql Server 2008 | Smart Phone Info. said
[…] How to find SQL Server and system CPU usage history … – SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers using below query. Note: For troubleshooting high CPU usage in SQL Server … […]
Cpu Usage How To Find | Information said
[…] How to find SQL Server and system CPU usage history … – SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers using below query Note: For troubleshooting high CPU usage in SQL Server follow … […]
Grammar Pedants are Fewer Interesting said
Slight typo…SchedluerMonitorEvent should be SchedulerMonitorEvent
SQL Server – CXPacket Waits – Compare Across SQL Server Instances | Learning in the Open said
[…] How to find SQL Server and system CPU usage history Link […]
done said
After looking at а number of the blоg posts on your bⅼog,
I seriously appreciate your way of writіng a blog. I bookmarked it to my bookmarк weЬsite
list аnd will be checking bacҝ in the near future. Please check out
my web site as ԝell ɑnd teⅼⅼ me h᧐w you feel.
หนังโป๊ said
Yⲟur mode of explaining alⅼ in this paragraph іs really nice, every оne can effortlessly understand it, Thanks
a lot.
How to get SQL Server CPU and Memory read and write usage within two weeks? said
[…] is another query,I took the query from This Blog, to get CPU utilization history. If you want for previous date you can use GETDATE() -1 instead of […]