SQL Server Performance Forum – Threads Archive

Stored Procedure slows down after some time

Windows 2003 SP1, SQL Server 2000 SP4 Hello, there is a Stored Procedure that is executed some thousand times by a data export application. It joins several tables (using Primary Keys or Indexes, I compared that to the Query Analyzer Execution Plan). If the SP is freshly created (DROP, CREATE), it takes 50-100 ms to execute, but after some time (some hours) it slows down to take more than 1500 ms. If it is re-created then, it is running normally fast again. BTW, we changed the server from Windows 2000 to 2003. Also the database structure slightly changed, but the SP’s code almost remains unchanged. Occasionally (two times in two years) this problem occured on the Windows 2000 server, too, but now it occurs daily (or hourly). Do you have any ideas on how to debug or solve this. Thanks
robbert

you need to use sp_recompile or with complie option.
Read about this in BOL

Thanks. As a (temporary) workaround, I’ve added declare @minute int
set @minute = datepart(n, getdate())
if @minute % 10 = 0
exec sp_recompile ‘dbo.MyProcedure’
to that procedure, and will be checking whether this helps. Nonetheless, recompilation should not be necessary as the data in the indexes don’t change frequently. That’s what I was wondering about…

I had similar problem on sql server 7.0 and solved by job that recompiled the proc each hour. I was not able to figure out what caused the problem. It looked like really ordinary procedure increased a bit memory usage each time executed daily breaking down sql server.

Do you think there could be some type of memory loss each time a cache hit is made or something? Or is it only certain types of stored procedures that you’ve seen this on in the past? I have some that are running many times per minute all day long during operational hours, but they involve parameters so I see a wide variation in performance and my assumption has been (I recently came into this particular environment) that the differences were because of some of the values may return more rows than others, require more date page lookups etc. But I’m wondering if this is a general problem and I should just put a job in place to recompile several of the most hit procs on a regular basis.

http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp for a good start. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

No, it was just one specific procedure which wasn’t doing anything special. I think there is no need for recompiling procedure unless you have serious performance problems.

check the execution plan before and after the slowdown. Perhaps it is changing dramatically If you are having to recompile your stored proc in order to boost performance, maybe it means there is no execution plan which can perform well against all possible combination of parameters to the proc.
Maybe you can split the proc into smaller segments depending on the params, each of which can cache its own little execution plan

]]>