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

> Measure index fragmentation and page density in SQL Server, then choose ALTER INDEX REORGANIZE, REBUILD or a statistics update, with online and resumable options.

Source: https://www.epifive.com/blog/sql-server-index-fragmentation-rebuild-reorganize · Published 2026-10-28 · EpiFive

## Key takeaways

- Check both avg_fragmentation_in_percent and avg_page_space_used_in_percent in sys.dm_db_index_physical_stats before deciding to maintain an index.
- Microsoft prefers ALTER INDEX REORGANIZE because it is always online, interruptible and cheaper than a rebuild.
- A rebuild updates statistics with a full scan, and that side effect often explains improvements people attribute to lower fragmentation.
- Do not rely on fixed fragmentation thresholds alone; measure query performance before and after, for example with Query Store.
- Use ONLINE and RESUMABLE rebuild options and WAIT_AT_LOW_PRIORITY to limit blocking and to survive interruptions on large indexes.

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](https://www.epifive.com/blog/sql-server-query-store-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](https://www.epifive.com/blog/sql-server-transaction-log-full-error-9002).

## 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.

```sql
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:

| Column | Meaning |
| --- | --- |
| `avg_fragmentation_in_percent` | Logical fragmentation, the share of out-of-order pages |
| `avg_page_space_used_in_percent` | Average page density |
| `page_count` | Size of the index in pages |

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.

```sql
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

| | `REORGANIZE` | `REBUILD` |
| --- | --- | --- |
| Resource use | Lower; Microsoft's preferred method | Higher |
| Online | Always | Offline by default; `ONLINE = ON` where supported |
| What it fixes (rowstore) | Reorders leaf-level pages and compacts them to the fill factor | Removes fragmentation at all levels and compacts pages to the fill factor |
| Statistics | Not updated | Updated with a full scan in most cases |
| Interruption | Progress made so far is kept; can be stopped and restarted | Lost, unless the rebuild is resumable |
| Extra space | Work pages in the same data file | Space for a second copy of the index |

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

```sql
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;
```

To reorganize every index on one table:

```sql
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

```sql
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
```

With options on all indexes of a table:

```sql
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:

```sql
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`:

```sql
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:

```sql
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](https://www.epifive.com/blog/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

## Frequently asked questions

### Should I rebuild or reorganize a fragmented index?

Microsoft's documentation says reorganizing is less resource intensive and should be your preferred method unless there is a specific reason to rebuild. Reorganize is always online. A rebuild removes fragmentation at every level and updates statistics with a full scan, but costs more CPU, I/O and log.

### What fragmentation percentage should trigger maintenance?

Current Microsoft guidance says decisions should not be based on fixed fragmentation or page density thresholds alone. Measure whether fragmentation correlates with slower queries in your workload, and maintain the specific indexes those queries use.

### Does rebuilding an index update statistics?

Yes. Rebuilding a rowstore index updates its statistics by scanning all rows, equivalent to FULLSCAN, in most cases. Reorganizing does not update statistics. Often the performance gain after a rebuild comes from the statistics update, which you can do on its own at much lower cost.

### Is index maintenance needed in Azure SQL Database?

Only when there is a demonstrated need. Microsoft notes that resource governance, replicas and storage behaviour change the cost-benefit picture, and points to automatic index compaction as a low-overhead alternative. If you do maintain indexes, reorganize or use online, resumable rebuilds.