When you create a stored procedure using the CREATE PROCEDURE statement, you can use the WITH RECOMPILE option to generate a new query plan every time the procedure executes. This option can be used in place of the FOR REPLICATION option, and the two cannot be used at the same time. To force a stored procedure to recompile, use the system stored procedure sp_recompile on a table or view that your stored procedure references.
You might wonder why WITH RECOMPILE might be worth using, and why it doesn't slow down performance in many cases. It is true that you loose the benefit of a compiled query plan, but a stored procedure almost always runs faster than a straight SQL statement. When a stored procedure runs it has be advantage of using previously parsed text, as well as a previously constructed query tree. WITH RECOMPILE trades off the small amount of time that it might take to build a new query plan, against the possibility that you will use a bad query plan that will cost you many more times the build time.
Use WITH RECOMPILE when your passed parameters to a stored query have a different distribution of data. In situations like these your optimized query plans would often turn out to be different as well. Also, use the WITH RECOMPILE option when you find that the parameters passed to the stored query result in very different execution times. Chances are that a stored query plan yielding very different
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in March 2005