Index Reorganize vs Index Rebuild in SQL Server

Both reorganizing and rebuilding are index-maintenance operations used to reduce index fragmentation and improve page organization.

The main difference is:

• REORGANIZE gradually rearranges the existing index pages.

• REBUILD creates a new index structure and replaces the old one.

---

What Is Index Fragmentation?

As rows are inserted, updated, and deleted, index pages may become disorganized or partially empty.

Two common forms are:

Logical fragmentation

The logical order of index pages no longer matches their physical page order.

Expected order:  1 → 2 → 3 → 4
Actual order:    1 → 4 → 2 → 3

This can increase page reads, especially during large range scans.

Low page density

Index pages contain too much unused space.

Page 1: 55% full
Page 2: 60% full
Page 3: 50% full

More pages must be read to return the same data.

Fragmentation is commonly caused by:

• Random inserts

• Page splits

• Updates that increase row size

• Frequent deletes

• Inappropriate fill factor

• Frequently changing indexed values

---

1. What Is Index Reorganize?

REORGANIZE is a lightweight operation that rearranges the existing leaf-level index pages into a better logical order.

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REORGANIZE;

All indexes on a table can be reorganized using:

ALTER INDEX ALL
ON dbo.Orders
REORGANIZE;

How Reorganize Works

SQL Server:

1. Uses the existing index structure.

2. Reorders leaf-level pages gradually.

3. Compacts pages where possible.

4. Removes some fragmentation.

5. Does not create a completely new index.

It is an incremental operation, so it generally uses fewer resources at one time than a rebuild.

Important Characteristics

• It is always performed online.

• It works mainly at the leaf level.

• It uses less additional disk space.

• It usually creates smaller, incremental transactions.

• It generates transaction log activity.

• It can usually be paused by cancelling and resumed later without losing all completed work.

• It does not automatically update index statistics.

• It is generally slower than rebuild when fragmentation is very high.

LOB Compaction

By default, reorganizing can compact large-object pages:

ALTER INDEX IX_Articles_Title
ON dbo.Articles
REORGANIZE WITH (LOB_COMPACTION = ON);

LOB data can include columns such as:

• VARCHAR(MAX)

• NVARCHAR(MAX)

• VARBINARY(MAX)

• XML

---

2. What Is Index Rebuild?

REBUILD creates a new index structure and replaces the old fragmented index.

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD;

To rebuild all indexes:

ALTER INDEX ALL
ON dbo.Orders
REBUILD;

How Rebuild Works

Conceptually, SQL Server:

1. Reads the existing index data.

2. Creates a new B-tree.

3. Applies the requested fill factor.

4. Organizes pages into the correct logical order.

5. Replaces the old index with the new index.

Because the index is recreated, rebuilding normally removes fragmentation more completely than reorganizing.

Important Characteristics

• Recreates the complete index structure.

• Handles severe fragmentation more effectively.

• Usually requires more CPU, memory, I/O, log space, and temporary space.

• Can be performed offline or online, depending on SQL Server capabilities and index characteristics.

• May block application activity when performed offline.

• Updates the index statistics using an equivalent of a full scan.

• Can apply a new fill factor.

• Can use parallel processing.

• Usually completes faster than reorganize for heavily fragmented indexes, but has a greater resource impact.

---

Main Differences

Feature	Reorganize	Rebuild
Operation	Rearranges existing leaf pages	Creates a new index structure
Best suited for	Low or moderate fragmentation	High fragmentation or structural refresh
Fragmentation removal	Gradual and less complete	More complete
Online operation	Always online	Online or offline
Blocking	Usually minimal	Can be significant, especially offline
Resource usage	Lower at one time	Higher
Additional disk space	Relatively low	More workspace may be required
Transaction behavior	Smaller incremental transactions	Larger operation and potentially heavy logging
Statistics update	Does not update index statistics	Updates index statistics
Fill factor	Cannot assign a new fill factor	Can assign a new fill factor
Parallelism	Single-threaded	Can use parallelism
Cancellation	Keeps completed reorganization work	Regular rebuild cancellation rolls back the operation
Disabled index	Cannot reorganize	Can rebuild and enable it
Typical duration	Can be slow for high fragmentation	Often faster for heavily fragmented indexes

---

Syntax Examples

Reorganize One Index

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REORGANIZE;

Reorganize All Indexes

ALTER INDEX ALL
ON dbo.Orders
REORGANIZE;

Rebuild One Index

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD;

Rebuild All Indexes

ALTER INDEX ALL
ON dbo.Orders
REBUILD;

Rebuild with Fill Factor

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD
WITH (FILLFACTOR = 90);

This leaves approximately 10% free space on leaf pages when the index is built.

Online Rebuild

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD
WITH (ONLINE = ON);

