SQLServer执行计划成本（续1）

聚合

查询里使用聚合函数（MIN、MAX、COUNT、AVG或SUM）时发生流聚合（Stream Aggregate）和标量计算（Compute Scalar）如下所示。

SELECT COUNT(*), AVG(Value) FROM M2C_10

MIN 和 MAX 函数要求流聚合操作。在执行计划里其他的函数要求流聚合后跟一个或多个标量计算操作。


图2-12. 流聚合和标量计算的执行计划

图2-13. 流聚合操作的成本细节

图2-14. 标量计算操作的成本细节

两个操作都列出每行的 CPU 为 0.0000001 （ I/O 成本为 0 ），但似乎只有其中一个应该计入最后的成本。 

连接

SQLServer 里的连接操作主要有 3 种类型：嵌套循环（循环）、哈希和合并。嵌套循环连接经常出现在那些查询里每个表常常涉及到很少行的事务处理应用里。哈希和合并连接则经常出现在那些连接常常影响大量行的决策支持应用里。实际上有 3 种哈希连接。这儿研究的哈希连接是内存中的哈希连接，它的整个哈希表内存都装得下。其他两个哈希连接类型应用于那些可以内存装不下的非常大的数据结构。查询优化器决定哪个连接类型和连接顺序更有效。如果指定了连接类型（循环、哈希或合并），那么连接顺序也固定了。 

嵌套循环连接

下面的查询显示了两表一个简单的连接。连接的执行计划可能包括索引搜索操作、书签查找或表扫描，依赖于指定的搜索参数和可用的索引。

SELECT M2C_01.ID, M2D_01.Value
FROM M2C_01 INNER JOIN M2D_01 ON M2D_01.ID = M2C_01.ID2
WHERE M2C_01.GroupID = 1

对于上面的查询，表 M2C_01 在列 GroupID 、 ID2 和 ID （如果 ID 是聚集主键的一部分或全部则自动被包括了）上有一个覆盖索引，表 M2D_01 在列 ID 上有一个聚集索引。循环连接执行计划如图 2-14 所示，涉及到的行很少索引计划成本最低。

图2-14. 循环连接执行计划

上面的执行计划里上表（ M2C_01 ）是外部源（ 译者注： 帮助文档里称为外部输入表），下表（ M2D_01 ）是内部源（ 译者注： 帮助文档里称为内部输入表）。上面嵌套循环连接的三个组件的成本细节显示在图 2-15a 、 15b 、 15c 里。循环连接计划首先取得外部源的行。对于外部源的每一行，从内部源里找到所有匹配的行。即使没有明确指定内部源表的搜索参数，在嵌套循环连接里连接条件自身可以作为搜索参数。在本例中，从外部源返回 10 行，成本细节显示估计行计数＝ 10 且估计执行数（ estimated number of executes ）＝ 1 。

 
图-15a. 外部源索引搜索成本细节

图-15b. 内部源索引搜索成本细节
 
图-15c. 循环连接成本细节

外部源的每一行正好匹配内部源的每一行。内部源索引搜索操作的成本细节显示估计行计数为 1 且估计执行数为 9.919 （实际是 10 ）。嵌套循环 / 内部连接成本细节显示估计行计数为 9 但 Set 选项 SHOWPLAN_ALL 显示是 9.92 。显示的成本细节低而不是四舍五入到最接近的值。注意循环连接细节里的 Argument （参数）是 OUTER REFERENCES 。它的重要性稍后讨论。

当外部源是索引搜索操作时（不是书签查找），该组件的成本仅是前面讨论的索引搜索操作成本。但带参数条件 OUT REFERENCES 的循环连接构成的操作的 I/O 成本为 0 ， CPU 成本规则如下：

Loop join CPU Cost = 0.00000418 per row

内部源表的成本细节显示为单行索引搜索的 I/O 和 CPU 成本（ 0.0063285 和 0.0000796 ），但是整个操作的成本（执行了 9.919 次）是 0.00768413 。执行数（ number of executes ）的成本规则不是简单的 I/O 和 CPU 成本的和再乘以执行数的值。

事实上，行计数对于循环连接的内部源而言有 3 种不同的成本，通过下面 2 个表的连接操作的 3 个查询可以模拟。（第 4 种情况缠绕操作将在以后解释。）循环连接要明确指定连接顺序，第一个表作为外部源，第二个表作为内部源。对于所有的情形，外部源表有一个覆盖索引用 GroupID 跟在连接列的后面。
情形 1
SELECT M2C_01.ID, N1C_01.Value
FROM M2C_01 INNER LOOP JOIN N1C_01 ON N1C_01.ID = M2C_01.ID6
WHERE M2C_01.GroupID = @Group1

情形2
SELECT M2C_01.ID, M2D_01.Value
FROM M2C_01 INNER LOOP JOIN M2D_01 ON M2D_01.ID = M2C_01.ID2
WHERE M2C_01.GroupID = @Group1

情形3
SELECT M2C_01.ID, M2D_01.Value
FROM M2C_01 INNER LOOP JOIN M2D_01 ON M2D_01.ID = M2C_01.ID2
WHERE M2C_01.GroupID = @Group1 AND M2D_01.GroupID = @Group2

