SQL Server Performance

Store procedure

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Rabani, Jan 13, 2009.

  1. Rabani New Member

    Hi ,
    Can anybody let me know if a store procedure is taking a high cpu time and high duration to execute , how can we fix this problem.
    Thanks,
    Rabani
  2. Harnath New Member

    Hi Rabani,
    1. Try to select only those columns which are needed, so that some how the performance will be increased.
    2. Debug the stored procedure in order to find out the query which is consuming lot of CPU time and try to design that statement in some other way.
  3. FrankKalis Moderator

    Can you post the code? Most likely this is the first thing that need to be fixed.
  4. Harnath New Member

    Can you post your stored procedure, so that i will reply accordingly..
  5. gurucb New Member

    High CPU can be caused by Hash / Merge Joins, Compilation / Remcompilations which can be due to more records and may be related to lack of indexes as well. High Duration can be caused by Blocking, Doing more than necessary IO (again lack of indexes).
    But Duration and CPU always are not interalted say for example if there is a blocking issues on server Duration will increase but not necessarily CPU.
    So, first we need to look at
    * Whether there are appropriate indexes supporting query.
    * If there are indexes is the query taking advantage of the indexes.
    Along with query , it is would better if you can share the execution plan to see if these are some of the causes. Or may be run the query to DTA and see for index recommendations.
  6. satya Moderator

  7. Kewin New Member

    Other reasons - very simplified:

    -- high duration (i/o bound)
    select col1 from veryLargeTable

    -- high cpu (cpu bound)
    select col1, <very complex calcultion> from smallTable where id = 1
    -- both
    select col1, <very complex calcultion> from veryLargeTable
    In general, doing arithmetics in SQL makes it cpu bound, and if it's over large volumes of data and/or a large number of read/write ops, it may also become i/o bound.
    /Kenneth

Share This Page