且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

SQL Server 2005中 - 你应该多久重建索引?

更新时间:2023-02-06 12:52:57

您死锁绝对可以与索引的重建。也有毫无疑问,这些索引不需要频繁重建那个。至少尽管你应该考虑使用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.

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