SQL 100% processor/Colleval loop

[squmph]

Hi all,

 

We have a single primary site on 2012 R2 SP1 comprising of a central site server and dedicated SQL server, both 2008 R2. We have lots of remote sites which are all set up as secondaries.

 

A few days ago, processor usage on our SQL box went through the roof. It sits at 99% constantly. The only way to stop it seems to be to stop SQL, or stop smsexec on the site server. As soon as you start them again, there it goes.

 

I've been trying to dig into this, and have found that our collections seem to be performing incremental updates at an insane rate. Colleval viewer tells me we have 94 collections, and 70 of them have performed an incremental update in the last 3 minutes. They only took a few seconds each and aren't queuing, but I think this seems pretty likely to be my cause.

Unfortunately... I have no idea what to do about this, short of disabling delta updates for all collections to see what happens.

 

Any ideas please? Our estate is otherwise unaffected, apart from a few entries in various logs about rather understandable SQL timeouts, and general slowness.

 

Thanks

Rob

[squmph]

So I stopped the collection evaluator component... that was obvious

Didn't help one bit; still at 100%.

Worked my way down all the components stopping them. Now the only one running is the sms executive, and we're still on 100%.

I've had nothing new in either of the two standard log locations for several minutes - even ccmexec.log has nothing useful in it.

 

Have now started digging into SQL and I think that the plan which is doing this is running the following query:

select ScopeName, SearchBase, Group_ObjectGUID, IsNestedGroup from vAllImmediateSearchBase_Group

 

Help!