主页 / dba / 问题 / 286705
Accepted
user2864740
Asked: 2021-03-09 15:41:38 +0800 CST

为什么 SQL Server 在将结果选择到标量变量时不使用 OPTION(RECOMPILE) 执行常量 (UNION ALL) 分支消除？

  • 772

我们使用一些“聚合”视图使用鉴别器从多个表中进行选择（注意：这些视图不是分区视图，因为鉴别器不在基表中）。这通常在使用 时效果很好option(recompile)，因为查询计划程序将在选择查询计划之前消除不可到达的union all路径。

但是，当将结果选择为标量变量时，这种恒定折叠优化似乎失败了。将结果选择到临时表变量中不会对重新编译进行反优化。

这是 SQL Server 2017 中的复制案例：

-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1));

-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table

正常查询，这会导致优化的查询计划仅涉及其中一个union all分支：

declare @descrim int = 2;

select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"

但是，一旦使用“选择到标量变量”，该计划就会变得不优化，因为它不会消除未使用的联合。（在查询文本中使用文字值时，该计划仍然正确优化。）

declare @descrim int = 2;
declare @brokeit int;

select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!

1. 这种去优化是“预期的”吗？

2. 关于和/或选择标量变量的这种显着的去优化行为在哪里option(recompile)记录或以其他方式深入讨论？

3. 有没有一种简单的方法可以在select @x = ..不使用临时表（变量）的情况下获得重新编译优化的计划？

虽然在查询执行期间，这union all将阻止对辅助工件的实际 IO 访问，但这仍然是查询计划生成的问题。在产生此问题的特定错误情况下，保留多个表以供考虑会阻止 SQL Server 选择适当的搜索计划，并且生成的计划选项在给定域中是非常糟糕的选择。

第一个“好”计划：

在此处输入图像描述

第二个也是“坏”的计划：

在此处输入图像描述

这个“坏”计划也有一个隐式转换警告，让我怀疑选择到标量变量可能会绕过许多不同的优化 - 甚至option(recompile)完全忽略提示。

sql-server optimization

2 个回答

  1. Best Answer

    常量折叠在 SQL Server 中具有特殊的含义。它不直接涉及您的问题。参数嵌入优化(PEO) 和矛盾检测结合起来为您提供了执行计划的广泛简化。

    在安全的情况下，PEO 将例如参数或局部变量的字面值嵌入到查询文本中。其中一项要求OPTION (RECOMPILE)是指定的。这保证了生成的计划永远不会被重用，因此用嗅探文字替换非文字可能是安全的。

    提示本身只提供每次执行都会生成一个新的OPTION (RECOMPILE)计划，任何参数的嗅探值都将用于基数估计，并且生成的一次性计划在执行后不会被缓存以供重用。

    PEO 最初是在 SQL Server 2008 中添加到产品中的，但由于可能出现不正确的结果，不久之后它就被禁用了。它在 SQL Server 2008 SP1 CU5（ Microsoft 博客文章）中重新启用。

    使用 PEO 优化的查询在查询优化器看来就像是用文字而不是参数或变量编写的查询一样。矛盾检测可以删除WHERE 0 = 1出现类似文字表达式的整个子句或关系运算符子树。之所以存在这种工具，是因为自动化工具通常会生成这样的 SQL。

    应用 PEO 并不总是安全的，但没有正式记录例外情况。一个例外是发生变量赋值的地方（其他存在，例如参数出现在OPTIMIZE FOR子句中的地方）。我的理解是，变量赋值涉及大量复杂的遗留行为，偶尔会有奇怪的语义，出于向后兼容性的原因而保留。保证 PEO 在所有情况下都能正确运行是不切实际的，因此在这种情况下禁用它。

    PEO 是一种机会主义工具，它超越了OPTION (RECOMPILE). 在许多情况下，它可以带来显着的性能优势，但没有正式记录。人们可能会将其视为一项奖励功能 - 得到它时很好，但如果失望，则不会退款。

    在您的示例中，无法应用 PEO，启动过滤器（已记录）提供了对子树执行的消除。“未优化”计划中显示的过滤器运算符是启动过滤器，仅当启动谓词评估为true时才执行其子树。

    在 PEO 上下文中，缺少“寻求计划”通常是由于优化只能在存在文字值时执行（由于安全问题或实施限制）。此文字可能出现在原始文本中，也可能已通过 PEO 替换。这方面的一个例子是优化规则SelOnSeqPrj，它允许谓词ROW_NUMBER在安全时通过序列函数，但仅在文字值可用时。

    问题中代码的SQL Server 2017 复制不会产生问题中提到的额外计算标量和隐式转换。用于生成该计划的查询似乎与问题中给出的查询不同。或者，实例或数据库可能有一些未指定的重要配置或选项。无论如何，我无法重现它。

    OPTION (RECOMPILE)提示永远不会被忽略。查询中唯一的隐式转换是根据（而不是）的要求从to的内部bigint结果转换。COUNT(*)integerCOUNTCOUNT_BIG

    根据问题背后实际应用程序的要求和限制，您可能需要使用动态 SQL 或其他一些解决方案。如果可以以适合我们问答格式的方式表达，请随意提出有关潜在问题的潜在解决方案的新问题。

    您的问题的简要答案是：

    1. 是的。
    2. 我在Parameter Sniffing、Embedding 和 RECOMPILE Options中介绍了它。
    3. 不。
    • 7

  2. 您可以 SELECT INTO 临时表并从中分配变量作为解决方法：

    declare @descrim int = 2;
declare @brokeit int;

select count(col1) c
into #results
from [test].test_view
where descrim = @descrim
option (recompile)
    
select @brokeit = c from #results
    • 0

相关问题