SQL Server Performance

stored procedure running time

Discussion in 'Performance Tuning for DBAs' started by japan2002, Feb 28, 2003.

  1. japan2002 New Member

    Hi everybody,
    We've got a problem with sql-server-performace running stored procedures. In optimal case this procesure takes about 3 minutes to run. When we repeat running this procedure several times again, we suddenly get the response about 10 times longer, which means 30 minutes.
    After restarting the SQL server, the running time gets back to "normal" 3 minutes.
    And so our temporary solution is to restart the SQL server before every run of that procesure, because we never know in advance, how much time it will take.
    This stored procedure uses 3 levels of nested transactions.

    We would really appreciate every helpful information.
    Thanks.
    Jaroslav Pan
    jaroslav.pan@hpl.cz
  2. HarryArchibald New Member

    There are other factors to be considered in the running time of a stored procedure. The overall load on a server can have an effect on the run time and the volume of data may effect run time too.
    In one case, a high volume of data caused a sort warning to be raised, indicating data had to be written to disk, and the stored procedure run time increased by 20-30 fold.
    If you require the optimum run time of a stored procedure, run it on a server with no other load. Any increase in run time could then be attributed to other factors.
  3. bradmcgehee New Member

    Here's some questions. First, are you running the exact same SP with the same data parameters, or do the data parameters change from run to run? If the data changes, it is possible that the SP needs to be recompiled between runs because the execution plan for the SP may be changing. Trying running the SP using the WITH COMPILE option to see what happens. Also check the execution plan of each run to see what is happening. Also, have you run a Profiler Trace of each run of the SP to see what exactly is going on? This may be helpful. Without more details, it is hard to offer many more suggestions.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. satya Moderator

    IN addition to Brad's suggestion by default running PROFILER gives you much idea about slow running queries and recommendations on index by submitting the trace to INDEX TUNING WIZARD.

    HTH

    Satya SKJ
  5. gaurav_bindlish New Member

    In addition to Brad's sugestion, Also you can check if there is some batch job running periodically which is hurting SQL Performance.

    Gaurav
  6. Chappy New Member

    Also look at your lock waits and avg lock wait time in performance monitor, during optimal and suboptimal execution. If the sp's are waiting on locks, it might explain why its optimal after restarting, since fewer locks would generally be held by 3rd parties after a restart.

Share This Page