SQL Server Index Fragmentation: When to Rebuild or Reorganize an Index

Rebuild or reorganize a SQL Server index only when measurement shows it helps. Reorganize first because it is lighter and always online, rebuild when you need to remove fragmentation at every level and restore page density, and try a statistics update before either, because the benefit people see after a rebuild often comes from fresh statistics. This guide shows how to measure fragmentation and page density, run the right ALTER INDEX statement, and verify the result.

Details were checked against Microsoft Learn's SQL Server 2025 (version 17) documentation on 28 October 2026. Most of the guidance also applies to Azure SQL Database and Azure SQL Managed Instance, with the extra considerations described later.

Who this is for and what you will have at the end

This is for database administrators and developers who inherited a nightly "rebuild everything" job, or who see fragmentation percentages in a monitoring tool and are unsure whether to act. You do not need to be an expert in index internals.

At the end you will have:

• A query that reports fragmentation and page density for rowstore indexes.
• A way to decide between reorganize, rebuild and a statistics update.
• Tested ALTER INDEX statements, including online, resumable and low-priority-lock variants.
• A method to confirm the change helped, and a short troubleshooting list.

What fragmentation and page density mean

Microsoft's index maintenance article defines two separate problems.

Fragmentation in a rowstore (B-tree) index exists when the logical order of pages, based on the key values, does not match their physical order. Inserts, updates and deletes cause page splits that scatter data over time. Fragmented indexes can hurt queries that read many pages through full or range scans, because they need many small I/O requests instead of a few large ones.

Page density (page fullness) is how full the pages are. A page split divides a full page into two pages that are each about half full. Low density means more pages for the same data, so more I/O and more memory to cache it. The same article adds a tip: in many workloads, increasing page density has a greater positive effect than reducing fragmentation.

A fill factor below 100 deliberately lowers page density. Microsoft does not recommend values other than 100 or 0 except for specific indexes with many page splits, for example those with a leading column of nonsequential GUID values.

Fragmentation matters less on some storage. For most storage types used in Azure SQL Database and Azure SQL Managed Instance, Microsoft says there is no performance difference between sequential and random I/O, which reduces the impact of fragmentation on query performance.

Prerequisites

• Permission to run ALTER INDEX on the table (the ALTER permission on the table or view is the usual requirement; confirm in the ALTER INDEX reference for your version).
• A maintenance window or a plan to run during low activity, because both methods raise CPU, memory and storage I/O use.
• Free space. A rebuild needs room for two copies of the index while it runs, and a reorganize needs space in the data file that holds the index for its temporary work pages.
• Query Store enabled if you want a before-and-after comparison. See Query Store and plan regression for setup.
• A recent backup, and for large indexes a look at transaction log capacity. A full log is a common failure; see error 9002 transaction log full.

Step 1: Measure fragmentation and page density

Run this in the database you want to inspect. It is the sample query from Microsoft's documentation and uses SAMPLED mode to return results quickly. Use DETAILED for more accurate numbers, but it scans all index pages and can take a long time.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') AS ips
     INNER JOIN sys.indexes AS i
         ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
ORDER BY page_count DESC;

Read the columns like this:

Ordering by page_count puts the biggest indexes first. Small indexes are poor candidates. Microsoft notes that rebuilding or reorganizing small rowstore indexes usually does not reduce fragmentation, partly because on SQL Server 2014 and earlier their pages can sit on shared mixed extents.

For columnstore indexes, fragmentation is the ratio of deleted rows to total rows in compressed row groups. Microsoft's documentation gives a longer query using sys.dm_db_column_store_row_group_physical_stats for this. Also review fragmentation and density after large deletes or updates, and for heaps with frequent updates, check periodically to avoid forwarding records.

Step 2: Decide whether maintenance is worth it

Maintenance is resource intensive, and the documentation says the benefit ranges from vitally important to minuscule. It also states that decisions should not rest on fixed fragmentation or page density thresholds alone. Older advice that gives a single percentage cut-off for reorganize versus rebuild is therefore a starting point for investigation, not a rule.

Microsoft's recommended strategy, in practical terms:

1. Do not assume maintenance improves your workload.
2. Measure the effect on query performance. Query Store A/B comparison is suggested for before-and-after testing.
3. If a rebuild helped, try a statistics update instead. Updating statistics costs far less; the page notes it often completes in minutes while rebuilds can take hours.
4. Track fragmentation and density over time and look for a correlation with query performance.
5. Maintain only the indexes used by queries that got slower, rather than every index.
6. Do not assume a fixed schedule. Run maintenance when monitoring shows it is needed, ideally at low usage times, and retest periodically.

The statistics point is the one most often missed. An index rebuild updates statistics on the key columns by scanning all rows, equivalent to UPDATE STATISTICS ... WITH FULLSCAN. Updated statistics cause plans to recompile, and a better plan can follow. Microsoft states that people often attribute this gain to reduced fragmentation when the same gain is available by updating statistics.

UPDATE STATISTICS HumanResources.Employee WITH FULLSCAN;

Run it against a test copy first and compare query durations. Use FULLSCAN when default sampling is not giving good estimates, otherwise the default sampling is cheaper.

Reorganize versus rebuild

Two details from the documentation are worth remembering. A reorganize cannot run when ALLOW_PAGE_LOCKS is OFF for the index. And an index cannot be reorganized or rebuilt if its filegroup is offline or read-only; if you use ALL and any index is on such a filegroup, the whole statement fails.

Step 3: Run the maintenance

Reorganize

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

To reorganize every index on one table:

