为什么在相同计划中，（相同）1000个唯一索引的估计成本不同？

在下面的查询中，两个执行计划都估计将对唯一索引执行1,000个搜索。

这些搜索是由对同一源表的有序扫描驱动的，因此看起来应该最终在相同的源表中搜索相同的值。

两个嵌套循环都有<NestedLoops Optimized="false" WithOrderedPrefetch="true">

谁知道为什么在第一个计划中此任务的成本为0.172434而在第二个计划中的成本为3.01702？

>（提出这个问题的原因是，由于明显降低了计划成本，因此向我建议了第一个查询，作为一种优化。实际上，对我来说，这似乎是在做更多的工作，但我只是想解释一下差异。 ..）

设置

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL); 

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,  
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

查询1“粘贴计划”链接

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

查询2“粘贴计划”链接

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

查询1

查询2

以上内容已在SQL Server 2014（SP2）（KB3171021）-12.0.5000.0（X64）上进行了测试

@Joe Obbish在评论中指出，更简单的复制将是

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN (SELECT * FROM Target) AS T 
    ON T.KeyCol = S.KeyCol;

对于1,000个行的登台表，以上两个仍然具有相同的计划形状（带有嵌套循环），而没有派生表的计划看起来更便宜，但是对于10,000行的登台表和相同的目标上面的表格中的成本差异确实改变了计划的形状（完全扫描和合并联接似乎比昂贵的寻找更具吸引力），表明这种成本差异可能会带来影响，而不仅仅是使计划比较变得困难。

#1 楼

任何人都知道为什么在第一个计划中此任务的成本为0.172434，而在第二个计划中的成本为3.01702吗？

通常来说，在嵌套循环联接下方进行内部搜索的成本是假定随机I / O模式。对于后续访问，有一个基于替换的简单减少，这考虑了所需页面已被先前迭代带入内存的机会。此基本评估产生标准的（较高）成本。

还有另一个成本核算输入，即Smart Seek成本核算，对此鲜为人知。我的猜测（这就是本阶段的全部内容）是SSC尝试更详细地评估内部搜寻I / O成本，也许是考虑了本地顺序和/或要获取的值的范围。谁知道。

例如，第一个查找操作不仅引入所请求的行，还引入该页面上的所有行（按索引顺序）。给定整体访问模式，即使禁用了预读和预取功能，在1000次搜索中获取1000行也仅需要2次物理读取。从这个角度来看，默认的I / O成本表示过高的估计值，而SSC调整后的成本更接近实际。

可以合理地预期，在循环驱动一个SSC的情况下，SSC将是最有效的。索引或多或少直接查找，而联接外部引用是查找操作的基础。据我所知，总是尝试对SSC进行适当的物理操作，但在将查找与其他操作的联接分开时，大多数情况下不会进行向下调整。简单过滤器是对此的一个例外，也许是因为SQL Server经常可以将它们推入数据访问运算符。无论如何，优化器对选择都有很深的支持。

不幸的是，此处子查询外部投影的“计算标量”似乎会干扰SSC。计算标量通常会在连接上方重新放置，但是这些标量必须保留在原处。即便如此，大多数普通的Compute Scalars对优化来说都是透明的，因此这还是有点令人惊讶。

无论如何，当通过简单选择索引PhyOp_Range产生物理操作SelIdxToRng时，SSC都是有效的。当使用更复杂的SelToIdxStrategy（在表上选择索引策略）时，所得的PhyOp_Range运行SSC，但不会减少。同样，似乎更简单，更直接的操作最适合SSC。

我希望我能准确地告诉您SSC的功能，并显示确切的计算，但我不知道这些细节。如果要浏览自己可用的有限跟踪输出，可以使用未记录的跟踪标志2398。示例输出为：

Smart seek costing (7.1) :: 1.34078e+154 , 0.001

该示例与备忘录组7有关，替代项1，显示费用上限，系数为0.001。要查看更简洁的因素，请确保在没有并行的情况下重建表，以便页面尽可能地密集。如果不这样做，则对于示例目标表，该因子更像是0.000821。当然，那里有一些相当明显的关系。

还可以通过未记录的跟踪标志2399禁用SSC。在激活该标志的情况下，这两个成本都是较高的值。

#2 楼

不确定这是否是答案，但是要发表评论有点长。造成这种差异的原因是我个人的纯粹猜测，也许可以为他人思考。

执行计划的简化查询。

SELECT S.KeyCol, 
       S.OtherCol,
       T.*
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

SELECT S.KeyCol, 
       S.OtherCol,
       T.*
FROM staging AS S 
  LEFT OUTER JOIN (
                  SELECT *
                  FROM Target
                  ) AS T 
    ON T.KeyCol = S.KeyCol;

这些等效查询之间的真正区别（实际上可能导致相同的执行计划）之间的主要区别是计算标量运算符。我不知道为什么它必须存在，但我想这是优化程序可以优化派生表的最大范围。

我的猜测是计算标量的存在是什么正在计算第二个查询的IO成本。

从优化器内部：计划成本计算

第一行的CPU成本计算为0.0001581，和
0.000011用于后续的行。
...
0.003125的I / O成本恰好是1/320 –反映了该模型的假设，即磁盘子系统可以执行320个随机I / O每秒的操作数
...
成本计算组件足够聪明，可以识别出需要从磁盘引入的总页数永远不会超过
存储整个表所需的页数。

在我的情况下，该表占用5618页，并且要从1000000行中获取1000行，估计需要的页数是5.618的IO成本为0.015625。

两个查询接缝的CPU成本相同，为0.0001581 * 1000 executions = 0.1581。

因此，根据上面链接的文章，我们可以计算出第一个查询的成本为0.173725。

并且假设我对计算标量如何造成IO成本一团糟是正确的，则可以将其计算为3.2831。

