SQLServer - Difference between Index Rebuild and Index Reorganize

Index Rebuild

1.  Index Rebuild works by process of recreating the index which internally drops and recreates the index. It removes fragmentation, reclaims the disk space by compacting pages based on the specified fill-factor setting and re-orders the index rows in contiguous pages.

2.    Statistics are re-computed during the index rebuild process as new index is created.

3.    It requires free space to perform this operation. And it uses more CPU and locks databases resources.

4.  Index rebuild can’t perform online if it contains LOB (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns) data.

5.    If an index has multiple partitions, then we can’t rebuild single index partition online. And if we want to rebuild the index with multiple partitions online, then we will have to rebuild the index with all the partitions.

6.   Example:

USE Test;

GO

-- Rebuild all indexes on the Test.Employee_ARCHITECT_List table.

ALTER INDEX ALL ON Test..Employee_ARCHITECT_List

REBUILD ;

GO

Index Reorganize

1.   Index Reorganize works by process of physically reorganizing the leaf nodes of the index. It does reorganize in two phases that are compaction and defrag. It can remove almost all of the logical fragmentation but it can’t necessarily fix extent fragmentation.

2.    Statistics are not updated as part of the Index reorganize.

3.   It does not require any free space to perform this operation as it just swaps one page with another. It uses minimal system resources.

4. ALTER INDEX..REORGANIZE has a feature called Large Object Compaction (LOB_COMPACTION) and this is the online operation.

5.    If an index has multiple partitions, then we can reorganize the single index partition online.

6.   Example:

USE Test;

GO

-- Reorganize all indexes on the Test.Employee_ARCHITECT_List table.

ALTER INDEX ALL ON Test..Employee_ARCHITECT_List

REORGANIZE ;

GO

Points to be noted

sys.dm_db_index_physical_stats[column_Name: Avg_fragmentation_in_percent] is used to identify the degree of fragmentation values.

If avg_fragmentation_in_percent value > 5 and <=30,

Then ALTER INDEX REORGANIZE.

If avg_fragmentation_in_percent value > 30,

Then ALTER INDEX REBUILD.