Post reply

Alter SP, does it recompile ? Performance

  • November 5, 2009 at 9:33 am

    #213539

    We have a stored procedure that's been timing out. As part of troubleshooting, I added steps throughout that writes records to a log table to see how long each step took. This morning it ran quickly without a problem. Would ALTER PROCEDURE act as a recompile and possibly improve performance ? We may have just been lucky and it will time-out tomorrow.

  • November 5, 2009 at 10:05 am

    #1075474

    Hi,

    Yes, ALTER causes recompilation of the procedure. This could have caused different execution plan to be generated, which works good now for the data the procedure works on.

    Regards

    Piotr

    ...and your only reply is slàinte mhath

  • November 5, 2009 at 10:11 am

    #1075479

    If you justwant to recompile a single procedure, use sp_recompile.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply