Agent - Idle CPU condition - question

• Davis H

  Right there with Babe

  Points: 727

  More actions

  May 9, 2014 at 4:10 pm

  #396729

  Hello all, sorry if this has been asked/answered, but I was unable to find it by searching.

  SQL2012 Enterprise SP1

  I am wanting to fire off maintenance when the CPU goes idle for my replicated (transactional) server when it is "idle". When there is no load of significance, I see from my monitoring tools the CPU utilization drops down to under 10%. I set the Agent Properties for Idle Condition to: Average CPU below 20% for 300 seconds.

  However, the maintenance job is never firing (have the schedule set for the idle condition). So, I disabled that job and created a dummy job to just send me an email. Set the schedule to the idle condition. Nothing ... Changed the idle condition to: Average CPU below 90% for 20 seconds ... nothing.

  So, then I think perhaps my monitoring software might be in left field so I ran:

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

  DECLARE @IntervalMin tinyint = 5

  DECLARE @AvgCPU tinyint

  DECLARE @AvgCPULimit tinyint = 50

  SELECT AVG(SQLProcessUtilization) --as AvgCPU

  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 N'%<SystemHealth>%') AS x ) AS y

  WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(MINUTE, -@IntervalMin, GETDATE())

  PRINT @AvgCPU

  It tells me the utilization for the last 5 minutes is less than 10.

  I try the exact same scenario over on my sandbox and the emails send just fine, as expected, but on this other machine, it doesn't.

  What am I missing?

  TIA