SQL Server Performance

SQL Profiler- CPU vs Duration

Discussion in 'Performance Tuning for DBAs' started by benwilson, Jun 28, 2005.

  1. benwilson New Member

    Hi all,
    I am currently running a trace to try and identify long running queries...i am a bit confused about which time i should be looking at- CPU or Duration?!?!

    I am capturing RPC: Completed and SQL: BatchCompleted. Some of the queries/procedure calls coming though have very low CPU time (0 in some cases) but the duration is 15 seconds plus! I really dont understand what is going on! What should i be most concerned about?!?!

    Thanks,
    Ben

    'I reject your reality and substitute my own' - Adam Savage
  2. derrickleggett New Member

    Both. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] You should also be looking at reads and writes, so you can figure out where the load is occurring at (disk, memory, CPU). Tune the top ends of both. Also, periodically run "intensive scans" on everything in the system so you catch the small things that fall under your filters normally, but run millions of times a day.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  3. benwilson New Member

    Thanks Derrick,
    Looking at the trace data, i see there are a lot of reads for the long running queries...so i am guessing this is where the time is being spent...is it now a matter of tuning the query to reduce the number of reads?!?

    'I reject your reality and substitute my own' - Adam Savage
  4. satya Moderator

    Check the code whether it is using too many joins.
    While determining the tables with the most number of READS associated with them, you will also often discover additional problems in the execution plan, such as table scans, index scans, bookmark lookups, hash joins, or sort operations.

    As you tune a query with joins, you may want to pay attention to the Scan Count, watching to see if it is increasing or decreasing as you tune the query.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. derrickleggett New Member

    Agree with Satya. A lot of times the reads are going to be caused by not using indexes, or doing scans on them. Try to reduce your reads by being more selective, adding indexes where needed, etc. You also want to look at your disk queue length in Performance Monitor while this is running. If you are getting a queue size greater that 2 or 3 per disk, the heavy reads will have a much greater performance affect on the system than normal.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. benwilson New Member

    Thanks Derrick and Satya,
    so, i gather i need to look at the execution plan to try and see what is taking the most time, etc am going to have a read through http://www.sql-server-performance.com/query_execution_plan_analysis.asp and see if i can make any sense of the plan! (but chances are there will be more questions!)

    The code for the problem procedures is quite complex and have many joins...unfortunately, i cant see a way to cut the proc down- as far as i can tell it needs to be this complex :-(

    Ben

    'I reject your reality and substitute my own' - Adam Savage
  7. satya Moderator

    Any chance of indexes and submit the trace to Index TUning wizard for any recommendation.
    Frequent checkup of index fragmentation will also help to fine tune the performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page