Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes".
enter image description here 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.

share|improve this question
    
I would suggest using sql diag with sql nexus. It is a learning curve if you haven't used them before. There is lot of material on them out there. –  KNI Feb 12 at 23:04
1  
we're going to need a lot more information than this. please see if you can capture activity (specific queries) on the server during these CPU spikes. sqlnexus is a tool you can use, or you can search for "glenn berry diagnostic dmv queries" and snag some useful scripts there –  swasheck Apr 11 at 15:14

1 Answer 1

Since I don't have sufficient reputation, an answer will have to suffice.

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.

Hope that helps out.

John

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.