|
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），但似乎只有其中一个应该计入最后的成本。?
2?? 连接
SQLServer里的连接操作主要有3种类型：嵌套循环（循环）、哈希和合并。嵌套循环连接经常出现在那些查询里每个表常常涉及到很少行的事务处理应用里。哈希和合并连接则经常出现在那些连接常常影响大量行的决策支持应用里。实际上有3种哈希连接。这儿研究的哈希连接是内存中的哈希连接，它的整个哈希表内存都装得下。其他两个哈希连接类型应用于那些可以内存装不下的非常大的数据结构。查询优化器决定哪个连接类型和连接顺序更有效。如果指定了连接类型（循环、哈希或合并），那么连接顺序也固定了。?
2.1?? 嵌套循环连接
下面的查询显示了两表一个简单的连接。连接的执行计划可能包括索引搜索操作、书签查找或表扫描，依赖于指定的搜索参数和可用的索引。
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成本。
如果仅为一个表指定搜索参数，那么作为外部源的表可以避免高成本开销的表扫描。内部源表连接列上的索引在循环连接里是有用的。如果为内部源表指定搜索参数，那么在连接列后跟搜索参数上的索引在中间行的范围内通常不会有高的每行成本。其他可能影响连接顺序的因素是每个表参与的行数。循环连接每行成本结构主张讲很少行的表作为外部源。
?
?
2.2?? 哈希连接
哈希和合并连接都是分开处理内部源和外部源的。连接条件不能用作搜索参数。当没有为表或存在的不合适的索引明确指定搜索参数时，就要对那个表进行扫描。有可能哈希和合并连接有书签查找操作，但也未必，除非强制指定连接类型。
下面的查询明确声明一个哈希连接。连接操作里的每一个表指定了搜索条件，并且两个表都有聚集索引或覆盖索引。
SELECT m.ID,n.Value
FROM M2C m INNER HASH JOIN M2D n ON n.ID = m.ID
WHERE m.GroupID = @Group1 AND n.GroupID = @Group2
图2-19里的执行计划由每个源上的索引搜索操作加上哈希连接共计3部分组成。哈希连接的成本细节在图2-20里显示。
图2-19.哈希连接执行计划
图2-20.哈希连接成本细节
哈希和合并连接对于内部源和外部源的成本结构按照前面描述的索引搜索或表扫描操作的规则。外部源和内部源操作都在所有行参与的单个执行里处理，不象循环连接的内部源是根据外部源的每行执行一次。哈希连接组成的操作的成本结构大致按照下面的规则：
CPU Cost = ~0.017770 + ~0.00001881 per row,1-to-1 join
? ? ? ? + 0.00000523 to 0.000000531 per additional row in IS
第一行适合于一对一的连接。第二行适合于外部源的每一行连接到内部源多行的情形。每行成本结构也适合由少量行的表作为外部源的情形。上面的哈希连接的总成本为以下几个的和：外部源索引搜索，内部源索引搜索和哈希连接。
2.3?? 合并连接
合并连接的细节在SQLServer文档和其他地方有过讨论。有两种类型的合并连接：一对多（包括一对一）和多对多合并连接。两种类型的合并连接都要求每个表里的行排序。一对多连接是更简单更有效的操作。一对多合并连接另外的要求关键是外部源的行必须在连接列上是唯一的。外部源的每一行可以连接内部源的任意多行，但内部源的每一行不能连接外部源的多行。多对多合并连接没有要求这个条件但它是更复杂的操作。
下面的查询特别指定一个合并连接，它将强迫连接排序。外部源（M2C）在列GroupID和ID上有覆盖索引。外部源的连接列是ID字段，它是主键，所以适合一对多的合并连接条件。两个表都明确指定了一个搜索参数允许外部源和内部源上执行索引搜索操作代替表扫描。两个表都有聚集索引或覆盖索引，所以不要求书签查找操作。
SELECT m.ID, n.Value
FROM M2C m INNER MERGE JOIN M2D n ON n.ID = m.ID
WHERE m.GroupID = @Group1 AND n.GroupID = @Group2
合并连接执行计划显示在图2-21里。图2-22显示了合并连接细节。注意最下面的参数（argument）条目是MERGE。该合并连接的成本结构有三部分操作组成：两个索引搜索操作（一个为外部源另一个为内部源）和合并连接操作。索引搜索操作的成本结构已经了解了。
??????
图2-21.合并连接执行计划
图2-22.合并连接成本细节
合并连接的成本结构基本上如下：
CPU Cost = ~0.0056046 + ~0.00000446 per row,one-to-one
成本规则对于某些个案有少量的不同。对于一对多连接，内部源每增加一行的成本是：
CPU Cost 0.000002370 per additional inner source row
对于所以类型的连接，成本结构适合外部源表提供少量行而内部源表提供更多的行的情形。?
2.4??? 多对多合并连接
在下面的查询里，外部源的连接列是ID2，它不是主键，也没有唯一索引。即使其他表的连接列是唯一的，明确的合并连接会强制指定的连接顺序，使第一个表作为外部源。因此该查询需要多对多的合并连接。多对多合并连接执行计划显示在图2-23里，连接细节在图2-24里。
SELECT m.ID, n.Value
FROM M2C m INNER MERGE JOIN M2D n ON n.ID = m.ID2
WHERE m.GroupID = @Group1 AND n.GroupID = @Group2
图2-23.多对多合并连接执行计划
图2-24.多对多合并连接成本细节
这里的I/O成本不为0，且参数是MANY-TO-MANY MERGE。检查范围内的行的成本，发现多对多连接操作有如下的成本规则：
I/O Cost = 0.000310471 per row
CPU Cost = 0.0056000 + 0.00004908 per row,1-1
STATISTICS IO输出结果用表WorkTable显示了前面多对多合并的两个表的作为总和的一部分的I/O。
Table 'Worktable'. Scan count 749,logical reads 1250,physical reads 0,read-ahead reads 0. ?
2.5?? 带排序操作的合并连接
当排序索引不可用时合并连接操作可以和排序操作一起完成。图2-25a显示了内部源上索引分离需求行时的执行计划，但不是按照指定的连接条件排的序。排序操作成本细节显示在图2-25b里。排序随行数增加的CPU成本显示在图2-26里。这种模式的CPU成本推断起来略有困难。注意当成本减去0.0000785后，成本图形在重对数尺寸上是线性的。但坡度略高过1。在大于5000行的某处，排序的CPU成本改变了，但这里不去探究它。
图2-25a.带排序操作的合并连接的执行计划
图2-25b.排序操作成本细节
图2-26.排序操作随行数增加时的CPU成本
5000行以下的排序操作成本接近下面的规则。在5000和10000行之间有骤然变化。
Sort I/O Cost = 0.011261261
Sort CPU Cost ~ 0.0000785 + 0.000005 * (rows ^ 1.16)
排序的CPU成本规则是非线性的。就是说，每行的排序成本随着排序的行数增加。
?
3?? 循环、哈希与合并连接比较
在分别讨论了循环、哈希与合并连接的执行计划成本规则之后，现在将3个连接类型放到适合各自条件的环境下一起比较。
下面的图2-27显示了循环、哈希与合并连接在1P/2P系统上的启动成本。外部源和内部源启动成本描述了索引搜索的基本成本包括每行成本0.0000011。连接成本也除去第一行，这样似乎仅有个案是少量变化的。4P系统的启动成本仅在索引搜索基本成本（0.003283025）上不同。
图2-27.循环、哈希与合并连接在1P/2P系统上索引搜索的启动成本
表2-1显示了在一对一的循环、哈希与合并连接在两个表都有明确的SARG下的每行成本和每页成本。每行（/r）成本适用于所有行，每页（/p）成本仅适用于需要增加的叶页。每行和每页总成本的规则需要将每页条件计算在内。
| 
