SQL Server Performance

Slow running Query

Discussion in 'T-SQL Performance Tuning for Developers' started by avmreddy17, Dec 9, 2002.

  1. avmreddy17 New Member

    Hi

    I Have a Stored Procedure which joins 2 Tables( which are not very large , but it still has appropriate indexes )
    and it used to run in no time ..Suddenly our web pages started giving time out errors.

    After checkiing the Execution Plan .. its doing an Index Seek and even I checked the Fragmentation on the table using show contig
    which showed almost 100%. After checking all these .. I dropped and recreated the SP and its started working fine ..giving the result set back
    in no Time .

    What could be the reason for these time out errors and why it is working fine after I dropped and recreated the SP with out making any changes.

    Thanks A Million


    Venu
  2. stanle New Member

    It can be the compiled plan. If the sp is reading data from frequently updated/inserted tables, then you have to recompile it from time to time. A good way to do it is to use sp_recompile with input parameter one of the join tables - this will recompile any stored source that accesses the table.
    Another possibility is that the actual sp was attempting to recompile at each query - this is possible if you have recursion or looped calls between stored procedures/triggers.
  3. royv New Member

    http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22228


    "How do you expect to beat me when I am forever?"
  4. bradmcgehee New Member

    The advice you have received so far is good. In addition, consider using Profiler to capture details of the stored procedure in question, tracking the execution plan, recompiles, and any other data you want to track, so that you can see what is going on in more detail. The more data you have, the easier it will for you to identify the exact problem.

    If it is not a recompile issue, also consider the prospect of out of date statistics. Although statistics should update automatically (if the default database statistics settings are used), they don't always do so often enough, which sometimes can through off the query optimizer. Only consider this after if you eliminate recompile as an issue.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page