DIFFERENCE BETWEEN REBUILDING AND REORGANIZING INDEXES

1. DIFFERENCE BETWEEN REBUILDING AND REORGANIZING INDEXES You can remedy index fragmentation by reorganizing or rebuilding an index. It is prudent to know the differences between the two: Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. Rebuilding of indexes is mainly an offline operation (will not skip pages), but reorganizing an index is mainly an online operation (may skip pages) Stopping of rebuilding will cause a rollback and loss of work. Stopping of reorganizing will not cause any loss of work Rebuilding an index updates the index statistics (but not column statistics). However, reorganizing neither updates index statistics nor updates column statistics (which is why you should always run sp_updatestats after rebuilding/reorganizing your indexes) Rebuilding requires additional free space with the data files, reorganizing does not Rebuilding can make use of parallelism according to your MAXDOP setting. Reorganizing cannot make use of parallelism Rebuilding usually runs faster for indexes with high level of logical fragmentation. However, reorganizing is usually faster for indexes with low levels of fragmentation Rebuilding usually runs much faster than reorganizing on very large indexes To read more, please visit Satish Kartan's blog where he has shared much important information! Also read: SAN VS DAS –WHAT’S GOOD FOR SQLby Satish Kartan