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
SELECT record.value('(./Record/@id)', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)','int') AS [SQLProcessUtilization], [timestamp]
SELECT [timestamp], CONVERT(xml, record) AS [record]
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())
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?