情形 1 和情形 2 没有逻辑区别。区别仅在内部源表的大小上。情形 1 中，表 N1C_01 很小，更明确的说单个 8K 的页能存下。情形 2 中，表 M2D_01 不小，有 50000 行 506 页。外部源表 M2C_1 正好也是 50000 行，但这不影响成本结构。情形 1 和情形 2 连接都为内部源使用聚集索引上的索引搜索操作。情形 3 和情形 2 有相同的表。不同的是情形 3 为内部源表明确指定了搜索参数。这允许内部源的索引搜索使用在连接列后跟 GroupID 上的覆盖索引。

现在来考虑循环连接的本质。对于外部源的每一行，在内部源里找到匹配的行。不能保证外部源里的连续行会匹配内部源里的连续行。例如，外部源的第一行连接列的值也许是 1 ，接下来的行可能的值是 2 或 50000 。外部源的每一行都要求一个完整的索引搜索操作来定位匹配内部源里的记录。唯一确定的协同作用是为内部源表使用的索引的根级已被找到。

图 2-16 显示了上面的例子里情形 1 、 2 和 3 循环查询的执行计划的常规成本。术语常规成本是除以单行索引搜索成本（ 1P/2P 系统为 0.0064081 ）后的成本。 4P 系统成本实质上有相同的成本结构，除了索引搜索基本成本大概是 1P/2P 系统的一半外。 3 种情形都有相同的外部源索引搜索和循环连接构成的成本，所以区别完全是由于内部源索引搜索成本引起。

图2-16. 循环连接成本与参与的行

在情形 1 里，内部源表单个页就能装下，所以内部源的执行计划仅仅为外部源的每一行重复读取一个叶页即可。情形 1 的内部源成本规则相当接近下面：

情形1 Inner Source Cost = 0.0063285 + 0.0000796 per execute

情形 2 里，内部源表不小。成本规则适当的低于执行数，如下：

情形2 I.S. Cost = 0.0063285 + 0.000140-0.000154 per execute

对于有大量的行参与的情况，成本规则急剧地偏离上面的等式。在情形 3 里，内部源表不小，但覆盖索引把需要的行从内部源里隔离到一个限制的范围了。情形 3 的内部源成本规则等同于有少量行参与的情形 2 ，但不会骤然增加 100 到 200 行。

图 2-17 显示了上面讨论的 3 个循环连接每行的不同的常规成本。在 300 行使每行的不同成本是通过在 300 行连接和 100 行连接之间不同的成本除以 200 行里不同的成本计算得到的。

图2-17. 循环连接每行的不同成本

情形 1 的查询非常接近上面描述的成本规则（在内部源表里每增加一个执行的成本为 0.0000796 ）。情形 2 和 3 的查询在内部源里有很少行参与时的成本规则大约为每行 0.00015 。有时在 100 和 200 行之间，情形 2 的内部源成本波动很剧烈。最后，在有大量行参与时（大约 1000 ～ 2000 行的情形），所有情形增加的成本是内部源上每个执行为 0.0000796 。情形 3 成本结构逐渐从情形 2 范围减少到情形 1 的范围。在少数情况下，明确指定 SARG （搜索参数）的唯一好处可能是情形 3 的双倍效果。

上面的计划成本图形是一对一的连接，外部源的每一行正好和内部源的一行连接。当外部源的每一行连接到内部源的多行时，内部源上增加的 CPU 成本（称为每执行成本）为： I.S. CPU Cost 0.0000011 per additional row 如果外部源的每一行匹配内部源里大量的行，则每增加一个叶页的成本大概为 0.00074074 ，但该情形测试不了。循环连接组成的成本（每行 0.00000418 ）适用于连接的所有行。

循环连接可以为内部源和外部源的其中一个或二者都使用书签查找操作。图 2-18 里的第一个执行计划就是在外部源发生连接操作之前进行书签查找操作。第二个计划显示了内部源连接之后的书签查找操作。书签查找可以在连接操作之前或之后。书签查找的成本似乎依循前面所讲的规则，但这并没有得到广泛的论证。偶尔也能看到成本异常。外部源也可能是表扫描操作。大可不必在循环连接的内部源上进行表扫描，因为这会让外部源的每一条目都执行一次。



图2-18. 带书签查找的循环连接

循环连接成本结构总结如下：外部源的成本构成按照早前来自索引搜索和书签查找（如需要的话）或表扫描的规则。内部源更复杂些。如果没有明确指定搜索参数，那么或多或少和增加的每行成本有关。循环连接构成的操作自身相当简单，仅有每行的 CPU 成本。

如果仅为一个表指定搜索参数，那么作为外部源的表可以避免高成本开销的表扫描。内部源表连接列上的索引在循环连接里是有用的。如果为内部源表指定搜索参数，那么在连接列后跟搜索参数上的索引在中间行的范围内通常不会有高的每行成本。其他可能影响连接顺序的因素是每个表参与的行数。循环连接每行成本结构主张讲很少行的表作为外部源。