What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?

Question

I run into the classic Parameter Sniffing issues in SQL Server 2012. Based on some research I found multiple options around this problem. The two options that I need to understand the difference between are OPTION(OPTIMIZE FOR UNKNOWN) vs OPTION(RECOMPILE).

I am hesitating to use OPTION(RECOMPILE) at the end of my queries that are having this issue because it will force the server to generate a new execution plan each time. If I call this query often this will spike up the CPU of that machine.

So that I use he best available solution, what are the real differences between the two options?

Will OPTION(OPTIMIZE FOR UNKNOWN) reuse cache instead of recompiling each time?

Answer 1

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:

1. 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.

2. 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.