Online rebuild reduces application blocking, but short-duration locks may still occur at the beginning and end. Availability also depends on the SQL Server version, edition, and index features.

Rebuild Using tempdb

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD
WITH
(
    SORT_IN_TEMPDB = ON,
    ONLINE = ON
);

SORT_IN_TEMPDB moves sorting work to tempdb. It may improve performance when tempdb is properly configured, but it requires sufficient space.

---

Effect on Statistics

Reorganize

Reorganizing an index does not update its statistics.

After reorganizing, statistics may need to be updated separately:

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerId;

Or:

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;

Do not automatically run FULLSCAN for every large table; it can consume considerable resources.

Rebuild

Rebuilding an index updates that index’s statistics using a full-scan-equivalent collection.

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD;

However, rebuilding one index does not update every other statistic on the table, such as unrelated column statistics.

---

Effect on Transaction Log

Both operations generate transaction-log records.

Reorganize

• Uses smaller transactions.

• Usually consumes log space gradually.

• May run for a long time on highly fragmented indexes.

• Can still generate substantial total log activity.

Rebuild

• Can generate heavy log activity over a shorter period.

• Requires adequate transaction-log space.

• Can increase log backups, replication traffic, or availability-group data movement.

Before rebuilding a large index, check:

• Transaction-log free space

• Data-file free space

• tempdb space

• Backup and restore impact

• Availability Group or replication lag

• Maintenance window

---

Locking and Availability

Reorganize

REORGANIZE is online, so users can normally access the table while it runs.

However, it is not completely lock-free. Short locks and blocking may still occur.

Offline Rebuild

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD WITH (ONLINE = OFF);

An offline rebuild can hold strong locks and block reads or writes for a significant period.

Online Rebuild

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD WITH (ONLINE = ON);

The table remains more available, but brief schema locks may still be required.

For supported environments, low-priority lock waiting can reduce disruption:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD
WITH
(
    ONLINE = ON
    (
        WAIT_AT_LOW_PRIORITY
        (
            MAX_DURATION = 5 MINUTES,
            ABORT_AFTER_WAIT = SELF
        )
    )
);

This example allows the rebuild to wait at low priority and cancel itself if it cannot acquire the required lock.

---

Resumable Online Rebuild

Supported SQL Server environments can use a resumable online rebuild:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD
WITH
(
    ONLINE = ON,
    RESUMABLE = ON,
    MAX_DURATION = 30 MINUTES
);

The operation can pause after the specified time rather than requiring one long maintenance window.

Resume it:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
RESUME;

Pause it manually:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
PAUSE;

Abort it:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
ABORT;

This is useful for very large indexes and short maintenance windows.

---

How to Check Index Fragmentation

Use sys.dm_db_index_physical_stats:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.page_count,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
) AS ips
INNER JOIN sys.tables AS t
    ON t.object_id = ips.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
INNER JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE ips.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;

Important values:

• avg_fragmentation_in_percent: logical fragmentation

• page_count: size of the index

• avg_page_space_used_in_percent: page density

Do not make maintenance decisions using fragmentation percentage alone.

---

Common Fragmentation Guidelines

A traditional starting guideline is:

Fragmentation	Possible action
Less than 5%	Usually no action
5%–30%	Consider reorganizing
Greater than 30%	Consider rebuilding

These are guidelines, not mandatory rules.

A 70% fragmented index containing only 20 pages may not need maintenance because it is already small and inexpensive to scan.

A better decision considers:

• Index page count

• Query usage

• Logical reads

• Page density

• Storage type

• Maintenance duration

• Blocking risk

• Transaction-log capacity

• Whether the workload performs range scans

• Whether fragmentation is actually affecting performance

---

Practical Maintenance Example

The following example ignores small indexes and chooses an operation using traditional thresholds:

DECLARE
    @SchemaName SYSNAME,
    @TableName  SYSNAME,
    @IndexName  SYSNAME,
    @Fragmentation FLOAT,
    @Sql NVARCHAR(MAX);

DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
    s.name,
    t.name,
    i.name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
) AS ips
INNER JOIN sys.tables AS t
    ON t.object_id = ips.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
INNER JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE ips.index_id > 0
  AND ips.page_count >= 1000
  AND ips.avg_fragmentation_in_percent >= 10;

OPEN IndexCursor;

