Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Unanswered: degraded stored procedure performance

    Hi,

    I have been experiencing an intermittent problem that I hope someone can help me with.

    Twice over the last month or so, we have experienced a problem where the performance of a single stored procedure degrades dramatically very quickly, possibly instantaneously from ~1sec to ~15-25 secs. What's throwing me is there are hundreds of other stored procedures, but none of them has been affected. Is there a possibility that our Maintenance plan which is scheduled to reorganize the data and index pages on Sunday night be the culprit?? Both times the problem reared it's ugly head on Monday that's why I'm wondering if it is causing some problem with the sp's execution plan or something, because the problem goes away if I recompile the stored procedure. If it is the problem, I have a couple more questions, what can I do to help prevent it from occurring in the future, and why does it only affect one sp?? If however you don't think it is the cause any other thoughts would be greatly appreciated.



    The stored procedure in question has not been modified for 2 months and generally performs fine.

    SQL Server 2000 Enterprise Edition 8.00.679

    Thanks in Advance

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What you have describe is not that uncommon. This would most likely happen if the size of the underlying table(s) grew or shrank significantly after the sp was compiled or if the data distibution drastically changed after the sp was compiled.

    One thing you might want to do is force a recompile of all sp and triggers after the maintinance plan runs check out 'sp_recompile' in BOL.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    to step though all tables you can use:

    Microsofts code:
    Code:
    sp_MSforeachtable 'sp_recompile ''?'''
    Or your own:
    Code:
    declare @Tbl sysname
    select @Tbl = min(name) from sysobjects where type = 'U'
    while (@Tbl is not null) begin
       exec('sp_recompile ' + @Tbl)
       select @Tbl = min(name) from sysobjects where type = 'U' and name > @Tbl
    end
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •