OPTION（OPTIMIZE FOR UNKNOWN）和OPTION（RECOMPILE）之间的主要区别是什么？

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

我在SQL Server 2012中遇到了经典的参数嗅探问题。基于一些研究,我发现了围绕这个问题的多种选择。我需要理解它们之间的区别的两个选项是OPTION(OPTIMIZE FOR UNKNOWN)和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.

我犹豫是否在我的查询结束时使用OPTION(RECOMPILE),因为它会强制服务器每次都生成一个新的执行计划。如果我经常调用此查询,这将会占用该计算机的CPU。

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?

OPTION(OPTIMIZE FOR UNKNOWN)是否会重用缓存而不是每次重新编译?

3 个解决方案

#1

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

OPTION(OPTIMIZE FOR UNKNOWN)是否会重用缓存而不是每次重新编译?

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.

OPTION(RECOMPILE)将强制重新编译每次执行,并且是一种相当沉重的方法。仅在分析DW / BI环境中才有意义,其中每个查询可能不同,复杂并且可能具有显着的运行时间。

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

查询存储(仅限SQL 2016),因为它允许您“固定”优惠的执行计划

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

这两种方法都可以让您获得与帖子相同的效果,但是以非侵入性的方式(没有应用程序代码/查询更改)。

#2

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

OPTION(OPTIMIZE FOR UNKNOWN)是否会重用缓存而不是每次重新编译?

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:

从MSDN的引用中可以看出OPTION(OPTIMIZE FOR UNKNOWN)和OPTION(RECOMPILE)之间有两个主要区别:

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.

指示SQL Server数据库引擎在执行后丢弃为查询生成的计划,强制查询优化器在下次执行相同查询时重新编译查询计划。如果不指定RECOMPILE,数据库引擎会缓存查询计划并重用它们。编译查询计划时,RECOMPILE查询提示使用查询中任何局部变量的当前值,如果查询位于存储过程内,则传递给任何参数的当前值。

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.

通常,生成的查询计划会被缓存并重用。 OPKMIZE FOR UNKNOWN不会影响引擎的此功能。 RECOMPILE禁止此功能并告诉引擎丢弃计划而不将其放入缓存中。

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.

通常,优化器会“嗅探”参数值,并在生成计划时使用这些值。 OPTIMIZE FOR UNKNOWN抑制此功能并告诉引擎将所有参数视为其值未知。优化程序具有内置规则和启发式方法,如何使用可用的统计信息来处理各种过滤条件。请参阅优化...平庸?更多细节。通常,参数嗅探在第一次运行查询/存储过程时使用,并在第一次运行期间使用参数值。生成的计划将被缓存,之后可以重复使用。

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.

这里要记住的一个非显而易见的事情是,在两种情况下(正常时没有任何查询提示和OPTIMIZE FOR UNKNOWN提示)生成的计划必须有效并为任何可能的参数值生成正确的结果。它适用于在正常/无提示情况下第一次运行期间使用的嗅探值;它不适用于OPTIMIZE FOR UNKNOWN情况下的任何特定值,但如果参数稍后以任何方式更改,它仍然有效。

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.

OPTION(RECOMPILE)允许优化器在每次运行期间内联参数的实际值,优化器使用参数的实际值来生成更好的计划。它不必担心生成的计划可能无法与其他参数值一起使用,因为计划不会被缓存和重用。

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.

如果@ParamSomeID为0,则优化器会将查询视为根本没有任何WHERE子句。该计划根本不会提及OtherTable。

If @ParamSomeID is -1, the plan would join T to OtherTable using Left Anti Semi Join and would scan the whole OtherTable.

如果@ParamSomeID为-1,则计划将使用Left Anti Semi Join将T连接到OtherTable,并将扫描整个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.

如果@ParamSomeID是5,那么该计划将在OtherTable上的唯一索引中执行索引查找,并从OtherTable中只读取一行。

Without OPTION(RECOMPILE) this kind of simplification and transformation would not happen.

如果没有OPTION(RECOMPILE),这种简化和转换就不会发生。

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.

使用OPTION(RECOMPILE)的另一个原因是您的数据分布非常偏斜。例如,您有一个包含1M行的表。一列在990K行中具有值0,在1K行中具有从1到10的值。筛选此列的查询应具有不同的计划,具体取决于筛选器的实际值。

In both examples above OPTIMIZE FOR UNKNOWN would generate a mediocre plan.

在上面两个例子中,OPTIMIZE FOR UNKNOWN将产生一个平庸的计划。

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

我用过这两个。 OPTION(OPKMIZE FOR UNKNOWN)用于获取各种参数的重型搜索存储过程。有一些条件,我不知道(统计数据和什么不是),这将导致优化,查询是平凡的,但是,它会导致严重的延迟(甚至超时)。选项(未知的优化)解决了这个问题,但并不理想。

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.

同样繁重的搜索程序会产生间歇性问题,这意味着几个月后搜索会超时。直接的解决方案是调用sp_recompile,它与向存储过程添加OPTION(RECOMPILE)子句同义。

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.

最后,我删除了OPTION(OPTIMIZE FOR UNKNOWN),只是简单地将EXEC sp_recompile 添加到我的夜间维护工作中,并解决了所有问题。

#1