0

We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in modifying the below script to determine the resource which caused on 25th or pls suggest any alternatives

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

1 Answer 1

1

Your code requires little tweaking. Where you are doing getdate you just need to do getdate() -6. The changed code is below.

Please note that ring buffer capacity is limited so it would not store information for complete day it would have information about specific time.

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()-6) AS EventTime,--changed here 
    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
2
  • on 25-Feb due to which server reboot was performed - the DMV data will get flushed out. How would the OP get back dated data ?
    – Kin Shah
    Mar 2, 2016 at 15:44
  • He would be only able to get after 25th reboot. Before that its not possible. Moreover intention was to help him that a bit of change would get you backdate data, well ofcourse subject to various conditions
    – Shanky
    Mar 2, 2016 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.