在SQL Server维护计划中重新组织索引vs重建索引 [英] Reorganise index vs Rebuild Index in Sql Server Maintenance plan
问题描述
在改进SQL Server数据库的SSW规则中，有一个完整的数据库维护计划的示例: SSW .在示例中，它们同时运行重组索引"，重建索引"和更新统计信息".这有什么意义吗?我以为Reorganize Index是Rebuild Index的快速但效果较差的版本?并且重建索引还会自动更新统计信息(至少在聚集索引上).
In the SSW rules to better SQL Server Database there is an example of a full database maintenance plan: SSW. In the example they run both a Reorganize Index and then a Rebuild Index and then Update Statistics. Is there any point to this? I thought Reorganize Index was a fast but less effective version of Rebuild Index? and that an index rebuild would also update the statistics automatically (on the clustered index at least).
推荐答案
在同一索引上执行REORGANIZE然后执行REBUILD是没有意义的，因为通过REBUILD.
Doing a REORGANIZE and then a REBUILD on the same indexes is pointless, as any changes by the REORGANIZE would be lost by doing the REBUILD.
更糟糕的是，在SSW的维护计划图中，它首先执行SHRINK，这会破坏索引，这是释放空间的方式的副作用.然后REBUILD在REBUILD操作期间再次为数据库文件分配更多空间作为工作空间.
Worse than that is that in the maintenance plan diagram from SSW, it performs a SHRINK first, which fragments the indexes as a side effect of the way it releases space. Then the REBUILD allocates more space to the database files again as working space during the REBUILD operation.
-
REORGANIZE是一种联机操作，它使用很少的额外工作空间逐页对群集或非群集索引页中的叶子页进行碎片整理.
REORGANIZEis an online operation that defragments leaf pages in a clustered or non-clustered index page by page using little extra working space.
REBUILD在Enterprise版本中是联机操作，在其他版本中是脱机操作，并且再次使用与索引大小一样多的额外工作空间.它创建索引的新副本，然后删除旧索引，从而摆脱了碎片.默认情况下，此操作会重新计算统计信息，但可以将其禁用.
REBUILD is an online operation in Enterprise editions, offline in other editions, and uses as much extra working space again as the index size. It creates a new copy of the index and then drops the old one, thus getting rid of fragmentation. Statistics are recomputed by default as part of this operation, but that can be disabled.
有关更多信息，请参见重组和重建索引.
See Reorganizing and Rebuilding Indexes for more information.
除了使用TRUNCATEONLY选项外，不要使用SHRINK，即使文件再次增大，您也应该认真考虑是否必要:
Don't use SHRINK except with the TRUNCATEONLY option and even then if the file will grow again then you should think hard as to whether it's necessary:
这篇关于在SQL Server维护计划中重新组织索引vs重建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
