sql-serverrebuildmaintenance-planreorganize

Reorganise index vs Rebuild Index in Sql Server Maintenance plan


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


Solution

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

    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