Microsoft's announcement of Automatic Index Compaction is titled 'Stop defragmenting and start living'. That is not an accident. Brent Ozar has been making the case for years that defragmenting indexes is largely maintenance theater — that external fragmentation barely matters on modern SSDs and shared storage and that nightly rebuild jobs hammer your transaction log and I/O for gains that are difficult to measure.
His sessions on the topic have been circulating for over a decade, and now Microsoft's own documentation states it plainly: 'For most workloads, a higher index fragmentation doesn't affect query performance or resource consumption.' I believe that may be Brent's argument almost verbatim in their official docs.
What Microsoft is shipping instead is Automatic Index Compaction — a continuous, low-overhead background process that keeps page density high as your data changes, without a scheduled job, without a maintenance window, and without the collateral damage of a full rebuild. Here is what it actually is, how it works, and what the honest limitations are.
What It Does
Index bloat happens because pages get partially empty as data changes — deletes leave gaps, updates move rows, and over time you're reading twice the pages for the same data. Automatic Index Compaction attacks that continuously in the background. It piggybacks on the PVS cleaner — or the persistent version store, which is the same process that removes obsolete row versions after DML. As it visits recently modified pages, it checks whether rows from the pages can be consolidated onto the current page. If that move frees at least one full page, it makes the move and deallocates the empty page. Fewer pages, higher page density, less I/O to read the same data.
The critical distinction from REORGANIZE and REBUILD is the scope. REORGANIZE and REBUILD process every page in an index. Automatic compaction only touches pages that have been recently modified. That is what makes the overhead minimal rather than punishing.
How to Enable It
One command, per database, no restart required. Compaction starts or stops within minutes:
-- Enable ALTER DATABASE [YourDatabase] SET AUTOMATIC_INDEX_COMPACTION = ON; -- Disable ALTER DATABASE [YourDatabase] SET AUTOMATIC_INDEX_COMPACTION = OFF;
How It Compares to What You're Already Doing
| Consideration | REORGANIZE / REBUILD | Auto Compaction |
|---|---|---|
| Scope | All pages in the index | Recently modified pages only |
| Overhead | High — hammers I/O and log during maintenance window | Minimal — runs continuously in background |
| Reduces fragmentation | Yes | No — improves page density, not fragmentation |
| Updates statistics | REBUILD does; REORGANIZE does not | No |
| Requires scheduled job | Yes | No |
| Space in data files required | REBUILD needs free space equal to index size | None |
| Blocking risk | REBUILD can block; REORGANIZE is always online | Short-term millisecond locking only, rare |
Note on fragmentation vs. page density. There are two different metrics in sys.dm_db_index_physical_stats: avg_fragmentation_in_percent and avg_page_space_used_in_percent. Compaction targets page density, not fragmentation. You may actually see fragmentation numbers go up when compaction is running. Microsoft says this is expected and, for most workloads, not a concern.
The Gotchas
It is not available for on-prem SQL Server 2025 yet.
Don't get too excited. The Microsoft Learn documentation for SQL Server 2025 index maintenance references automatic compaction as an alternative — but the feature itself currently applies only to Azure SQL Database, Azure SQL Managed Instance (with the Always-up-to-date update policy) and SQL database in Fabric. On-prem SQL Server 2025 is not in the 'Applies to' list. Watch this one. It is clearly on its way, but it is not here yet.
It maintains compactness better than it remediates bloat.
This is the limitation Jeff Moden surfaced in Brent's comment thread when the announcement dropped, and Dimitri Furman from Microsoft confirmed it. The process works on recently modified pages. If you have a badly bloated index today, compaction will improve it gradually over time as DML touches those pages — but how fast depends entirely on your workload patterns. A table with low DML activity may take a very long time to compact. If you have significant existing bloat, the guidance is to run a one-time REBUILD first to get page density up, then let auto compaction maintain it from that point forward.
Fill factor edge case.
Compaction never fills a page above the fill factor. But if DML has already pushed a page above the fill factor, compaction will not reduce it. Those pages stay as-is. For most shops running at the default fill factor of 100%, this is not a practical issue.
It does not update statistics.
If your current rebuild job is also your statistics update strategy — and in many shops it is — you will need a separate statistics update job to cover that gap. Auto compaction does not touch statistics.
Transaction log impact.
Moving rows between pages generates log writes. For write-intensive workloads, you may see an increase in log I/O and larger transaction log backups. The Microsoft FAQ says this impact is not noticeable for most workloads, but I would still be sure to monitor after enabling.
It suspends under pressure.
Compaction is deprioritized when the PVS cleaner is under load. It suspends entirely when PVS size exceeds 150 GB or when aborted transactions exceed 1,000. In those conditions, PVS cleanup takes priority and compaction stops until the backlog clears.
What it will not compact:
| Not eligible |
|---|
| Heap tables |
| ROW_OVERFLOW_DATA and LOB_DATA allocation units |
| Columnstore compressed rowgroups |
| Memory-optimized tables |
| System tables (msdb is the sole exception) |
| Indexes with page locks disabled |
Monitoring It
If you enable this in Azure today, the query below will give you page count, average page density, and average fragmentation across all eligible indexes — the three numbers to track before and after enabling compaction:
SELECT
COALESCE(OBJECT_SCHEMA_NAME(ips.object_id), '<Total>') [schema_name],
COALESCE(OBJECT_NAME(ips.object_id), '<Total>') [object_name],
COALESCE(i.name, '<Total>') [index_name],
AVG(ips.avg_page_space_used_in_percent) [avg_page_density],
AVG(ips.avg_fragmentation_in_percent) [avg_fragmentation],
SUM(ips.page_count) [page_count]
FROM
sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') ips INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE
i.type_desc IN ('CLUSTERED', 'NONCLUSTERED', 'XML', 'SPATIAL')
AND ips.index_level = 0
AND ips.page_count > 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
GROUP BY
ROLLUP(ips.object_id, i.name, i.type_desc, ips.partition_number)
HAVING (ips.object_id IS NULL AND i.name IS NULL)
OR (ips.object_id IS NOT NULL AND i.name IS NOT NULL)
ORDER BY
IIF(ips.object_id IS NULL, 0, 1), page_count DESC;
Change 'SAMPLED' to 'DETAILED' for precise results — but be aware that DETAILED mode does a full scan, which can be timely on large databases.
The Bottom Line
What it is not, is a replacement for all index maintenance. Statistics still need to be maintained separately. Severely bloated indexes benefit from a one-time REBUILD first, and for workloads with heavy random deletes causing genuine size growth, a targeted rebuild on the worst offenders is still the right tool.
But the underlying message from Microsoft — that fragmentation percentages are less important than page density, and that nightly full-index rebuilds are often more overhead than benefit — is the same argument that has been circulating in this community for years. The feature is the engine finally making it operational rather than just philosophical.
On-prem shops: watch the Microsoft Learn docs for an update to the 'Applies to' line on the automatic compaction page. That is when the conversation gets practical for most of your environments.
More to Read:
Stop Defragmenting and Start Living: Introducing Auto Index Compaction — Microsoft Community Hub
Automatic Index Compaction (preview) — Microsoft Learn
Optimize Index Maintenance to Improve Query Performance — Microsoft Learn
Stop Worrying About SQL Server Index Fragmentation — Brent Ozar
Why Index Fragmentation Doesn't Matter — Brent Ozar
