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 AdvanceWhat 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.|||to step though all tables you can use:

Microsofts code:
sp_MSforeachtable 'sp_recompile ''?'''

Or your own:
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