Users are complaining that their application gets frequent time outs. I tracked the database using perfmon for approx 2 hours (60 second increments) during peak activity and noticed that the %processor time showed steep peaks and valleys instead of consistent usage (of 118 samples 91 where either above 90% or below 10% for an overall average of 33%) and the processor queue length averaged over 5 (single CPU machine). My initial thought is a single (or few) nasty queries/stored procedures are periodically hogging the CPU, creating the timeouts, and driving up the queue length. I'm collecting transaction info. using Profiler now, but wanted to get some other opinions.
I think your initial thought is probably right on. How many CPUs does your system have? What is the sp_configure "max degree of parallelism" value set to?
In addition to the comments above, Also Check if some job is running on the server periodically. Gaurav
I've discovered a really nasty stored procedure that recompiles each time it runs (contains a mixture of DML and DDL) and has very high CPU and Duration numbers.
You are on the right track. Use profiler to identify all that nasty SPs or queries, one at a time, and attack them as you have time. You may also want to check your indexing with the Index Wizard to see if there are any obvious indexes missing. ------------------ Brad M. McGehee Webmaster SQL-Server-Performance.Com