计划中所显示的并不完全正确，但它就在附近。

#3 楼

（最好将其作为对Paul的回答的评论，但我没有足够的代表。）

我想提供以前使用的跟踪标志列表（以及一些DBCC语句）几乎可以得出结论，以防日后调查类似差异会有所帮助。所有这些都不应该在生产中使用。

首先，我看了最终备忘录，以了解正在使用什么物理运算符。根据图形执行计划，它们的外观肯定相同。因此，我使用了跟踪标志3604和8615，第一个将输出定向到客户端，第二个显示了最终备注：

SELECT S.*, T.KeyCol
FROM Staging AS S
      LEFT OUTER JOIN Target AS T
       ON T.KeyCol = S.KeyCol
OPTION(QUERYTRACEON 3604, -- Output client info
       QUERYTRACEON 8615, -- Shows Final Memo structure
       RECOMPILE);

从Root Group追溯，我发现这些几乎相同的PhyOp_Range运算符：

PhyOp_Range 1 ASC 2.0 Cost(RowGoal 0,ReW 0,ReB 999,Dist 1000,Total 1000)= 0.175559(Distance = 2)
PhyOp_Range 1 ASC 3.0 Cost(RowGoal 0,ReW 0,ReB 999,Dist 1000,Total 1000)= 3.01702(Distance = 2)

对我来说，唯一明显的区别是2.0和3.0，它们分别指的是“备忘录组2，原始”和“备忘录组3，原始”。查看备忘录时，它们指的是同一件事-因此尚无差异。

其次，我调查了一堆混乱的跟踪标志，这些跟踪标志对我来说毫无用处-却有一些有趣的内容。我从本杰明·内瓦雷斯（Benjamin Nevarez）举起了大部分东西。我一直在寻找关于在一种情况下而不是另一种情况下应用的优化规则的线索。

 SELECT S.*, T.KeyCol
 FROM Staging AS S
      LEFT OUTER JOIN Target AS T
        ON T.KeyCol = S.KeyCol
 OPTION (QUERYTRACEON 3604, -- Output info to client
         QUERYTRACEON 2363, -- Show stats and cardinality info
         QUERYTRACEON 8675, -- Show optimization process info
         QUERYTRACEON 8606, -- Show logical query trees
         QUERYTRACEON 8607, -- Show physical query tree
         QUERYTRACEON 2372, -- Show memory utilization info for optimization stages 
         QUERYTRACEON 2373, -- Show memory utilization info for applying rules
         RECOMPILE );

第三，我研究了哪些规则适用于我们的PhyOp_Range s看起来很相似。我在博客中使用了Paul提到的几个跟踪标记。

SELECT S.*, T.KeyCol
FROM Staging AS S
      LEFT OUTER JOIN (SELECT KeyCol
                      FROM Target) AS T
       ON T.KeyCol = S.KeyCol
OPTION (QUERYTRACEON 3604, -- Output info to client
        QUERYTRACEON 8619, -- Show applied optimization rules
        QUERYTRACEON 8620, -- Show rule-to-memo info
        QUERYTRACEON 8621, -- Show resulting tree
        QUERYTRACEON 2398, -- Show "smart seek costing"
        RECOMPILE );

从输出中，我们看到直接JOIN应用了此规则来获取我们的PhyOp_Range运算符：Rule Result: group=7 2 <SelIdxToRng>PhyOp_Range 1 ASC 2 (Distance = 2)。子选择改为应用此规则：Rule Result: group=9 2 <SelToIdxStrategy>PhyOp_Range 1 ASC 3 (Distance = 2)。您也可以在这里看到与每个规则相关的“智能搜寻成本”信息。对于直接JOIN，这是输出（对我而言）：Smart seek costing (7.2) :: 1.34078e+154 , 0.001。对于子选择，这是输出：Smart seek costing (9.2) :: 1.34078e+154 , 1。

最后，我不能得出太多结论-但是Paul的答案弥补了大部分差距。我想了解有关智能搜寻成本计算的更多信息。

#4 楼

这也不是真正的答案-正如Mikael指出的那样，很难在注释中讨论此问题...

有趣的是，如果将子查询(select KeyCol FROM Target)转换为嵌入式TVF，您会看到计划，及其成本与简单的原始查询相同：

CREATE FUNCTION dbo.cs_test()
RETURNS TABLE
WITH SCHEMABINDING
AS 
RETURN (
    SELECT KeyCol FROM dbo.Target
    );

/* "normal" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN Target AS T ON T.KeyCol = S.KeyCol;

/* "subquery" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN (SELECT KeyCol FROM Target) AS T ON T.KeyCol = S.KeyCol;

/* "inline-TVF" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN dbo.cs_test() t ON s.KeyCol = t.Keycol

查询计划（pastetheplan链接）：

推导使我相信成本核算引擎对这种子查询可能具有的潜在影响感到困惑。

例如，以下内容：

SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN (
        SELECT KeyCol = CHECKSUM(NEWID()) 
        FROM Target
        ) AS T ON T.KeyCol = S.KeyCol;

您要花多少钱？查询优化器选择与上面的“子查询”变体非常相似的计划，其中包含计算标量（pastetheplan.com链接）：

计算标量具有与上面显示的“子查询”变体的成本有所不同，但是由于查询优化器无法先验地知道返回的行数是多少，因此这仍然只是一个猜测。该计划对左外部联接使用哈希匹配，因为行估计是未知的，因此设置为“目标”表中的行数。

我不知道除了我同意Mikael在他的回答中所做的工作，并因此得出一个很好的结论，并希望其他人可以提出更好的答案。

编程黑洞网

为什么在相同计划中，（相同）1000个唯一索引的估计成本不同？

#1 楼

#2 楼

#3 楼

#4 楼