Refreshing Stored Procedure Cached Plan

A nice and easy one but worth sharing.

We had an issue on our dev environment where the stored procedure managed to get a really yucky query plan, to resolve this you can DROP and CREATE the procedure or you can use sp_recompile.

To use , you call sp_recompile with the object name you want to refresh and basically the next time it is used the query plan will be recreated.

sp_recompile 'NameofProc'

You can also specify a table object and what this will do is mark all the procedures that reference that table to be recompiled on their next use.

This is a really simple solution, better than the alternatives (DBCC FREEPROCCACHE etc.. ).

Of course, why did it get a bad plan? That’s a different thing altogether and something that needs to be addressed.