asp.net - SQL Server 2005 - 您应该多久重建一次索引？

Question

我最近接手了一个项目，他们有一个 SQL 作业设置，每三个小时运行一次，它重建在 ASP.NET Membership 数据库表中找到的索引。

这似乎相当高，每天重建索引 8 次。我每天大约有 2000 个新用户，总共有大约 200 万注册用户。

对于正确的索引重建计划，您有什么建议？

Answer 1

您的死锁肯定与索引的重建有关。毫无疑问，这些索引不需要那么频繁地重建。如果可以防止索引在重建之前被删除，您至少应该考虑使用 ONLINE 选项。

这是我们使用的指南:

Index should be rebuilt when index fragmentation is greater than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

来源: http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/