I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes".
Some known information:
- sqlserver.exe is confirmed as the process consuming the CPU (2008 R2 Standard edition).
- It's very intermittent in nature. Can't pin it down to a particular query. Profiler traces don't seem to show any correlation. In fact, it looks as though all queries are being made to wait during the spike, but interestingly there seems to be a correlation with there being a service broker queue with a backlog of messages currently being processed.
- Service broker is being used purely for asynchronous intra-database stored procedure execution, with heavy use of CLR functions.
I'm suspecting some sort of background thread or process that is "catching up", but haven't been able to pin it down yet.
Best Answer
The first thing I would suggest you check is your "max degree of parallelism" via sp_configure. By default this is set to 0, so a parallel query will consume all CPU on the box (up to 64 threads) until it finishes if this setting is unchanged. I've seen this occur on more systems than I care to admit over my career, and it tends to be even more frustrating on the larger servers (with a higher number of CPUs). If this is set to the default of 0, the recommendation from MS on what this should be can be found here: http://support.microsoft.com/kb/2806535
If it's a smaller server with very few CPUs (e.g. 1 or 2) it could be something as trivial as auto-update statistics kicking in, though that has been reportedly rare... but rare still happens. You'd have to check if that is the culprit via a profiler trace to be certain though.