主页 / dba / 问题 / 86696
Accepted
Craig
Asked: 2014-12-22 00:57:47 +0800 CST

简单的 DELETE，但复杂的执行计划

  • 772

当我运行此删除时：

DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870

...它删除了 39,157 行。它应该很简单，因为它是在 ETLHeaderID 上删除，它是聚集索引和主键。但是（根据执行计划）它似乎达到了 361,190 行并使用了其他索引。该表确实有一个 XML 数据类型的字段（以防影响此 DELETE）。

有什么想法为什么以及如何加快此删除速度？

此处执行计划： http : //sharetext.org/qwDY 此处表架构：http: //sharetext.org/Vl9j

谢谢

sql-server-2008 performance

2 个回答

  1. Best Answer

    该计划的顶层涉及从基表（聚集索引）中删除行，并维护四个非聚集索引。其中两个索引在处理聚集索引删除的同时逐行维护。这些是下面以绿色突出显示的“+2 非聚集索引”。

    对于其他两个非聚集索引，优化器决定最好将这些索引的键保存到 tempdb 工作表（Eager Spool）中，然后播放 spool 两次，按索引键排序以促进顺序访问模式。

    定期索引维护

    最后的操作序列与维护主索引和二级xml索引有关，这些索引未包含在您的 DDL 脚本中：

    XML索引维护

    对此没有什么可做的。非聚集索引和xml索引必须与基表中的数据保持同步。维护此类索引的成本是您在表上创建额外索引时做出的权衡的一部分。

    也就是说，这些xml指数尤其成问题。优化器很难准确评估在这种情况下有多少行符合条件。事实上，它大大高估了xml索引，导致为该查询授予了近 12GB 的内存（尽管在运行时只使用了 28MB）：

    估计的行数

    您可以考虑以较小的批量执行删除，希望减少过多的内存授予的影响。

    您还可以在不使用排序的情况下测试OPTION (QUERYTRACEON 8795)计划的性能。这是一个未记录的跟踪标志，因此您应该只在开发或测试系统上尝试它，而不是在生产中。如果生成的计划更快，您可以捕获计划 XML 并使用它为生产查询创建计划指南。

    • 12

  2. 你走在正确的轨道上——XML 索引是问题所在。显然，有一个主 XML 索引和一个辅助 XML 索引。

    在对基表 (ETLHeaders) 执行 DELETE 时，也必须从该表的每个索引中删除数据。这种开销可能很大，尤其是对于 XML 索引。

    导致长持续时间的索引是辅助 XML 索引 [XML_IX_ETLHeaders_Property]。“关系表”中的 39,157 行是指主 XML 索引 [XML_IX_ETLHeaders] 中的 361,190 行。并且需要对这 361k 行进行排序，以便能够用于删除二级索引。而这种排序操作导致查询的持续时间过长。（附带说明一下，两个 xml 索引的索引统计数据似乎还差得很远：主 xml 索引的 361k 行的实际数据大小为 160MB，而估计的数据大小几乎为 4TB（是的，4 TerraByte ！！）） .

    我认为加快此查询的唯一选择是消除辅助 XML 索引。根据数据，将 XML 数据分解为关系表可能是更好的选择。

    • 3

相关问题