SQL Server Performance

%Processor all or nothing

Discussion in 'Performance Tuning for DBAs' started by dtipton, Apr 17, 2003.

  1. dtipton New Member

    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.

  2. sqljunkie New Member

    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?
  3. dtipton New Member

    Single CPU

    max degree of parallelism 0

  4. sqljunkie New Member

    Did you find any queries in profiler that had high durations or high CPU usage?
  5. gaurav_bindlish New Member

    In addition to the comments above, Also Check if some job is running on the server periodically.

    Gaurav
  6. dtipton New Member

    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.
  7. bradmcgehee New Member

    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

Share This Page