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
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.