我注意到执行计划中有一个大的急切线程操作符：
这完成了以下特征：
删除了> 6000万行
> 1.9使用GiB TempDB
> 14分钟的执行时间
>系列计划
> 1重新绑定在线轴上
>估计扫描成本：364.821
如果我欺骗估算器低估,我会得到一个更快的计划,避免使用TempDB：
预计扫描成本：56.901
(这是一个估计的计划,但评论中的数字是正确的.)
有趣的是,如果我通过运行以下命令刷新delta存储,则spool会再次消失：
ALTER INDEX IX_Clustered ON Fact.RecordedMetricsDetail REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
要检查增量存储的大小,我正在运行以下查询以检查表的行内页面：
SELECT
SUM([in_row_used_page_count]) AS in_row_used_pages,SUM(in_row_data_page_count) AS in_row_data_pages
FROM sys.[dm_db_partition_stats] as pstats
JOIN sys.partitions AS p
ON pstats.partition_id = p.partition_id
WHERE p.[object_id] = OBJECT_ID('Fact.RecordedMetricsDetail');
第一个计划中的阀芯迭代器有什么合理的好处吗？我必须假设它的目的是提高性能,而不是万圣节保护因为它的存在不一致.
我在2016 CTP 3.1上测试了这个,但我在2014 SP1 CU3上看到了相同的行为.
我发布了一个生成模式和数据的脚本,并引导您演示问题here.
问题主要是出于对优化器在此时的行为的好奇,因为我有一个问题的解决方法提示问题(一个大的线轴填充TempDB).我现在正在使用分区切换来删除.
解决方法
Is there any plausible benefit to the spool iterator in the first plan?
这取决于你认为“合理”的东西,但根据成本模型的答案是肯定的.当然这是事实,因为优化器总是选择它找到的最便宜的计划.
真正的问题是为什么成本模型认为计划与线轴比没有计划便宜得多.在将任何行添加到增量存储之前,请考虑为新表(从脚本)创建的估计计划：
DELETE Fact.RecordedMetricsDetail WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE()) OPTION (RECOMPILE);
该计划的估计成本为771,734单位：
成本几乎全部与聚集索引删除相关联,因为删除预计会导致大量随机I / O.这只是适用于所有数据修改的通用逻辑.例如,假设对b树索引的无序修改集导致很大的随机I / O,并且具有相关的高I / O成本.
由于这些成本原因,数据更改计划可能具有排序以按顺序显示行以促进顺序访问.在这种情况下,影响会加剧,因为表是分区的.事实上,非常分区;你的脚本创建了15,000个.对非常分区的表的随机更新的成本特别高,因为在流中切换分区(行集)的价格也是高成本的.
要考虑的最后一个主要因素是上面的简单更新查询(其中’update’表示任何数据更改操作,包括删除)符合称为“行集共享”的优化,其中相同的内部行集用于扫描和更新表格.执行计划仍然显示两个独立的运算符,但是,只使用了一个行集.
我提到这一点是因为能够应用此优化意味着优化器采用的代码路径根本不考虑显式排序的潜在好处,以降低随机I / O的成本.如果表是b树,这是有道理的,因为结构本身是有序的,因此共享行集会自动提供所有潜在的好处.
重要的结果是更新运算符的成本计算逻辑不考虑底层对象是列存储的这种排序好处(促进顺序I / O或其他优化).这是因为列存储修改不是就地执行的;他们使用三角洲商店.因此,成本模型反映了b树与列存储上的共享行集更新之间的差异.
然而,在(非常！)分区列存储的特殊情况下,保留排序可能仍然有好处,因为从I / O角度来看,在移动到下一个分区之前对一个分区执行所有更新可能仍然是有利的.
标准成本逻辑在此处重复用于列存储,因此保留分区排序的计划(尽管不是每个分区中的顺序)的成本较低.我们可以通过使用未记录的跟踪标志2332在测试查询中看到这一点,以要求对更新运算符进行排序输入.这会在更新时将DMLRequestSort属性设置为true,并强制优化器生成一个计划,该计划在移动到下一个分区之前为一个分区提供所有行：
DELETE Fact.RecordedMetricsDetail WHERE MeasurementTime < DATEADD(day,GETUTCDATE()) OPTION (RECOMPILE,QUERYTRACEON 2332);
该计划的估计成本非常低,为52.5174单位：
这种成本的降低都是由于更新时估计的I / O成本较低.引入的Spool不执行任何有用的功能,除了它可以保证按分区顺序输出,如DMLRequestSort = true的更新所需(列存储索引的串行扫描不能提供此保证).线轴本身的成本被认为相对较低,特别是与更新时的成本降低(可能不切实际)相比.
在查询优化中很早就决定是否要求对更新运算符进行有序输入.此决定中使用的启发式方法从未被记录,但可以通过反复试验来确定.似乎任何增量商店的大小都是这个决定的输入.一旦完成,选择对于查询编译是永久性的.没有USE PLANhint会成功：计划的目标是订购了更新的输入,或者没有.
还有另一种方法可以获得此查询的低成本计划,而无需人为地限制基数估计.避免假脱机的足够低的估计可能导致DMLRequestSort为假,由于预期的随机I / O导致非常高的估计计划成本.另一种方法是使用跟踪标志8649(并行计划)和2332(DMLRequestSort = true)：
DELETE Fact.RecordedMetricsDetail WHERE MeasurementTime < DATEADD(day,QUERYTRACEON 2332,QUERYTRACEON 8649);
这导致使用每分区批处理模式并行扫描和订单保留(合并)Gather Streams交换的计划：
根据硬件上分区排序的运行时有效性,这可能是三者中最好的.也就是说,对列存储进行大量修改并不是一个好主意,因此分区切换的想法几乎肯定更好.如果您可以应对分区对象经常出现的长编译时间和奇怪的计划选择 – 特别是当分区数量很大时.
将许多相对较新的功能组合在一起,尤其是接近极限,是获得糟糕执行计划的好方法.优化器支持的深度随着时间的推移趋于改善,但使用15,000个列存储分区可能总是意味着您生活在有趣的时代.
