Introduction

This article attempts to differentiate between the uses of WITH RECOMPILE and OPTION (RECOMPILE) features in Microsoft SQL Server 2005/2008. First, the features are explained and then they are differentiated. Lastly, some relevant cautionary notes for their usage are mentioned.

Recompile Options

Option1: WITH RECOMPILE [at a Stored Procedure level only]

Option2: Option (Recompile) [at a T-SQL statement level only]

Their functionality

When used in the T-SQL code of a particular stored procedure (SP), Option 1 compiles that SP every time it is executed by any user. It can not be used at an individual statement level but at a stored procedure level only. Any existing plan is never reused even if the new plan is exactly the same as any pre-existing plan for that SP. Once the SP is executed, the plan is discarded immediately. There is no caching of this particular execution plan for future reuse.

When used with a T-SQL statement whether inside an SP or adhoc, Option 2 above creates a new execution plan for that particular statement. Any pre- existing plan even if it is exactly the same as the new plan, is not used. The new plan is discarded immediately after execution of the statement.

General Plan Reuse in brief

SP

Assuming both of these options are not being used, an execution of an SP prompts a search for pre-existing plans (one serial plan and one parallel plan) in memory (plan cache). If found, it is reused. New input parameters in the current instance of execution replace the previous input parameters from a previous execution plan in the execution context handle which is part of the overall execution plan. So usage of different input parameters doesn't necessarily cause a recompilation. Recompilation happens when about 20% of the data in the tables being referenced within the SP is found to have changed since the last time statistics were updated for those tables and its indexes.

If you are accessing four tables in that SP and roughly 20% of the data for one of these tables has been found to have changed since the last statistics update, then that statement is recompiled (statement level recompile). If all four tables have changed by about 20% since last statistics update, then the entire SP is recompiled. This plan reuse system works as long as objects are qualified with it owner and DatabaseName (for e.g. DBName.dbo.TableName instead of just TableName).

If the database level setting "auto-update statistics" is off, we have less recompilation of SP's to begin with. Use of a temporary table within an SP also causes recompilation of that statement. There are other factors also that affect plan reuse but that theory is too detailed. I have just mentioned the most relevant points here with respect to this article. Please note that while searching for an existing execution plan, SQL server searches by the ID of the SP being currently submitted for execution. So the search for existing execution plans is faster in case of SP's.

Adhoc T-SQL

Adhoc T-SQL statements follow the same rules (statistics, etc) as an SP regarding plan reuse and some more. An adhoc T-SQL statement doesn't have an ID to begin with since it is not a database object. So existing plans are searched by the statement structure and the objects contained in that statement. So it takes a bit longer for the SQL Engine to search for an existing reusable plan in case of adhoc T-SQL statements. Any existing execution plan in order to be reused is matched with the previous similar statement in the plan cache referencing the same object(s) (like tables).

For example, the second statement below would reuse the plan created by the first statement. This is due to a database setting called "Simple Parameterization" which is ON by default (it treats the constant in the WHERE clause as a parameter and just switches the parameter value from 1 to 2 in the execution context of the existing plan created by the first statement). The third statement would also reuse the existing plan. The fourth statement below (it has the exact same functionality as the first statement) will not reuse the existing plan because the structure of the physical query is not exactly the same as the first query (extra spaces before the keyword WHERE). So the fourth statement would be compiled afresh and will have a new execution plan.

select a, b from db.user.abc where c = 1 
select a, b from db.user.abc where c = 2
select a, b from db.user.abc where c = @input_parameter
select a, b from db.user.abc                                  where c = 1

Again, if the statement is a bit complex unlike the statements above, even Simple Parameterization won't help and the statement would always generate a fresh execution plan unless the database option called "Forced Parameterization" is set. That again is a different ball game altogether. The rules for exact match of the complex adhoc T-SQL statement still hold good though.

Explanation

SP

It is for the reasons given in the theory above that RECOMPILE clause at an SP level can be avoided unless we have just dropped and recreated/changed indexes, updated statistics, etc or if it is a new SP in which case it would be automatically generate a fresh plan anyway. Another drawback is that this SP-level RECOMPILE clause would erase any previous execution plan and create a new plan on each execution which is unnecessary and expensive.

AdHoc T-SQL

Using the RECOMPILE option would discard any reusable plans. But most of the existing plans do not get reused in case of adHoc T-SQL due to reasons mentioned above. So plan cache ends up holding those un-reusable plans unnecessarily till the time comes (memory pressure) when they need to be really discarded to make space for new plans in the plan cache. Usage of the RECOMPILE option would help reduce the utilization of plan cache in such cases. But again the choice is between using the RECOMPILE option in adhoc T-SQL or, the basic rules to be followed (same structure, same columns, same tables, fully qualified names, etc) for plan reuse. Changing an SP is simple. Changing an adhoc T-SQL statement means making changes to the application as well that generates the adhoc T-SQL code and recompiling the affected application dll's in most cases. So SP is always the best choice not only for this reason but for scores of other reasons as well which we can discuss in a separate thread.

Note: I would like to stress the point that the Data Access Layer (DAL) would never pose a limitation on the ability to use SP's. If you can call even just one SP through DAL, you can call any number and any type of SP's from the same DAL whether the SP's use static or dynamic T-SQL code inside them. DAL can not be made a scapegoat under any circumstance for excess usage of adhoc T-SQL code in the application.

A slight clarification

Once these two options are used (SP level and adhoc statement level), there would be no scope for reusing any "good" plans. This is because plans won't exist after an execution is over. They would be discarded immediately after the execution.

Caution

Developers (mostly in the beginning stages of their career) often tend to use OPTION (RECOMPILE) an an excuse for introducing more and more adhoc T-SQL into the application and then tend to eliminate SP's altogether. And then they reach a stage where plan cache gets filled up to the brim again and again. This defeats the very purpose of Option (recompile) for adhoc T-SQL code. Please note that every recompile issues locks on the underlying tables. And every new plan generation process takes an additional time and overhead (CPU time and statistics analysis). Adhoc T-SQL reflects/implies inappropriate application design. It also increases network roundtrips and length/duration of a business logic implementation and execution. OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc T-SQL code to Stored Procedures is underway.

Suggestion/Conclusion

Based on the explanation above, using WITH RECOMPILE clause for SP's is not suggested. Using OPTION (RECOMPILE) at a statement level can be tried for adhoc T-SQL. It would be of course more productive to convert from adhoc T-SQL to Stored Procedures rather than the effort in adding OPTION(RECOMPILE) for each adhoc T-SQL statement in the application. This is because SP's are generally much superior to adhoc T-SQL statements in all respects.