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

Question

我在 SQL Server 2012 中遇到了经典的 参数嗅探 问题。根据一些研究，我发现了围绕这个问题的多种选择。我需要了解两者之间区别的两个选项是 OPTION(OPTIMIZE FOR UNKNOWN) vs OPTION(RECOMPILE) 。

我犹豫是否在遇到此问题的查询结束时使用 OPTION(RECOMPILE) 因为它会强制服务器每次生成一个新的执行计划。如果我经常调用这个查询，这会增加那台机器的 CPU。

所以我使用他最好的解决方案，这两个选项之间的真正区别是什么？

OPTION(OPTIMIZE FOR UNKNOWN) 会重用缓存而不是每次都重新编译吗？

原文由 Junior 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

OPTION(OPTIMIZE FOR UNKNOWN) 会重用缓存而不是每次都重新编译吗？

是的，它会的。

---

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

OPTIMIZE FOR UNKNOWN

指示查询优化器在编译和优化查询时使用统计数据而不是所有局部变量的初始值，包括通过强制参数化创建的参数。

RECOMPILE

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

因此，两个主要区别是：

1. 缓存（或不缓存）查询计划。

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

1. 在计划生成期间使用（或不使用）实际参数值。

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

这里要记住的一件不明显的事情是，在这两种情况下（正常没有任何查询提示和 OPTIMIZE FOR UNKNOWN 提示）生成的计划必须是有效的，并为 任何 可能的参数值产生正确的结果。它适用于在正常/无提示情况下首次运行期间使用的嗅探值；它不是针对 OPTIMIZE FOR UNKNOWN 案例中的任何特定值量身定制的，但如果以后以任何方式更改参数，它仍然有效。

这很重要，它会阻止优化器执行计划的某些转换和简化。

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

这种效果在 动态搜索条件 查询中最为明显。例如：

 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 -1 , the plan would join T to OtherTable using Left Anti Semi Join and would scan the whole OtherTable .

如果 @ParamSomeID 是 5，则计划将在 OtherTable 上的唯一索引中进行索引搜索，并从 OtherTable 中仅读取一行。

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

使用 OPTION(RECOMPILE) 的另一个原因是当您的数据分布非常倾斜时。例如，您有一个包含 1M 行的表。一列在 990K 行中具有值 0，在 1K 行中具有从 1 到 10 的值。根据过滤器的实际值，在此列上过滤的查询应该有不同的计划。

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

原文由 Vladimir Baranov 发布，翻译遵循 CC BY-SA 3.0 许可协议