Reorganize and Rebuild Indexes

This document describes how to reorganize and rebuild indexes in SQL Server. Indexes are critical for query performance. Over time, due to data modifications (INSERT, UPDATE, DELETE operations), indexes can become fragmented. Fragmentation can lead to performance degradation as the query optimizer may have to scan more pages than necessary. Reorganizing and rebuilding are maintenance tasks designed to address this fragmentation.

Understanding Index Fragmentation

Index fragmentation occurs when the logical order of pages in an index does not match the physical order of pages on disk. There are two types of fragmentation:

• Internal Fragmentation: Occurs when there are empty spaces within index pages. This is common when rows are updated, causing page splits.
• External Fragmentation: Occurs when pages belonging to the same index level are not stored contiguously on disk.

SQL Server provides system catalog views to assess the level of fragmentation. The primary view for this is sys.dm_db_index_physical_stats.

SELECT
    object_id,
    index_id,
    avg_fragmentation_in_percent,
    fragment_count,
    page_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE
    avg_fragmentation_in_percent > 30;

Reorganizing Indexes

The ALTER INDEX ... REORGANIZE command defragments the leaf level of a clustered index or a heap. It also consolidates the pages of a nonclustered index into a contiguous block. Reorganization is an online operation, meaning it does not block user activity during the process. It is generally less resource-intensive than rebuilding.

When to Use REORGANIZE

• When the average fragmentation is between 5% and 30%.
• For online maintenance, minimizing downtime.

Syntax

-- Reorganize a specific index
ALTER INDEX IX_MyTable_MyColumn ON dbo.MyTable REORGANIZE;

-- Reorganize all indexes on a table
ALTER INDEX ALL ON dbo.MyTable REORGANIZE;

-- Reorganize a heap
ALTER INDEX ALL ON dbo.MyHeap REORGANIZE;

Rebuilding Indexes

The ALTER INDEX ... REBUILD command reconstructs the entire index. This process removes fragmentation, updates index statistics, and can also reclaim unused space. Rebuilding an index can be an offline or online operation depending on the edition of SQL Server and the type of index.

When to Use REBUILD

• When the average fragmentation is greater than 30%.
• To update statistics (if not done separately).
• To reclaim unused space (especially after large deletions).
• For offline maintenance if online is not available or required.

Syntax

-- Rebuild a specific index (offline, requires Enterprise Edition for online)
ALTER INDEX IX_MyTable_MyColumn ON dbo.MyTable REBUILD;

-- Rebuild a specific index online (SQL Server 2005+ Enterprise Edition)
ALTER INDEX IX_MyTable_MyColumn ON dbo.MyTable REBUILD WITH (ONLINE = ON);

-- Rebuild a clustered index, moving to a new filegroup
ALTER INDEX PK_MyTable ON dbo.MyTable REBUILD PARTITION = ALL
WITH (DROP_EXISTING = ON, ONLINE = ON, SORT_IN_TEMPDB = ON,
      DATA_COMPRESSION = PAGE ON PARTITIONS(1, 2, 3));

-- Rebuild all indexes on a table (offline)
ALTER INDEX ALL ON dbo.MyTable REBUILD;

Choosing Between Reorganize and Rebuild

The choice between reorganizing and rebuilding depends on the level of fragmentation and your maintenance window.

• Low Fragmentation (0-5%): No action needed.
• Moderate Fragmentation (5-30%): REORGANIZE is usually sufficient and less disruptive.
• High Fragmentation (30%+): REBUILD is generally recommended for better results and may be necessary to address fragmentation effectively.

Automating Index Maintenance

Manually performing these tasks can be tedious. It's common practice to automate index maintenance using SQL Server Agent jobs. There are many community scripts available that provide robust solutions for automated index maintenance, often based on dynamic management views to determine which indexes need attention and what action to take.