Automatic Index Compaction

Azure SQL DB

Azure SQL Managed Instance

Microsoft Fabric SQL Database

Index maintenance has been part of a DBA’s life for as long as I can remember. We rebuild,

we reorganize, we schedule a job to run in the middle of the night, and we guard that maintenance window, making sure we squeeze as much in there as we can. The metric we have always chased is fragmentation — and back on spinning disks, that made sense.

But storage changed. In modern flash, the physical order of pages on disk has little effect on query performance. The metric that actually drives your I/O is page density — how full each 8KB page is. When a page splits, you end up with two half-empty pages, the same rows spread across more pages, and every read touching more of them.

In March 2026, Microsoft introduced Automatic Index Compaction, a feature that targets density directly and does so automatically. Microsoft’s announcement describes a background process that continuously consolidates partially-filled pages and removes empty ones — no job, no maintenance window.

It is in public preview on Azure SQL Database, Azure SQL Managed Instance (Always-up-to-date update policy), and SQL database in Microsoft Fabric. It is not yet in on-prem SQL Server, but I'm hoping it's released soon in a Cumulative Update.

Let’s run a few tests and see how this actually works.

Test Scenario

I am using the script example here from Dimitri Furman's blog to test a single Azure SQL Database with a clustered table of 50,000 tightly-packed rows — the pristine, just-rebuilt state at about 99% page density that every maintenance job is chasing. Every line of T-SQL below comes straight from the Azure SQL blog and the Microsoft Learn examples; I just sequenced them into a repeatable before/after. To measure, I watch two things: page density and page count from sys.dm_db_index_physical_stats, and logical reads from a fixed query with SET STATISTICS IO ON.

Turning It On

The entire feature is one line. No agent job, no script library to babysit, no restart, and no exclusive access — it starts within minutes.

ALTER DATABASE CURRENT SET AUTOMATIC_INDEX_COMPACTION = ON;
GO

-- Verify it flipped on (watch the flag go to 1)
SELECT name, is_automatic_index_compaction_on
FROM sys.databases
WHERE database_id = DB_ID();
GO

Watch the flag flip to 1, and the engine is now maintaining the database for you.

The Baseline

With the feature on, build the table and capture the starting baseline. At baseline, my 50,000-row table sits at 99.51% density across 962 pages, and a fixed query returning 1,000 rows takes just 25 logical reads. Remember that number.

-- Density, pages, and rows at the leaf level
SELECT avg_page_space_used_in_percent AS page_density, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.t'), 1, 1, 'DETAILED')
WHERE index_level = 0;

-- The fixed query I measure each time
SET STATISTICS IO ON;
SELECT TOP (1000) id, dt, u, s INTO #t FROM dbo.t WHERE id >= 10000;
SET STATISTICS IO OFF;

Wrecking It

Next, I run the churn procedure, which randomly inserts, updates, deletes, and selects 1 to 100 rows at a time in a loop. That is not an attack — it is a normal day of OLTP writes, and it is exactly what shreds page density through page splits. You can drive it with SQLQueryStress at 30 threads and 400 iterations to match the published numbers, or with a self-contained T-SQL loop if you can’t install a tool on the box.

Originally, I believe this test was simulated with 12,000 iterations, but if you run a quick test in SSMS that might be a bit too much, so I recommend turning this down and working up if you want to test.

-- Self-contained workload driver
DECLARE @Iterations int = 500;   -- more = more bloat
DECLARE @i int = 0;
WHILE @i < @Iterations
BEGIN
    EXEC dbo.churn;
    SET @i += 1;
END;
GO

After the workload, the picture is ugly. That same query now takes 1,610 logical reads — 60 times the I/O for the identical result set. Density has collapsed to 52.71%, and the table has ballooned to 4,394 pages. The data did not grow; only the density changed. In the old world, we would schedule a rebuild exactly here.

The Payoff

Here is the part that still feels strange to type. I did nothing. No rebuild, no reorganize, no command. I waited a few minutes and ran the same query a third time.

25 reads became 1,610 — and then dropped back to 35. Density recovered to 96.11% and pages fell to 1,065. That is roughly 98% of the logical reads gone, automatically, with no maintenance window and no job to babysit.

What It Doesn’t Do

It does not defragment — it raises density but will not fix fragmentation, and may even nudge it up slightly (fine on flash). It does not update statistics the way a rebuild does as a side effect, so lean on auto-update stats or add a small stats job. It honors your fill factor and is not a shrink — notice the recovery landed at 96%, not back at the pristine 99.5%, which is exactly what I would expect. Overhead is real but small: low single-digit CPU, a little more log write I/O on write-heavy workloads, and brief page locks (locked pages are simply skipped). It also has guardrails and will auto-suspend if the version store grows too large or too many transactions abort.

One thing to note while testing: the auto_index_compaction_stats Extended Event fires every 10 minutes and its counters are cumulative, so if you start the session late you can stare at an empty result for 20 minutes wondering what is wrong. Start it at the very beginning.

Conclusion

A 98% reduction in logical reads for one line of T-SQL and zero ongoing maintenance is, in my opinion, worth it. I want to test this next on a larger, multi-terabyte database and watch density trend over several days in something closer to production, but the early results are hard to argue with. If you have been managing nightly index jobs and a maintenance window, this one is worth a look.

If you want to run it yourself, here are the demo scripts on Github I used https://bit.ly/automaticindexcompactiondemo, and the full presentation is https://bit.ly/presentationautoindexcompaction.

Measurement	Before Workload	After Workload	After Compaction
Logical reads	25	1,610	35
Page density	99.51%	52.71%	96.11%
Pages	962	4,394	1,065

If you give this a try, I'd love to hear about your results. If you have any additional thoughts or comments leave them in the comments below or send me an email.