CPU History for last 24 hours.
-
Hello,
Is there anyway we can get the history of CPU utilization for last 24 hours. I had some HIGH cpu utilization last night and no one acknowledged and just want to figure it out what made cpu high. I got this query in google but it is just showing only 240 records and that too it doesn't have any reason why it's been utilized or what made it to utilize. Can some one provide me a script which provides 24 hours of information along wih sql text or query in that which happened this issue.
--script I used:
declare @ts_now bigint
select @ts_now = ms_ticks from
sys.dm_os_sys_info
select record_id,dateadd (ms, (y.[timestamp] -@ts_now), GETDATE()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int') as SQLProcessUtilization,
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>%') as x
) as y
order by record_id desc
Thanks,
-
I just tried other way and resolved to get yestarday cpu utilization. But, can we also get sql scripts what went on yestarday which utilized high cpu?
--just added this step:
CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101)
declare @ts_now bigint
select @ts_now = ms_ticks from
sys.dm_os_sys_info
select record_id,dateadd (ms, (y.[timestamp] -@ts_now), CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101)) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int') as SQLProcessUtilization,
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>%') as x
) as y
order by record_id desc
-
Your addition to the script is returning the exact same data - ie. the last 240 minutes, it is just that you have modified it to display the wrong date (yesterday)!!
If you have no monitoring in place to record this information (third party tools or MDW), you will struggle to get the information you need.
You may be lucky - the query that caused the high CPU may have been a one-off, and querying sys.dm_exec_query_stats on last_execution_time may show that something used a lot of CPU during the time period you are interested in.
-
Yeah Richard.. you are right. Also we do not have tools to monitor. will check with query stats
-
Yes, exec query stats worked. I was able to calculate the CPU usage. Thanks.
-
can you please post the script that worked for you, for the benefit of community
-
Check out https://sqldashboards.codeplex.com/
There is a section on queries ordered by cpu. You can change the code for the overall cpu chart to gather the last 24 hours too.
-
declare @ts_now bigint
select @ts_now = ms_ticks from sys.dm_os_sys_info
select top 35 record_id,
dateadd(ms, -15 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
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>%') as x
) as y
order by record_id desc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply