What are the main differences between OPTION(OPTIM

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.

Answer 2

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

Yes. Optimize for unknown will influence how the plan is generated (i.e. explicitly prevent it from sniffing parameters and compare it with column data histogram), but once generated the plan stays in cache and is reused.

OPTION(RECOMPILE) will force a recompile on every execution and is a rather heavy handed approach. It makes sense only in an analytical DW/BI environments where each query may be different, complex and probably with a significant run time.

You also have other options at your disposal:

• Plan Guides
• Query Store (only in SQL 2016) as it allows you to 'pin' a favoured execution plan

Both of these allow you to obtain the same effect as in your post, but in a non-invasive way (no app code/query changes).

Answer 3

I have used both. OPTION(OPTIMIZE FOR UNKNOWN) was used for a heavy search stored procedure that took in a variety of parameters. There were certain conditions, unknown to me (statistics and what not), that would throw the optimization off, the query was mundane, however, it would cause serious delays (and even time out). OPTION(OPTIMIZE FOR UNKNOWN) solved this issue but was not ideal.

The same heavy search procedure would have intermittent issues, meaning after a few months, the search would time out. The immediate solution would be to call sp_recompile , which is synonymous to adding a OPTION(RECOMPILE) clause to the stored procedure.

The guts of the stored procedure propelled a "result as you type" solution in which every three keystrokes would trigger a DB search and the results would populate in a dropdown.

In the end, I removed the OPTION(OPTIMIZE FOR UNKNOWN) and just simply added a EXEC sp_recompile<sp> to my nightly maintenance job and that resolved all issues.