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).
+8
A:
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-clutered ones.
Biri
2008-08-11 07:44:54
+3
A:
Exactly what Biri said. Here is how I would reindex an entire database:
EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"
GateKiller
2008-08-11 09:17:41
+5
A:
When doing a reorg of an index, if the index is spread across two or more physical files the data will only be defragged within the data file. Pages are not moved from one data file to another.
When the index is in a single file the reorg and reindex will have the same end result.
Some times the reorg will be faster, and some times the reindex will be faster depending on how fragmented the index is. The less fragmented the index then a reorg will be faster, the more fragmented the slower the reorg will be, but the faster a reindex will be.
mrdenny
2008-09-03 06:36:13