sql-server – 为什么在这些计划中,(相同的)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),32)
FROM master..spt_values v1;

查询1 “Paste the plan” link

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 “Paste the plan” link

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;

VS

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

对于1,000行临时表,上述两者仍然具有相同的计划形状和嵌套循环,而没有派生表的计划看起来更便宜,但对于10,000行临时表和上述相同的目标表,成本差异确实改变了计划形状(具有完全扫描和合并连接似乎比昂贵的成本搜索相对更具吸引力)显示这种成本差异可能具有其他影响,而不仅仅是使比较计划更难.

解决方法

Anyone know why this task is costed at 0.172434 in the first plan but 3.01702 in the second?

一般而言,假设随机I / O模式,在嵌套循环连接下方的内侧搜索是成本计算的.对于后续访问,存在简单的基于替换的简化,考虑了前一次迭代已经将所需页面带入内存的可能性.该基本评估产生标准(更高)成本.

还有另一个成本计算输入,Smart Seek Costing,关于哪些细节已知.我的猜测(这就是现阶段的全部)是SSC尝试更详细地评估内侧寻求I / O成本,可能是考虑本地排序和/或要获取的值范围.谁知道.

例如,第一个搜索操作不仅引入请求的行,而且引入该页面上的所有行(按索引顺序).给定整体访问模式,即使禁用预读和预取,在1000次搜索中获取1000行也只需要2次物理读取.从这个角度来看,默认的I / O成本代表了一个显着的高估,而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.激活该标志后,两个成本都是较高的值.

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!