SQL Server 2005中 - 你应该多久重建索引？ [英] SQL Server 2005 - How often should you rebuild Indexes?

问题描述

最近，我接手的一个项目，他们有一个SQL作业设置运行每三小时，这重建ASP.NET成员数据库表中发现的索引。

I recently took over a project and they have a SQL job setup to run every three hours which rebuilds the indexes found within the ASP.NET Membership database tables.

这似乎是pretty高，要重建索引，每天8次。我得到每天约2000新用户，总计约200万注册用户。

This seems pretty high, to rebuild indexes 8 times a day. I get about 2000 new users every day, and a total of about 2 million registered users.

什么你会推荐一个合适的索引重建时间表？

What would you recommend for a proper index rebuilding schedule?

推荐答案

您死锁绝对可以与索引的重建。也有毫无疑问，这些索引不需要频繁重建那个。至少尽管你应该考虑使用ONLINE选项，如果你可以从它的重建之前被丢弃保证指数。

Your deadlocks can definitely be related to the rebuilding of the indexes. There is also no doubt that those indexes don't need to be rebuilt that frequently. At a minimum though you should consider using the ONLINE option if you can to keep the index from being dropped before it's rebuilt.

下面是我们使用的指导原则：

Here's a guideline we use:

指数应该被重建时指数
  碎片是大于40％。指数
  应在重组指数
  碎片为10％到40％之间通过。
  索引重建过程中使用更多的CPU
  它锁定的数据库资源。
  SQL Server开发版和
  企业版有选择ONLINE，
  可接通时指数是
  重建。 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.

来源：<一个href=\"http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/\">http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/

这篇关于SQL Server 2005中 - 你应该多久重建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！