主页 / dba / 问题 / 123993
Accepted
James Lupolt
Asked: 2015-12-18 10:34:14 +0800 CST

eager spool operator 对于从集群列存储中删除是否有用？

  • 772

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

我注意到执行计划中有一个很大的 eager spool operator：

在此处输入图像描述

这完成了以下特征：

  • 删除了 6000 万行
  • 使用 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 CTP 3.1 上对此进行了测试，但我在 2014 SP1 CU3 上看到了相同的行为。

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

这个问题主要是出于对优化器此时行为的好奇，因为我有一个解决方法来解决提示问题的问题（一个大的假脱机填充了 TempDB）。我现在正在通过使用分区切换来删除。

sql-server sql-server-2014

1 个回答

  1. Best Answer

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

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

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

    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个单位：

    DMLRequestSort=真计划

    这种成本的降低完全是由于更新时估计的 I/O 成本较低。引入的 Spool 没有执行任何有用的功能，除了它可以保证按照更新的要求按分区顺序输出DMLRequestSort = true（列存储索引的串行扫描无法提供此保证）。假脱机本身的成本被认为是相对较低的，特别是与更新时成本的降低（可能不切实际）相比。

    关于是否要求更新运算符的有序输入的决定是在查询优化的早期做出的。此决策中使用的启发式方法从未记录在案，但可以通过反复试验来确定。似乎任何增量存储的大小都是此决定的输入。一旦做出选择，该选择对于查询编译是永久性的。任何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);

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

    有序删除

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

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

    • 24

相关问题