Will OPTION(OPTIMIZE FOR UNKNOWN) reuse cache instead of recompiling
each time?
Yes, it will.
There are two main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE) as can be seen from this quote from MSDN:
OPTIMIZE FOR UNKNOWN
Instructs the query optimizer to use statistical
data instead of the initial values for all local variables when the
query is compiled and optimized, including parameters created with
forced parameterization.
RECOMPILE
Instructs the SQL Server Database Engine to discard the plan
generated for the query after it executes, forcing the query optimizer
to recompile a 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, the two main differences are:
- Caching (or not) of the query plan.
Usually the generated query plan is cached and reused. OPTIMIZE FOR UNKNOWN doesn't affect this feature of the engine. RECOMPILE suppresses this feature and tells the engine to discard the plan and not put it into the cache.
- Using (or not) actual parameter values during plan generation.
Usually optimizer "sniffs" the parameter values and uses these values when generating the plan. OPTIMIZE FOR UNKNOWN suppresses this feature and tells the engine to treat all parameters as if their values were unknown. Optimizer has built-in rules and heuristics how to use available statistics for various filtering criteria. See Optimize for… Mediocre? for more details. Normally parameter sniffing is used on the first run of the query/stored procedure and uses the values of parameters during the first run. The generated plan is cached and later can be reused.
One non-obvious thing to remember here is that in both cases (normal without any query hints and with OPTIMIZE FOR UNKNOWN hint) the generated plan has to be valid and produce correct result for any possible parameter value. It is tailored to the sniffed values that were used during the first run in the normal/no-hint case; it is not tailored to any specific value in the OPTIMIZE FOR UNKNOWN case, but it is still valid if parameter changes later in any way.
This is significant and it prevents optimizer from performing certain transformations and simplifications of the plan.
OPTION(RECOMPILE) allows optimizer to inline the actual values of parameters during each run and optimizer uses actual values of parameters to generate a better plan. It doesn't have to worry that the generated plan may not work with some other value of parameter, because the plan will not be cached and reused.
This effect is mostly visible for the Dynamic Search Conditions queries. For example:
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 optimizer would treat the query as if it didn't have any WHERE clause at all. The plan would not mention OtherTable at all.
If @ParamSomeID is -1, the plan would join T to OtherTable using Left Anti Semi Join and would scan the whole OtherTable.
If @ParamSomeID is, say, 5, the plan would do an index seek in unique index on OtherTable and read only one row from OtherTable.
Without OPTION(RECOMPILE) this kind of simplification and transformation would not happen.
Another reason to use OPTION(RECOMPILE) is when your data distribution is very skewed. For example, you have a table with 1M rows. One column has value 0 in 990K rows and values from 1 to 10 in 1K rows. The queries that filter on this column should have different plans depending on the actual value of the filter.
In both examples above OPTIMIZE FOR UNKNOWN would generate a mediocre plan.