FETCH NEXT FROM IndexCursor
INTO @SchemaName, @TableName, @IndexName, @Fragmentation;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Fragmentation < 30
    BEGIN
        SET @Sql =
            N'ALTER INDEX ' + QUOTENAME(@IndexName) +
            N' ON ' + QUOTENAME(@SchemaName) +
            N'.' + QUOTENAME(@TableName) +
            N' REORGANIZE;';
    END
    ELSE
    BEGIN
        SET @Sql =
            N'ALTER INDEX ' + QUOTENAME(@IndexName) +
            N' ON ' + QUOTENAME(@SchemaName) +
            N'.' + QUOTENAME(@TableName) +
            N' REBUILD;';
    END;

    EXEC sys.sp_executesql @Sql;

    FETCH NEXT FROM IndexCursor
    INTO @SchemaName, @TableName, @IndexName, @Fragmentation;
END;

CLOSE IndexCursor;
DEALLOCATE IndexCursor;

This is only a basic learning example. A production process should include:

• Error handling

• Logging

• Time limits

• Online-operation support checks

• Partition awareness

• Availability Group considerations

• Transaction-log monitoring

• Statistics strategy

---

Partition-Level Maintenance

For partitioned tables, maintenance can target a specific partition.

ALTER INDEX IX_Orders_OrderDate
ON dbo.Orders
REBUILD PARTITION = 12;

Or:

ALTER INDEX IX_Orders_OrderDate
ON dbo.Orders
REORGANIZE PARTITION = 12;

This avoids maintaining unaffected partitions and is useful for large historical tables.

---

When Should You Reorganize?

Consider REORGANIZE when:

• Fragmentation is moderate.

• The system must remain online.

• There is limited extra disk space.

• A less resource-intensive operation is preferred.

• The transaction log cannot handle a large rebuild at once.

• The index is not heavily fragmented.

• A gradual operation is acceptable.

Remember that statistics may need to be updated separately.

---

When Should You Rebuild?

Consider REBUILD when:

• Fragmentation is high.

• Page density is poor.

• A new fill factor must be applied.

• Statistics need to be refreshed as part of the operation.

• The index structure requires a complete refresh.

• Reorganizing would take too long.

• Sufficient CPU, disk, memory, log, and maintenance time are available.

• An online or resumable rebuild is supported and appropriate.

---

Example Scenario

Suppose Orders contains 50 million records.

Index A

Page count:     600
Fragmentation:  45%

Although fragmentation is high, the index is small. Maintenance may provide little measurable benefit.

Index B

Page count:      2,000,000
Fragmentation:   18%
Page density:    62%

This index is large, frequently scanned, and has low page density. Maintenance may be beneficial even though fragmentation is below 30%.

This demonstrates why percentage alone is insufficient.

---

Advantages and Disadvantages

Reorganize Advantages

• Always online

• Lower resource usage at one time

• Less additional disk space

• Smaller transactions

• Suitable when blocking must be minimized

• Preserves completed work if interrupted

Reorganize Disadvantages

• Can be slow for severely fragmented indexes

• Does not update statistics

• Cannot assign a new fill factor

• Does not rebuild the complete B-tree

• Runs single-threaded

Rebuild Advantages

• More completely removes fragmentation

• Recreates the entire index

• Updates index statistics

• Supports a new fill factor

• Can use parallelism

• Often faster for heavily fragmented indexes

• Can enable a disabled index

Rebuild Disadvantages

• Higher CPU, I/O, memory, and log usage

• Requires additional working space

• Offline rebuilds can cause significant blocking

• Regular rebuild cancellation requires rollback

• May affect backups, replicas, and transaction-log throughput

---

Common Mistakes

Rebuilding Every Index Every Night

This wastes resources and can generate unnecessary log activity.

Using Only Fragmentation Percentage

Always consider page count, page density, usage, and actual performance.

Reorganizing Without Updating Statistics

REORGANIZE does not refresh statistics automatically.

Assuming Online Rebuild Means No Blocking

Online rebuilds can still acquire locks during certain phases.

Using a Low Fill Factor Everywhere

A lower fill factor leaves more free space but increases index size and logical reads. Use it only when page-split patterns justify it.

Maintaining Unused Indexes

An unused index may be a removal candidate rather than something to rebuild repeatedly.

Ignoring Transaction-Log and tempdb Space

Large rebuilds can fail or affect production workloads when space is insufficient.

---

Interview Key Points

• REORGANIZE rearranges existing leaf pages; REBUILD creates a new index.

• Reorganize is always online and uses smaller transactions.

• Rebuild may be online or offline.

• Rebuild updates index statistics; reorganize does not.

• Rebuild can apply a new fill factor; reorganize cannot.

• Reorganize is single-threaded; rebuild can use parallelism.

• Rebuild requires more CPU, I/O, log space, and working space.

• Traditional 5% and 30% thresholds are starting guidelines, not fixed rules.

• Small indexes usually do not require fragmentation maintenance.

• Check page count, page density, usage, workload, and actual performance.

• Online rebuild can still cause brief blocking.

• Resumable rebuilds are helpful for large indexes and short maintenance windows.