Will OPTION(OPTIMIZE FOR UNKNOWN) reuse the cache instead of recompiling it every time?
Yes it will.
There are two main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE) , as seen from this quote from MSDN :
OPTIMIZE FOR UNKNOWN
Instructs the query optimizer to use statistics instead of the initial values for all local variables when the query is compiled and optimized, including parameters created using forced parameterization.
RECOMPILE
Tasks the SQL Server Database Engine to abandon the plan generated for the query after it is executed, forcing the query optimizer to recompile the query plan the next time the same query is executed. Without specifying RECOMPILE , the Database Engine caches query plans and reuses them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.
So, two main differences:
- Caching (or absence) of the request plan.
Typically, the generated query plan is cached and reused. OPTIMIZE FOR UNKNOWN does not affect this feature of the engine. RECOMPILE suppresses this function and tells the engine to abandon the plan and not put it in the cache.
- Using (or not) the actual parameter values during plan generation.
Typically, the optimizer “sniffs” the parameter values and uses these values when creating the plan. OPTIMIZE FOR UNKNOWN suppresses this function and tells the engine to process all parameters as if their values were unknown. The optimizer has built-in rules and heuristics on how to use available statistics for various filtering criteria. For details, see Optimize for ... Mediocre? . Typically, parameter sniffing is used when the query / stored procedure is first run and uses the parameter values during the first run. The generated plan is cached and then reused.
One non-obvious thing to remember here is that in both cases (normal without prompts and with the prompt OPTIMIZE FOR UNKNOWN ), the generated plan must be valid and give the correct result for any parameter value. It is adapted to the fake values that were used during the first run in the normal case / without prompting; it is not adapted to any particular value in the case of OPTIMIZE FOR UNKNOWN , but it is still valid if the parameter is changed later in any way.
This is important and does not allow the optimizer to perform certain transformations and simplify the plan.
OPTION(RECOMPILE) allows the optimizer to embed the actual parameter values during each run, and the optimizer uses the actual parameter values to create a better plan. There is no need to worry that the generated plan may not work with any other parameter value, because the plan will not be cached and reused.
This effect is mainly displayed for dynamic search queries . For instance:
SELECT ... FROM T WHERE (@ParamSomeID = 0) OR ( @ParamSomeID = -1 AND T.SomeID NOT IN ( SELECT OtherTable.SomeID FROM OtherTable ) ) OR ( T.SomeID IN ( SELECT OtherTable.SomeID FROM OtherTable WHERE OtherTable.SomeID = @ParamSomeID ) ) OPTION(RECOMPILE)
If @ParamSomeID is 0 , the optimizer will process the request as if it had no WHERE . The plan does not mention OtherTable at all.
If @ParamSomeID is -1 , the plan will join T to OtherTable using Left Anti Semi Join and scan the entire OtherTable .
If @ParamSomeID is, say, 5, the plan will look for the index in the unique index on OtherTable and read only one row from OtherTable .
Without OPTION(RECOMPILE) this simplification and conversion will not happen.
Another reason for using OPTION(RECOMPILE) is that the data distribution is very distorted. For example, you have a table with 1M rows. One column has a value of 0 in 990K rows and values from 1 to 10 in 1K rows. Queries that are filtered in this column should have different plans depending on the actual value of the filter.
In both examples above, OPTIMIZE FOR UNKNOWN will create a mediocre plan.