sql server delete通过索引大大减慢了速度 [英] sql server delete slowed drastically by indexes

问题描述

我正在运行一个归档脚本，该脚本会根据大型行(〜50m记录数据库)的输入日期删除它们.日期字段是表上的聚集索引，因此是我将条件语句应用到的内容.

I am running an archive script which deletes rows from a large (~50m record DB) based on the date they were entered. The date field is the clustered index on the table, and thus what I'm applying my conditional statement to.

我正在while循环中运行此删除，批量尝试从1000到100,000条记录中的任何一个.不管批处理大小如何，它的速度都令人惊讶地慢；大约有10,000条记录在一分钟内被删除.查看执行计划，索引删除"花费了大量时间.该表中大约有15个字段，其中大约10个字段具有某种索引.有什么办法可以解决这个问题?我什至不知道为什么要花这么长时间删除每个索引，有人可以确切地了解这里发生了什么吗?这是我的执行计划的一个示例:

I am running this delete in a while loop, trying anything from 1000 to 100,000 records in a batch. Regardless of batch size, it is surprisingly slow; something like 10,000 records getting deleted a minute. Looking at the execution plan, there is a lot of time spent on "Index Delete"s. There are about 15 fields in the table, and roughly 10 of them have some sort of index on them. Is there any way to get around this issue? I'm not even sure why it takes so long to do each index delete, can someone shed some light on exactly whats happening here? This is a sample of my execution plan:

替代文本http://img94.imageshack.us/img94/1006/indexdelete .png

(序列指向Delete命令)

(The Sequence points to the Delete command)

该数据库是活动的，并且经常被插入，这就是为什么我犹豫使用复制和截断方法来调整大小的原因.我在这里还有其他选择吗?

This database is live and is getting inserted into often, which is why I'm hesitant to use the copy and truncate method of trimming the size. Is there any other options I'm missing here?

推荐答案

从聚簇索引中删除10k记录+ 5个非聚簇索引应该完全不需要1分钟.听起来您的IO子系统非常慢.的值是什么?

Deleting 10k records from a clustered index + 5 non clustered ones should definetely not take 1 minute. Sounds like you have a really really slow IO subsytem. What are the values for:

• 平均磁盘秒/写
• 平均磁盘秒/读
• 平均磁盘写队列长度
• 平均磁盘读取队列长度

与该操作有关的每个驱动器上(包括对数驱动器！).如果将索引放在单独的文件组中，并将每个文件组分配给自己的LUN或自己的磁盘，则可以确定哪些索引存在更多问题.同样，日志刷新可能是主要瓶颈. SQL Server在这里没有太多控制权，如何控制一切都掌握在自己手中.这样的时间不会花费在CPU周期上，而是花费在等待IO完成上，并且您需要针对所需的负载对IO子系统进行校准.

On each drive involved in the operation (including the Log ones!). If you placed indexes in separate filegroups and allocated each filegroup to its own LUN or own disk, then you can identify which indexes are more problematic. Also, the log flush may be a major bottleneck. SQL Server doesn't have much control here, is all in your own hands how to speed things up. that time is not spent in CPU cycles, is spent waiting for IO to complete and you need an IO subsystem calibrated for the load you demand.

要减少IO负载，您应该考虑使索引变窄.首先，请确保聚集索引是最窄的可行索引.然后，确保非聚集索引中不包含大量未使用的粗大列(我见过...).通过启用页面压缩，可能会获得重大收益.最后，在 sys.dm_db_index_usage_stats 中检查索引使用情况统计信息指数对斧头有益.

To reduce the IO load you should look into making indexes narrower. Primarily, make sure the clustered index is the narrowest possible that works. Then, make sure the nonclustered indexes don't include sporious unused large columns (I've seen that...). A major gain may be had by enabling page compression. And ultimately, inspect index usage stats in sys.dm_db_index_usage_stats and see if any index is good for the axe.

如果不能减少太多IO负载，则应尝试将其拆分.将文件组添加到数据库，将大索引移到单独的文件组上，然后将文件组放在单独的IO路径(主轴不同)上.

If you can't reduce the IO load much, you should try to split it. Add filegroups to the database, move large indexes on separate filegroups, place the filegroups on separate IO paths (distinct spindles).

对于将来的常规删除操作，最好的选择是使用分区切换，使所有索引与聚集索引分区对齐，并且在适当的时候，只需删除最后一个分区即可实现闪电般的快速删除.

For future regular delete operations, the best alternative is to use partition switching, have all indexes aligned with the clustered index partitioning and when the time is due, just drop the last partition for a lightning fast deletion.

这篇关于sql server delete通过索引大大减慢了速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！