When should I include the WITH RECOMPILE option when creating a stored procedure?

Whenever a stored procedure is run in SQL Server for the first time, it is optimized and a query plan is compiled and cached in SQL Server's memory. Each time the same stored procedure is run after it is cached, it will use the same query plan, eliminating the need for the same stored procedure from being optimized and compiled every time it is run. So if you need to run the same stored procedure 1,000 times a day, a lot of time and hardware resources can be saved and SQL Server doesn't have to work as hard.

If the query inside the stored procedure that runs each time has identical parameters in the WHERE clause, then reusing the same query plan for the stored procedure makes sense. But what if the same stored procedure is run, but the values of the parameters change? What happens depends on how typical the parameters are. If the values of the parameters of the stored procedure are similar from execution to execution, then the cached query plan will work fine and the query will perform optimally. But if the parameters are not typical, it is possible that the cached query plan that is being reused might not be optimal, resulting in the query running more slowly because it is using a query plan that is not really designed for the parameters used.

Most of the time, you probably don't need to worry about the above problem. But, in some circumstances, it can cause a problem, assuming the parameters vary substantially from execution to execution of the query.

If you identify a stored procedure that usually runs fine, but sometimes runs slowly, it is very possible that you are seeing the problem described above. In this case, what you can do is to ALTER the stored procedure and add the WITH RECOMPILE option to it.

With this option added, a stored procedure will always recompile itself and create a new query plan each time it is run. This of course removes the benefits of query plan reuse, but it does ensure that each time the query is run, that the correct query plan is used. If the stored procedure has more than one query in it, as most do, it will recompile all of the queries in the stored procedure, even those that are not affected due to atypical parameters.

In SQL Server 2005, a new option if available: the RECOMPILE query hint. The difference between RECOMPILE and WITH RECOMPILE is that RECOMPILE can be added to the stored procedure as a query hint, and only that query within the stored procedure, not all of the queries in the stored procedure, will be recompiled. This can be beneficial if your stored procedure has many queries, and only one or two need recompiling, but not the rest.