优化SQL Server的删除速度

Question

我们有一个庞大的生产数据库，其大小约为300GB。有没有办法提高删除查询的性能？目前删除速度每分钟在1-10k之间，对我们来说非常慢。

- User

---

每分钟只有1000行听起来非常慢。你遇到了阻塞吗？还是选择行的速度也很慢，这可能意味着需要建立索引？ - James Z

可能你需要创建索引来覆盖你的删除条件。 - Ginden

没有足够的细节来提供答案。你要执行哪个查询？涉及的条件列是否有索引（如果有的话）？删除操作上是否有触发器？... - Sébastien Sevrin

你是想一次性删除十亿行数据吗？有可能你在等待不断的自动扩展操作吗？很有可能你正在等待日志活动完成，而不是实际的删除操作。请参考这篇文章，里面有相关信息。 - Aaron Bertrand

你要从那个表中删除多少行？为什么不创建一个临时表来保存所有你不想删除的数据，然后截断实际表，再将临时表的数据转移到表中？请参考我在这里的回答：http://stackoverflow.com/questions/30401723/sql-server-delete-very-large-number-of-rows-optimization/30403333#comment48913871_30403333 - FutbolFan

还有，有外键约束吗？请提供完整的表定义、查询和执行计划。 - Martin Smith

Answer 1

如果您尝试在单个语句中删除大量行，那么很可能是因为等待日志活动。所以您可以： 确保你的日志大小足够，以免增长事件拖慢速度。默认情况下，你的日志可能从1MB开始，增长率为10%。增长事件是昂贵的，如果你删除了10GB的数据，不仅会影响当前性能，还会对未来产生影响（由于对虚拟日志文件的影响）。 如果你要删除整个表，请使用TRUNCATE或DROP/CREATE。 如果你要删除大部分表中的数据，请使用SELECT INTO将要保留的数据放入另一个表中，然后使用TRUNCATE，最后将小部分数据移回原表（或者直接删除旧表，重命名新表，并重新应用约束/权限等）。 首先通过分批删除数据来减少日志记录的影响，而不是一次性删除所有数据。请参考this article。你也可以考虑暂时切换到简单恢复模式，这样你只需要使用CHECKPOINT清除日志，而不需要进行日志备份，但你需要确保及时切换回去并进行新的完全备份以重新初始化日志链。

- Aaron Bertrand

---

+1，对于一篇出色的文章给予赞赏。这篇文章在过去帮助了我，让我们的开发人员理解了删除操作，因为他们总是向我们反映速度慢和日志文件增长的问题。 - KASQLDBA

另外，如果存在不必要的索引，删除它们将会提高删除速度。同样，如果需要删除所有或几乎所有的数据，先删除所有索引，然后再重新创建它们可能会产生良好的影响。 - Tony Hinkle

@Tony删除索引也需要记录（创建索引也是如此），所以这可能只是一个你想要承担这个成本的时间问题。没有经过测试，我不确定在删除场景中是否有巨大的优势（就像插入/更新操作那样），除非你有一些之后不再使用的索引。 - Aaron Bertrand

暂时禁用外键约束能够提高查询吗？ - Lev Z

Answer 2

有一些提示，但你使用的是哪个版本？是企业版吗？无论如何：

1. 如果可以的话，将事务日志移到更快的磁盘上。
2. 分析“where”条件。它是否使用索引来识别要删除的记录？如果没有，能否添加一个索引？
3. 在表上是否有任何可以删除的索引？如果有，删除它们。
4. 这个表是否有外键关联？这可能会严重影响删除操作的速度。
5. 如果你使用的是企业版，并且瓶颈是磁盘IO，那么对行级数据进行压缩可能会有所帮助（或者不会，这取决于你的数据）。
6. 你能否对表进行分区？本地索引和删除分区可能会更快。
7. 通过活动监视器调查瓶颈所在。

当处理大型数据库时，没有一个单一有效的答案，需要添加详细信息。

- user_0

---

对我来说，每个记录删除的平均时间从3秒降到0.00秒，这是由于外键与当前表之间的差异。影响真是难以置信。救了我的一天。 - ahmad molaie

Answer 3

你应该尝试逐块删除它们，可能需要在循环中进行删除，每个删除迭代都是一个独立的事务，然后在每个循环迭代结束时清除日志。 此外，你需要找到要用作删除记录块值的数字。这需要进行彻底的测试，最好先在用户验收测试中测试块值。 关于如何进行操作，建议你参考将大型删除操作分成块。

- KASQLDBA

Answer 4

再添加几点...

1. 尝试检查谓词是否有索引，并查看统计信息。
2. 如果您要删除大量行并且不想使用临时表选项，请选择tablock选项。
3. 查看是否有任何触发器，特别是在删除后的触发器。

要获得更多帮助，请发布您正在使用的查询、表信息以及任何阻塞信息。

- TheGameiswar

Answer 5

删除操作在有递归外键的大表上可能会很慢。 如果是这样的话，找到合适的时间，禁用相关服务，禁用递归外键，执行大规模删除，然后再恢复外键。

- obratim

---

这正是我的情况。禁用约束有一定风险，但删除速度从每秒1行增加到每秒500行。 - Jurion