我正在测试从群集的列存储索引中删除数据。

我注意到执行计划中有一个渴望切入的后台打印操作符：



具有以下特征：


删除了6000万行
使用了GiB TempDB的时间为
14分钟执行时间
串行计划
1重新绑定到线轴上
扫描的估计成本：364.821

如果我诱使估算器低估了，我会得到一个更快的计划，避免使用TempDB：



估计的扫描成本：56.901

（这是一个估计的计划，但注释中的数字正确。）

有趣的是，如果我通过运行以下命令刷新增量存储，则后台处理会再次消失：

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。

我发布了一个脚本，该脚本生成模式和数据，并引导您在此处演示问题。

现在的问题主要是出于对优化器行为的好奇因为我有一个解决该问题的方法，导致出现问题（一个大的线轴填充了TempDB）。我现在通过使用分区切换来删除。

评论

如果我尝试OPTION（QUERYRULEOFF EnforceHPandAccCard），则后台处理将消失。我认为惠普可能是“万圣节保护”。但是，然后尝试通过USE PLAN提示使用该计划失败（尝试从OPTIMIZE FOR解决方法中使用该计划也是如此）
谢谢@MartinSmith。知道AccCard是什么吗？升序列基数也许是基数？
@JamesLupolt不，我无法提出任何特别令人信服的东西。也许Acc正在累积或访问？

#1 楼


第一个计划中的假脱机迭代器有任何合理的好处吗？


这取决于您认为“合理的”，但是根据成本模型的答案是是。当然，这是正确的，因为优化器总是选择找到的最便宜的计划。

真正的问题是，为什么成本模型认为带有线轴的计划比不带线轴的计划便宜得多。在将任何行添加到增量存储之前，请考虑为新表（通过脚本）创建的估计计划：

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE);


此计划的估计成本非常高771,734个单位：



成本几乎全部与聚集索引删除相关，因为删除预期会导致大量的随机I / O。这只是适用于所有数据修改的通用逻辑。例如，假设对b树索引的无序修改集会导致很大程度上是随机的I / O，并伴有较高的I / O成本。

数据更改计划可能具有排序正是出于这些成本原因，按顺序显示行以促进顺序访问。在这种情况下，由于表已分区，因此影响更加严重。实际上，它非常分区。您的脚本创建了15,000个脚本。由于对中游切换分区（行集）的代价也很高，因此对一个非常分区的表进行随机更新的代价特别高。

要考虑的最后一个主要因素是简单的更新上面的查询（“更新”表示任何数据更改操作，包括删除）均符合称为“行集共享”的优化条件，其中，相同的内部行集用于扫描和更新表。执行计划仍显示两个单独的运算符，但是，仅使用了一个行集。

我之所以这么说是因为能够应用此优化意味着优化器采用的代码路径根本没有考虑显式排序的潜在好处，从而降低了随机I / O的成本。在表是b树的情况下，这是有道理的，因为结构固有地是有序的，因此共享行集会自动提供所有潜在的好处。

重要的结果是，更新的成本核算逻辑如果基础对象是列存储，则操作员不会考虑这种排序优势（促进顺序I / O或其他优化）。这是因为列存储修改未就地执行；他们使用增量存储。因此，成本模型反映了b树上的共享行更新与列存储之间的差异。

尽管如此，在（非常！）分区列存储的特殊情况下，这样做仍然有好处保留顺序，因为从I / O角度来看，在移至下一个分区之前对一个分区执行所有更新可能仍然是有利的。

这里将标准成本逻辑重新用于列存储，因此保留分区顺序（尽管不是每个分区中的顺序）的计划的成本较低。通过使用未记录的跟踪标志2332要求对更新操作符进行排序的输入，我们可以在测试查询上看到这一点。这会将DMLRequestSort属性在更新时设置为true，并迫使优化器生成一个计划，该计划为一个分区提供所有行，然后再移至下一个分区：

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332);


该计划的估计成本非常低，为52.5174个单位：



成本的降低全部归因于更新时估计的I / O成本较低。引入的后台打印程序不执行任何有用的功能，只是它可以保证按分区顺序输出，这是DMLRequestSort = true更新所要求的（列存储索引的串行扫描无法提供此保证）。线轴本身的成本被认为是相对较低的，尤其是与更新时的成本降低（可能不切实际）相比。

决定是否要求对更新操作符进行有序输入在查询优化中非常早。该决策中使用的启发式方法从未被记录下来，但是可以通过反复试验来确定。似乎任何增量存储的大小都是此决定的输入。做出选择后，该选择对于查询编译是永久的。没有USE PLAN提示会成功：计划的目标要么已命令输入更新，要么没有命令。

还有另一种方法来获取此查询的低成本计划，而无需人为地限制基数估计。避免假脱机处理的足够低的估计值可能会导致DMLRequestSort为false，由于预期的随机I / O导致非常高的估计计划成本。另一种选择是将跟踪标志8649（并行计划）与2332（DMLRequestSort = true）结合使用：

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332, QUERYTRACEON 8649);


这将导致使用按分区批量的计划模式并行扫描和保留顺序（合并）的Gather Streams交换：



根据分区排序在硬件上的运行时有效性，这可能会执行最好的三个。也就是说，对列存储进行大的修改不是一个好主意，因此分区切换的主意几乎可以肯定更好。如果您可以解决分区对象经常出现的冗长的编译时间和古怪的计划选择，尤其是在分区数量很大的情况下。

结合许多相对较新的功能，尤其是接近其极限，是获得不良执行计划的好方法。优化程序支持的深度会随着时间的推移逐渐提高，但是使用15,000个列存储分区可能总是意味着您生活在有趣的时代。