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).
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
.
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
is an online operation that defragments leaf pages in a clustered or non-clustered index page by page using little extra working space.
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.
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: