Reorganise index vs Rebuild Index in Sql Server Maintenance plan

Question

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).

question from:https://stackoverflow.com/questions/7579/reorganise-index-vs-rebuild-index-in-sql-server-maintenance-plan

Answer 1

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:

sqlservercentral_SHRINKFILE