ALTER INDEX ALL
    ON HumanResources.Employee REORGANIZE;

Because reorganize does not update statistics, schedule a statistics update afterwards if you need one.

In SQL Server Management Studio, expand the database, then Tables, the table and Indexes. Right-click the index and choose Reorganize, confirm it in the Reorganize Indexes dialog and select OK. The Compact large object column data check box includes LOB pages.

Rebuild

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;

With options on all indexes of a table:

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

That example comes from Microsoft's documentation. Do not copy FILLFACTOR = 80 blindly: it lowers page density by design, and Microsoft recommends 100 or 0 for most indexes. STATISTICS_NORECOMPUTE = ON also turns off automatic statistics updates for the index, so only use it if you manage statistics yourself.

Online, low-priority and resumable rebuilds

An offline rebuild is usually faster but holds object-level locks that block queries. An online rebuild avoids long object-level locks and takes a short lock at the end. Online operations make data modifications update an additional copy of the index, which can slow writes slightly.

The ALTER INDEX reference documents these patterns:

ALTER INDEX test_idx ON test_table
REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);
 
ALTER INDEX test_idx ON test_table
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

Key points:

• RESUMABLE = ON requires ONLINE = ON, and it cannot be used with the ALL keyword.
• A partitioned or resumable rebuild does not scan all rows for statistics; it uses the default sampling ratio instead.
• MAX_DURATION with a resumable operation pauses it after that many minutes. It must be greater than 0 and no more than 10,080 minutes (one week).
• Pause with ALTER INDEX ... PAUSE, continue with RESUME or by re-running the original statement, and cancel with ABORT. An aborted operation cannot be resumed.
• Do not leave a resumable operation paused indefinitely. A paused build still needs disk space for both copies, and DML keeps updating the in-build index. If you do not intend to finish it, abort it.
• The RESUMABLE option is not stored in the index metadata. You must specify it on each statement.
• A resumable operation cannot run inside an explicit transaction.

To limit blocking when an online operation starts or finishes, add WAIT_AT_LOW_PRIORITY:

ALTER INDEX test_idx ON test_table
REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));

With ABORT_AFTER_WAIT = SELF the index operation gives up after the wait; BLOCKERS instead kills the sessions blocking it, so use that only when you accept the consequences; it also requires the ALTER ANY CONNECTION permission. SELF cannot be used when the wait MAX_DURATION is 0. Raising MAXDOP can shorten a rebuild at the cost of more CPU.

Columnstore indexes

For columnstore indexes the picture differs. Reorganizing forces closed delta-store row groups into compressed row groups, merges small compressed ones and physically removes deleted rows. From SQL Server 2016, REORGANIZE performs most of what a rebuild does, online, so a rebuild is usually not needed. From SQL Server 2019, a background merge task also compresses and merges row groups, which removes the need for manual reorganize in most cases. To force all row groups, including open ones, into columnstore:

ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

SQL Server Management Studio's Reorganize does not compress open row groups, so use the T-SQL form when you need that.

Azure SQL Database and Managed Instance

Microsoft advises performing index maintenance in Azure SQL only when there is a demonstrated need. Maintenance competes with your workload for the CPU, memory and I/O that your service tier allows, a rebuild can slow other work through transaction log I/O, and in elastic pools it can affect other databases. Replicas can lag, and a long query on a readable replica can be terminated during a rebuild on the primary.

If you do need it, reorganize or use online rebuilds, and make them resumable so a failover does not restart them. The article also names two uncommon valid reasons: raising page density to stay within a tier's size limit, and compacting before shrinking files. It points to automatic index compaction as a low-overhead alternative. See the SQL Server 2025 upgrade checklist if you are planning a version move.

Step 4: Verify the result

After maintenance, rerun the measurement query for the affected indexes and confirm that avg_fragmentation_in_percent fell or avg_page_space_used_in_percent rose. Then compare query performance with Query Store for the queries that motivated the work. If fragmentation improved but queries did not, the index was not the problem, and you can stop repeating the maintenance.

To check an index's fill factor, look at sys.indexes.

Troubleshooting

• Fragmentation stays high on a small index. Expected for small indexes; do not repeat the maintenance.
• Error 1105, filegroup full, during a reorganize. The data file that contains the index is out of space for temporary work pages, even if the filegroup has free space elsewhere. Add space or autogrowth to that file.
• The statement fails with ALL. An index on an offline or read-only filegroup makes the whole statement fail. Run the statement per index instead.
• A reorganize is refused. Check whether ALLOW_PAGE_LOCKS is OFF for that index.
• Rebuild blocks users. Use ONLINE = ON with WAIT_AT_LOW_PRIORITY, or move the work to a quieter window.
• Log grows quickly or a replica lags. Index maintenance generates a lot of log. Use smaller batches of indexes, make the work resumable, and watch replicas.
• An online rebuild does not repair a corrupt nonclustered index. Microsoft notes an online rebuild carries the inconsistency over, so rebuild offline or drop and recreate the index, and run DBCC CHECKDB.

Checklist

• Measure fragmentation and page density, ignoring small indexes.
• Try a statistics update first and compare performance.
• Prefer REORGANIZE; rebuild only for a specific reason.
• Use online, resumable and low-priority options on busy systems, and keep FILLFACTOR at 100 or 0 unless page splits justify otherwise.
• Check free space and log capacity before starting.
• Verify with the same query and with Query Store.
• In Azure SQL, maintain indexes only when there is a demonstrated need.

References

• Maintain indexes optimally to improve performance and reduce resource utilization: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver17
• ALTER INDEX (Transact-SQL): https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17