Menu
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).
705
If you have an index maintenance routine that always rebuilds and never considers reorganizing, you should reconsider. It's usually better to reorganize a lightly fragmented index and rebuild a more heavily fragmented index – to save time and resources.
Index reorganization is a process where the SQL Server goes through the existing index and cleans it up. Index rebuild is a heavy-duty process where an index is deleted and then recreated from scratch with an entirely new structure, free from all piled up fragments and empty-space pages.
The option is under the Management node of SQL Server management studio. Right-click on Maintenance Plans and select New Maintenance Plan… Provide the appropriate name of the maintenance plan. Drag and drop the index rebuild task from the maintenance plan toolbox.
An index rebuild simply drops and recreates the index which means that index rebuild will solve both the internal and external fragmentation.
The reorganize and rebuild are different things.
Reorganize: it's a defrag for indexes. Takes the existing index(es) and defragments the existing pages. However if the pages are not in a contiguous manner, they stays like before. Only the content of the pages are changing.
Rebuild: actually it drops the index and rebuilds it from scratch. It means that you will get a completely new index, with defragmented and contiguous pages.
Moreover with rebuild you can change partitioning or file groups, but with reorganize you can defrag not only the whole index, but also only one partition of the index.
The update statistics is automatic on clustered indexes, but not on the non-clustered ones.
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
