SQL Server 2005 - 应该多久重建一次索引？
我最近接手了一个项目，他们有一个 SQL 作业设置，每三个小时运行一次，它会重建 ASP.NET 会员数据库表中找到的索引。
每天重建索引 8 次，这看起来相当高。我每天都会获得大约 2000 个新用户，总共大约有 200 万注册用户。
对于正确的索引重建计划，您有何建议？
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.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您的死锁肯定与重建索引有关。毫无疑问，这些索引不需要那么频繁地重建。如果可以防止索引在重建之前被删除，那么您至少应该考虑使用 ONLINE 选项。
这是我们使用的指南：
来源：http://blog.sqlauthority.com/2007/12/22/sql-server-difference- Between-index-rebuild-and-index-reorganize-explained-with-t-sql-脚本/
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:
Source: http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/
一个好的经验法则是，当超过 30% 的碎片时进行重建，当碎片在 10% 到 30% 之间时进行重组。
对于少于 1000 页的表，您不会注意到，不必理会其中任何一个，即使在对超过 30% 的表运行 REBUILD 后，它通常也会保留在 30%。
您可能应该以相当少的频率重建/重组，对于普通数据库来说最多每周一次。如果您必须更频繁地对索引进行碎片整理，那么您可能需要重新查看填充因子和填充。
一个例外是在批量数据加载之后，索引碎片可能很常见（有时最好禁用索引或删除索引并重建它们，具体取决于正在加载的数据）。
所以综上所述，一天8次确实显得有些过分了。
参考资料：
http://technet.microsoft.com/en-us/library/ms189858.aspx
http://www.sqlmusings。 com/2009/03/15/a-more-efficient-selective-index-rebuildreorganize-strategy/
http://realworlddba.wordpress.com/2008/ 01/27/重建或重组索引/
http://realworlddba.wordpress.com/2008/ 01/27/要重建或重组的索引/
A good rule of thumb is REBUILD when over 30% fragmented, REORGANIZE when between 10% and 30%.
Don't bother with either for tables less than 1000 pages, you won't notice, and even after running a REBUILD for one that is over 30% it will often be left at 30%.
You should probably be aiming to rebuild/reorganize fairly infrequently, weekly at most for an average database. If you're having to defrag the indexes more often than that then you probably need to re-look at your fill factors and padding.
An exception is after bulk data loading, where it might be common to have fragmented the indexes (sometimes its better to disable the index or to drop the indexes and rebuild or them depending upon the data being loaded).
So in summary, 8 times a day does seem excessive.
References:
http://technet.microsoft.com/en-us/library/ms189858.aspx
http://www.sqlmusings.com/2009/03/15/a-more-effective-selective-index-rebuildreorganize-strategy/
http://realworlddba.wordpress.com/2008/01/27/indexes-to-rebuild-or-reorganize/
http://realworlddba.wordpress.com/2008/01/27/indexes-to-rebuild-or-reorganize/
捕获死锁图，您就可以得到什么是死锁的实际答案，而不是猜测。鉴于死锁是（或至少应该是）相当罕见的情况（低于 10/秒），您可以相当安全地长时间附加探查器并仅捕获 锁定/死锁图 事件。
Capture the deadlock graph and you have an actual answer of what is deadlocking, as opposed to a guess. Given that deadlock are (or at least should be) a fairly rare occurence (under 10/second) you can pretty safely attach profiler over a long time and capture only the Locks/Deadlock Graph event.
重建是否会损害系统稳定性或占用太多系统时间？
如果您回答“否”，请不要碰它:)
Does the rebuilding hurt the system stability or takes too much system time?
If you answer no - don't touch it :)