Problem solve Get help with specific problems with your technologies, process and projects.

Using the WITH RECOMPILE option in SQL Server

What it the WITH RECOMPILE option is, when you should use it and how it relates to SQL Server stored procedures.

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 times needs to be modified for the individual parameters.

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.

Dig Deeper on SQL Server Stored Procedures