November 17, 2005 at 8:39 am
I have this VERY infrequent issue where a stored proc starts working very slow. Recompiling makes it fast. But waiting until the users compain seems like a bad ideea. Creating the sp "WITH RECOMPILE" is not an option as it is called VERY often. I was thinking of measuring the time the main query takes to run and if it's above what it usually takes, call recompile. Kind of like this :
Declare @dStart Datetime
set @dStart=getdate()
** SP body
if datediff(ms,@dStart,getdate())>5
exec sp_recompile name_of_stored_procedure
Return
This works fine with just a wrinkle. executing the sp_recompile throws a message (Object xxx was successfully marked for recompilation.) and middle tier code we have gets confused by.
Question
a) any better way of doing this?
b) can that message be turned off?
November 18, 2005 at 4:27 am
From BOL
Causes stored procedures and triggers to be recompiled the next time they are run.
To avoid the mesage being returned to the client, you could create a T-SQL scheduled task that runs on a determined frequency based on the time that it takes for performance to degrade. This avoids the message being returned to the client application as it the SQL Agent that would actually execute the task.
The task would merely issue the exec sp_recompile name_of_stored_procedure, causing it to recompile when next it is run.
November 22, 2005 at 9:25 am
It would be better to discover why you have the problem and deal with it - this path is only a fix not a solution.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply