问题描述
我遇到了经典的参数嗅探 SQL Server 2012 中的问题.基于一些研究，我找到了解决此问题的多种选择.我需要了解区别的两个选项是 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).
我在遇到此问题的查询结束时犹豫是否使用 OPTION(RECOMPILE)，因为它会强制服务器每次生成新的执行计划.如果我经常调用这个查询，这会增加那台机器的 CPU.
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?
OPTION(OPTIMIZE FOR UNKNOWN) 会重用缓存而不是每次都重新编译吗?
Will OPTION(OPTIMIZE FOR UNKNOWN) reuse cache instead of recompiling each time?
推荐答案
是的，会的.
从 MSDN:
优化未知
指示查询优化器使用统计数据而不是所有局部变量的初始值，当查询被编译和优化，包括使用创建的参数强制参数化.
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.
重新编译
指示 SQL Server 数据库引擎放弃计划执行后为查询生成，强制查询优化器下次执行相同查询时重新编译查询计划.不指定RECOMPILE，数据库引擎缓存查询计划并重复使用它们.编译查询计划时，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:
- 缓存(或不缓存)查询计划.
通常生成的查询计划会被缓存和重用.OPTIMIZE FOR UNKNOWN 不会影响引擎的这个特性.RECOMPILE 抑制此功能并告诉引擎放弃计划而不是将其放入缓存中.
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.
- 在计划生成期间使用(或不使用)实际参数值.
通常优化器嗅探"参数值并在生成计划时使用这些值.OPTIMIZE FOR UNKNOWN 抑制此功能并告诉引擎将所有参数视为它们的值是未知的.Optimizer 具有内置规则和启发式方法，可以说明如何将可用统计信息用于各种过滤标准.请参阅优化……平庸? 了解更多详情.通常，参数嗅探在查询/存储过程的第一次运行时使用，并在第一次运行期间使用参数值.生成的计划被缓存，以后可以重用.
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 提示)生成的计划必须有效并为 任何可能的参数值.它是根据在正常/无提示情况下第一次运行期间使用的嗅探值量身定制的；它不是针对 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.
这很重要，它会阻止优化器执行计划的某些转换和简化.
This is significant and it prevents optimizer from performing certain transformations and simplifications of the plan.
OPTION(RECOMPILE) 允许优化器在每次运行期间内联参数的实际值，并且优化器使用参数的实际值来生成更好的计划.不必担心生成的计划可能不适用于其他一些参数值，因为计划不会被缓存和重用.
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)
如果 @ParamSomeID 是 0 优化器会将查询视为根本没有任何 WHERE 子句.该计划根本不会提及OtherTable.
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 是 -1，计划将使用 Left Anti Semi Join 将 T 连接到 OtherTable并且会扫描整个OtherTable.
If @ParamSomeID is -1, the plan would join T to OtherTable using Left Anti Semi Join and would scan the whole OtherTable.
如果 @ParamSomeID 是，比如说，5，该计划将在 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.
如果没有 OPTION(RECOMPILE) 这种简化和转换就不会发生.
Without OPTION(RECOMPILE) this kind of simplification and transformation would not happen.
使用 OPTION(RECOMPILE) 的另一个原因是当您的数据分布非常倾斜时.例如，您有一个包含 100 万行的表.一列在 990K 行中具有值 0，在 1K 行中具有从 1 到 10 的值.根据过滤器的实际值，在此列上过滤的查询应该有不同的计划.
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.
在上面的两个例子中，OPTIMIZE FOR UNKNOWN 会生成一个平庸的计划.
In both examples above OPTIMIZE FOR UNKNOWN would generate a mediocre plan.
这篇关于OPTION(OPTIMIZE FOR UNKNOWN) 和 OPTION(RECOMPILE) 之间的主要区别是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！