Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Agent - Idle CPU condition - question Expand / Collapse
Author
Message
Posted Friday, May 9, 2014 4:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 8:53 AM
Points: 24, Visits: 282
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
Post #1569471
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse