对于群集列存储中的删除，急切的假脱机操作符有用吗？

Question

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

我注意到在执行计划中有一个大的急切的假脱机操作符：

这一过程具有以下特点：

• 删除6 000万行
• 1.9 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年的CTP3.1上进行测试，但我在2014年的SP1 CU3上看到了同样的行为。

我已经发布了一个脚本，它生成模式和数据，并引导您演示问题这里。

这个问题主要是出于对优化器目前的行为的好奇，因为我有一个解决方法来解决这个问题(一个装满了TempDB的大假脱机)。我现在使用分区切换来删除。

Answer 1

第一个计划中的假脱机迭代器有什么可行的好处吗？

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

真正的问题是，为什么成本模型用线轴来考虑计划比没有计划要便宜得多。在将任何行添加到增量存储之前，考虑为新表(从脚本中)创建的估计计划：

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

这项计划的估计费用为771 734个单位：

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

由于这些成本原因，数据更改计划可能会以一种顺序显示行的类型来促进顺序访问。在这种情况下，由于表是分区的，所以影响更加严重。实际上，是非常分区的；您的脚本创建了其中的15000个。对非常分区表的随机更新成本特别高，因为切换分区(行集)的代价也很高。

最后要考虑的主要因素是，上面的简单更新查询(其中' update‘是指任何数据更改操作，包括一个delete)都有资格进行名为“行集共享”的优化，其中相同的内部行集用于扫描和更新表。执行计划仍然显示两个单独的操作符，但是只使用了一个行集。

我之所以提到这一点，是因为能够应用此优化意味着优化器采用的代码路径不考虑显式排序的潜在好处，从而降低随机I/O的成本(如果表是b树)，这是有意义的，因为结构本身是有序的，所以共享行集会自动提供所有潜在的好处。

重要的结果是更新操作符的成本计算逻辑不考虑这种排序好处(促进顺序I/O或其他优化)，其中底层对象是列存储。这是因为没有就地执行列存储修改；它们使用的是增量存储。因此，成本模型反映了b树上的共享行集更新与列存储之间的差异。

不过，在特殊情况下(非常！)分区的列存储可能仍然有保留顺序的好处，因为从I/O的角度来看，在移动到下一个分区之前执行对一个分区的所有更新仍然是有利的。

这里的列存储重用了标准的成本逻辑，因此保留分区排序(虽然不是每个分区内的订单)的计划成本更低。我们可以在测试查询中看到这一点，方法是使用无文档的跟踪标志2332要求对update操作符进行排序输入。这将在更新时将DMLRequestSort属性设置为true，并强制优化器在移动到下一个分区之前生成一个为一个分区提供所有行的计划：

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

这项计划的估计费用要低得多，为52.5174个单位：

费用减少的全部原因是更新时估计的I/O成本较低。引入的Spool没有执行任何有用的功能，只是可以按照DMLRequestSort = true更新的要求(列存储索引的串行扫描不能提供这种保证)按分区顺序保证输出。假脱机本身的成本被认为是相对较低的，特别是与更新时(可能不现实)降低成本相比。

关于是否需要对update操作符进行有序输入的决定是在查询优化过程中很早就做出的。在此决策中使用的启发式方法从未被记录在案，但可以通过尝试和错误来确定。似乎任何增量存储的大小都是对此决定的输入。一旦做出选择，对于查询编译来说，选择是永久的。没有任何USE PLAN提示会成功:计划的目标要么已经命令了更新的输入，要么没有。

还有一种方法可以为这个查询获得一个低成本的计划，而不需要人为地限制基数估计。一个足够低的估计值，以避免Spool，可能会导致DMLRequestSort错误，由于预期的随机I/O，导致非常高的估计计划成本。另一种方法是使用跟踪标志8649 (并行计划)和2332 (DMLRequestSort = true)：

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

这导致了一个计划，该计划使用每个分区的批处理模式并行扫描和保持顺序的(合并)收集流交换：

这取决于您的硬件上分区排序的运行时有效性，这可能是这三种方法中最好的。尽管如此，对于列存储来说，大的修改并不是一个好主意，所以分区切换的想法几乎肯定更好。如果您能够处理长时间的编译和奇怪的计划选择，那么通常使用分区对象--特别是当分区数量很大时。

将许多相对较新的特性结合在一起，尤其是在它们的极限附近，是获得糟糕的执行计划的好方法。随着时间的推移，优化器支持的深度趋于提高，但是使用15,000个列存储分区可能总是意味着您生活在有趣的时代。