CPU_Usage_History of Past 4 hours

CPU_Usage_History of Past 4 hours

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info

SELECT top 240 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

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform