SQL Server Automatic Index Compaction: Is This the End of Index Maintenance?

SQL Server index maintenance has followed the same pattern for two decades: scheduled rebuild and reorganize jobs, shrinking maintenance windows, growing transaction logs, and the constant balancing act between performance and downtime. SQL Server Automatic Index Compaction changes that picture. Currently in preview for Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric, this new feature is designed to remove most of that operational overhead by handling index bloat continuously in the background.

This article walks through how the feature works, how to enable and monitor it, and how it compares to traditional index maintenance. If your team manages SQL Server environments and is weighing whether this is worth testing, Aryasoft’s SQL Server consulting services can help evaluate it against your own workloads.

The Problem With Traditional Index Maintenance

Scheduled rebuild and reorganize jobs come with real operational costs. Rebuilds are CPU- and I/O-intensive, maintenance windows keep shrinking while jobs take longer to finish, transaction logs balloon in size and put pressure on backups and replication, offline rebuilds lock the table completely, and even online rebuilds require brief exclusive locks at the start and end of the operation.

In addition, there is a more fundamental issue: these jobs have historically targeted the wrong metric. SQL Server index health is usually described through two different kinds of fragmentation:

• Internal fragmentation – how full each page is, measured by avg_page_space_used_in_percent. Low page density means more pages are needed to store the same data, which increases memory, I/O and CPU overhead.
• External fragmentation – whether pages are physically ordered on disk, measured by avg_fragmentation_in_percent. This mattered a great deal on spinning disks, where non-sequential reads meant expensive head movement.

However, on SSD, NVMe and cloud storage, the cost of non-sequential reads is now marginal, while the cost of low page density is not. Automatic Index Compaction is built specifically to address internal fragmentation, continuously, without touching external fragmentation at all.

How SQL Server Automatic Index Compaction Works

The feature is integrated into the Persistent Version Store (PVS) cleaner, a background process that is already part of Accelerated Database Recovery (ADR) on these platforms. In practice, it:

• Monitors pages that were recently modified by inserts, updates or deletes.
• Moves rows from subsequent pages into existing pages that have free space.
• Respects the space reserved by fill factor — it will not use that space.
• Deallocates pages once they become empty after row consolidation.

It only applies to leaf-level pages in IN_ROW_DATA allocation units: clustered indexes, nonclustered indexes, and B-tree indexes on special types such as XML, full-text and spatial data. Heap tables, ROW_OVERFLOW_DATA, LOB_DATA, compressed columnstore rowgroups, memory-optimized tables and indexes with disabled page locks are all out of scope.

The process skips a page if there is an active transaction on it, an index rebuild, reorganize or shrink is in progress, the PVS size has grown beyond 150 GB, or aborted transactions exceed 1,000. This is what allows it to run continuously in the background without competing with your workload for resources.

Enabling and Checking the Feature

In practice, SQL Server Automatic Index Compaction is controlled per database, with no restart or exclusive access required:

-- Enable
ALTER DATABASE [database-name] SET AUTOMATIC_INDEX_COMPACTION = ON;

-- Disable
ALTER DATABASE [database-name] SET AUTOMATIC_INDEX_COMPACTION = OFF;

-- Check status
SELECT database_id, name, is_automatic_index_compaction_on
FROM sys.databases;

-- Or, for a single database
SELECT DATABASEPROPERTYEX('database-name', 'IsAutomaticIndexCompactionOn');

It takes effect within minutes once enabled.

What Microsoft’s Own Demo Shows

Microsoft publishes an official, reproducible demo for Automatic Index Compaction in its bobsql GitHub repository, run against an Azure SQL Hyperscale database. The setup: a clustered index holding 1,000,000 rows at 99.8% page density. Half the rows are then scatter-deleted, which collapses density to 47.1% while the page count barely changes — meaning a full scan now reads far more pages than the data actually requires. Automatic Index Compaction is then enabled and left to run in the background.

Automatic Index Compaction consolidated 18,004 half-empty pages into 8,909 well-packed ones with no rebuild involved: logical reads dropped by roughly half, and cold-cache scan time dropped 43%, entirely in the background with no maintenance window.

One detail worth knowing: page count can briefly go up right after a large delete, even before compaction runs. On platforms using Accelerated Database Recovery, every deleted row keeps a small version pointer for the ghost record until cleanup, and on pages that were already packed near-full, that extra overhead can trigger page splits — which is exactly the kind of bloat Automatic Index Compaction is designed to clean up continuously.

Where It’s Available — and Where It Isn’t

Available on: Azure SQL Database, Azure SQL Managed Instance (with the Always-up-to-date policy), and SQL database in Microsoft Fabric.

Not available on: SQL Server 2025 on-premises, as of now. It also does not apply to system tables or system databases, with the exception of msdb.

Limitations to Plan Around

• It does not update statistics, unlike a rebuild.
• It does not reduce logical/external fragmentation, and can slightly increase it — though that is not a meaningful concern here.
• It does not restore the free space reserved by fill factor.
• It does not shrink data files; it reduces used space within them, not the allocated file size.
• On write-heavy workloads it can increase transaction log I/O and slightly raise fragmentation due to page splits.
• CPU overhead is minimal, in the low single-digit percentage range.

Monitoring Automatic Index Compaction

To track page density and fragmentation across your indexes:

SELECT COALESCE(OBJECT_SCHEMA_NAME(ips.object_id), '<Total>') AS schema_name,
       COALESCE(OBJECT_NAME(ips.object_id), '<Total>') AS object_name,
       COALESCE(i.name, '<Total>') AS index_name,
       AVG(ips.avg_page_space_used_in_percent) AS avg_page_space_used_in_percent,
       AVG(ips.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent,
       SUM(ips.page_count) AS page_count
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
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED', 'XML', 'SPATIAL')
  AND ips.index_level = 0
  AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
GROUP BY ROLLUP(ips.object_id, i.name, ips.partition_number);

For compaction-specific counters, query sys.dm_db_index_operational_stats for compaction_attempt_count, compaction_complete_count, compaction_skip_count, compaction_row_move_count and compaction_page_deallocation_count. An auto_index_compaction_stats extended event also fires every 10 minutes, reporting rows moved and pages deallocated, which is useful for building a lightweight monitoring dashboard.

Does SQL Server Automatic Index Compaction Replace Index Maintenance Jobs?

Not entirely. Statistics updates and logical fragmentation fixes still require a scheduled process, so SQL Server Automatic Index Compaction is not a full replacement for rebuild and reorganize jobs. What it does remove is the recurring, low-value work of keeping page density healthy across a constantly changing dataset — work that previously required a maintenance window and manual scheduling.

For example, a practical strategy is to run a one-time reorganize or rebuild on existing low-density indexes to bring them to a healthy baseline, then let Automatic Index Compaction take over ongoing maintenance from there. For environments that have outgrown their current maintenance windows, or where SQL Server modernization is already on the roadmap, this is worth testing early. See our related guide on SQL Server modernization for a broader view of where this fits into a wider upgrade or migration plan.

Because the feature is still in preview, we recommend validating it against your own workload in a non-production environment before enabling it broadly.

Further reading from Microsoft:
Automatic Index Compaction – Microsoft Learn
Automatic Index Compaction demo scripts – Microsoft GitHub (bobsql)
The End of Index Maintenance? – Data Exposed (YouTube)