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 http://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
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