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.