Post reply

Calling sp_recompile from the stored proc that needs recompile

  • November 17, 2005 at 8:39 am

    #111190

    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

    #604590

    From BOL

    sp_recompile

    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

    #605269

    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