About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
Some SQL Server systems carry a nightly job that rebuilds every index. It takes processor time, disk and transaction log space, and on a hosted server it competes with backups and other nightly work. Microsoft's current guidance questions whether that job is needed at all, and suggests measuring first.
This article summarises that guidance and how to apply it to a hosted business application.
Why it matters
Index maintenance is expensive. Microsoft states that it causes a significant increase in CPU utilization, memory used, and storage I/O. On a hosted VM with a fixed allocation, that load can slow other work, lengthen backup windows, and generate more log for any replica or backup chain to carry.
Done for the wrong reason, it uses those resources for little benefit. Microsoft says the benefits range from vitally important to minuscule.
How it works
Fragmentation and page density
Two measures describe the state of an index. Fragmentation is when the logical order of pages does not match their physical order. Page density is how full each page is. Low density means more pages to read and cache for the same data.
Microsoft's guidance gives page density weight: In many workloads, increasing page density results in a greater positive performance impact than reducing fragmentation.
No fixed thresholds
Older advice and maintenance scripts can use fixed percentages for when to reorganise or rebuild. Microsoft's current page states that maintenance decisions shouldn't be based on fixed fragmentation or page density thresholds alone. Query patterns, storage behaviour, available memory and engine changes also count.
The rebuild's hidden benefit is statistics
Statistics describe how values are distributed in a column, and the query optimiser uses them to choose plans. A normal nonpartitioned, nonresumable rowstore index rebuild updates that index's statistics by scanning all rows. Partitioned and resumable index builds/rebuilds are exceptions: statistics use the default sampling ratio or a persisted sample percentage where applicable. Reorganising an index does not update its statistics. These differences matter when comparing maintenance results.
Microsoft explains that improvements seen after a rebuild can come from this rather than from less fragmentation, and that the same benefit can often be achieved at a much lower resource cost by updating statistics instead of rebuilding indexes. Microsoft notes a statistics update can complete in minutes where a rebuild can take hours.
Automatic statistics updates
With the automatic update statistics option on, SQL Server marks statistics out of date after a number of row changes and updates them when a query next needs them. Microsoft advises keeping it on: Setting AUTO_UPDATE_STATISTICS to OFF can cause suboptimal query plans and degraded query performance.
The threshold depends on the database compatibility level. From SQL Server 2016 at compatibility level 130 or later, the threshold for large tables shrinks as the table grows. Microsoft's example: a 2-million-row table has its statistics updated after about 44,721 changes, rather than 400,500 under the older rule. Below compatibility level 130, the older threshold is the default, but an existing trace flag 2371 can enable the decreasing dynamic threshold at level 120 or lower. Inspect both compatibility level and actual trace-flag configuration before diagnosing an inherited database. This is a check, not an instruction to enable the flag; any change needs vendor agreement and testing.
Reorganise or rebuild
Microsoft describes reorganising as less resource-intensive and says it should be the preferred method unless there is a specific reason to rebuild. Reorganising is always online.
A rebuild can be offline or online. Microsoft notes an offline rebuild holds locks that block queries for its duration. Online rebuilds are an Enterprise edition feature in SQL Server 2022 and 2025; on Standard edition, a rebuild of a busy table blocks users while it runs.
What to choose and why
Microsoft's recommended strategy, summarised:
- Do not assume maintenance helps. Microsoft says: Don't assume that index maintenance always noticeably improves your workload.
- Measure before and after. Microsoft suggests Query Store to compare query performance before and after maintenance.
- Try statistics first. If a rebuild seems to help, try a statistics update instead and see whether it gives the same result.
- Watch trends. Track fragmentation and page density alongside query performance over time, and maintain only the indexes whose degradation affects queries.
- Run maintenance as needed, not by fixed habit, and at low-usage times when it is needed.
- Test again periodically, because usage changes.
What to record when measuring
A measurement is only useful if the same things are recorded before and after each change. For each trial, record:
- the duration and resource use of the application's key queries and reports, from Query Store;
- the fragmentation and page density of the indexes those queries use;
- how long the maintenance itself took and how much transaction log it generated;
- whether backups, replicas or other nightly work ran late.
Run the same workload for each comparison. A trial during a quiet week compared with one during month-end tells you about the calendar, not about maintenance.
For a hosted business application, this can mean replacing a nightly rebuild-everything job with a regular statistics update plus targeted index work when measurements show a need. That is a decision to make from your own measurements.
Risks and limits
- Change maintenance jobs on a test copy first. Use an isolated test server with synthetic or masked data, and keep the old job's definition so it can be restored.
- Application vendors may set their own requirements. Some vendors specify maintenance for their databases; check before changing.
- Maintenance affects replicas and backups. Rebuilds generate transaction log; replicas can fall behind and log backups grow.
- Standard edition blocks during rebuilds. Schedule rebuilds on Standard edition for times when users can tolerate blocking.
- Heaps and columnstore indexes have their own rules. This article covers ordinary rowstore indexes.
- No commands are given here. Microsoft's pages include examples.
Sources
Opened 14 November 2026.
- Microsoft Learn: Optimize index maintenance to improve query performance and reduce resource consumption, source updated 2026-10-27.
- Microsoft Learn: Statistics, source updated 2026-10-05.
- Microsoft Learn: Editions and supported features of SQL Server 2025, source updated 2026-07-20.
- Microsoft Learn: Editions and supported features of SQL Server 2022, source updated 2026-07-20.
Related service: Managed support.
Related future topics
- Reporting load on SQL Server.
- SQL Server editions for hosted applications.
- SQL Server compatibility level and query baselines after an upgrade.
Learning notes
- Index maintenance is expensive; Microsoft says to measure its benefit, not assume it.
- Fixed fragmentation thresholds alone should not drive maintenance.
- Part or all of a rebuild's benefit can come from updated statistics, which cost much less to refresh.
- Keep automatic statistics updates on; inspect compatibility level and any existing trace flag 2371. Reorganise does not refresh statistics.
- On Standard edition, rebuilds are offline and block users.
This article was originally published on .