Public preview · Azure SQL · SQL DB in Fabric
Automatic Index Compaction
Index maintenance without the maintenance job
Jeff Taylor
jaxdata.org
Every 3rd Wednesday 6-8pm
Except for May, November and December
Jeff Taylor
Principal Data Consultant
Database Consulting, LLC
Super Hero Suit
Public preview · Azure SQL · SQL DB in Fabric
Automatic Index Compaction
Index maintenance without the maintenance job
Jeff Taylor
The problem we've all lived with
Index maintenance is a tax you pay every night
- Scheduled jobs — fire in a window you have to defend in change control
- Resource-intensive — rebuild & reorganize touch every page, needed or not
- Free space required — a rebuild needs space ≈ the index size; in the cloud, a bill
- Constant babysitting — thresholds, reports, the 2 a.m. failure, the on-call page
A true story · the cost of doing it by hand
The night the maintenance job won
- A "standard" nightly REBUILD ALL — on a database quietly past a terabyte
- It ran long, then into the morning — still rebuilding as users logged in
- The log file exploded — rebuild is heavily logged; the drive filled, the job failed
- Blocking, then a P1 — queries piled up behind it. Guess who got the call?
The job built to protect availability is the thing that took us down.
Agenda
Where we're going
- Bloat 101 — density, splits, what fragmentation really costs
- The one-liner — turn it on, prove it's running
- See it work — a live OLTP workload: bloat in, compaction out
- The payoff — less maintenance, and faster queries — plus the honest limits
- Guardrails & adoption — overhead, eligibility, and Monday's plan
Bloat 101
What actually goes wrong inside an index
- Page density — how full each 8 KB page is
- Page splits — inserts/updates split full pages, leaving both ~50% empty
- The result — same rows, far more pages → more I/O, CPU, memory
DENSE ≈99%
BLOATED ≈53%
Same rows. Far more pages — every read touches more of them.
A reframe
Page density is the metric. Fragmentation usually isn't.
Density
Drives I/O, CPU & memory — fewer pages to read is a real, measurable win.
Fragmentation
Page order on disk — on modern flash it rarely changes query performance.
Compaction optimizes the metric that matters — and accepts the one that doesn't.
The entire feature
Introducing Automatic Index Compaction
A background process that consolidates partially-filled pages and removes empty ones — continuously, as data changes. You turn it on like this:
ALTER DATABASE [YourDatabase] SET AUTOMATIC_INDEX_COMPACTION = ON;No jobs
Stop scheduling maintenance
Less storage
Bloat is reclaimed automatically
Faster queries
Fewer pages → less I/O, CPU, memory
Availability
Where you can use it today (preview)
✓ Azure SQL Database
✓ Azure SQL MI
Always-up-to-date update policy
✓ SQL database in Fabric
✗ Not on box / on-prem SQL Server yet
SQL Server 2025 docs reference it, but on-prem isn't in the “Applies to” list. Watching this one closely.
Under the hood
How compaction actually works
It rides the Persistent Version Store (PVS) cleaner — the same background process used by Accelerated Database Recovery.
1 · Visit
Visits recently changed pages
2 · Check
Compares free vs used space nearby
3 · Move
Moves rows to fill a page
4 · Deallocate
Frees the emptied page
It only touches recently modified pages — never the whole index. That's why overhead is a fraction of a rebuild.
Don't take my word for it
The test scenario
Surface
Azure SQL Database (preview), one user DB — never master
The table
50,000-row clustered index — seq key, datetime, GUID, padded string
The workload
dbo.churn — random INSERT/UPDATE/DELETE/SELECT of 1–100 rows
We measure
Density, page count, logical reads of one fixed 1,000-row query — at 3 points
All scripts come straight from Microsoft's blog and Learn docs — nothing cooked up to look good.
Demo 1
Enable it, and prove it's on
-- Turn it on. No restart, no exclusive access.
ALTER DATABASE CURRENT SET AUTOMATIC_INDEX_COMPACTION = ON;
-- Verify across the server
SELECT database_id, name, is_automatic_index_compaction_on
FROM sys.databases;Watch is_automatic_index_compaction_on flip to 1.
Demos 2–4
Baseline, then wreck it
-- 50,000 tightly-packed rows (pristine, ~99.5% dense)
INSERT INTO dbo.t (s)
SELECT REPLICATE('c',50) FROM GENERATE_SERIES(1,50000);
-- dbo.churn: random INSERT/UPDATE/DELETE/SELECT, 1–100 rows
WHILE @i < @Iterations BEGIN EXEC dbo.churn; SET @i+=1; END;Baseline ≈ 99.51% density · 962 pages · 25 logical reads. Now the workload shreds it.
Demo 5 · run #2
The damage: same query, 60× the reads
1,610
logical reads
was 25
52.71%
page density
was 99.51%
4,394
pages
was 962
A normal day of writes — the exact state a nightly job exists to fix. Watch what happens if I do nothing.
Demo 5 · run #3 — the payoff
Minutes later, with zero user action
25
Before
1,610
After workload
35
After compaction
| Before | After WL | |
|---|---|---|
| Logical reads | 25 | 1,610 |
| Page density | 99.51% | 52.71% |
≈ 98%
fewer logical reads — automatically
Decision guide
Compaction vs. reorganize vs. rebuild
| Aspect | Auto compaction | Reorganize |
|---|---|---|
| Trigger | Continuous, automatic | Manual / scheduled |
| Pages processed | Recently modified only | All pages |
The honest limits · read the label
What it deliberately does NOT do
Doesn't defrag
Raises density, won't fix fragmentation — and may nudge it up. Usually fine.
Doesn't update stats
Unlike rebuild. Lean on auto-update, or add a small stats job.
Honors fill factor
Never fills a page above your fill factor.
Also: it slows file growth, but it's not a shrink — it won't hand allocated space back to the OS.
Safety & cost
Overhead is real but small — with guardrails
Low CPU
Occasional low single-digit % when it runs
Log I/O
Write-heavy workloads: more log write I/O & backups
Short X locks
Brief page locks like reorg; skips locked pages
Auto-suspend
Pauses if PVS > 150 GB or aborted txns > 1,000
Demos 6–7
Prove it — monitoring compaction
-- #1 Database-wide density & fragmentation rollup
SELECT ... FROM sys.dm_db_index_physical_stats(
DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') ...
-- #2 Extended Event: rows moved, pages deallocated
CREATE EVENT SESSION automatic_index_compaction ON DATABASE
ADD EVENT sqlserver.auto_index_compaction_stats ...;Fires every 10 min & is cumulative.
Monday morning
An adoption playbook
1. Enable in non-prod — watch your own metrics for a week
2. One-time rebuild — already bloated? Reset density once; compaction maintains it
3. Add a stats job — if you relied on rebuilds for statistics
4. Retire the old jobs — once you trust it, take your window back
The payoff
Less maintenance. Faster queries.
Less maintenance
•No scheduled jobs or windows
•No free space to provision
•No 2 a.m. pages or failed jobs
•The engine handles it, continuously
Faster queries
•Higher page density, automatically
•~98% fewer logical reads in our test
•Less disk I/O, CPU & memory
•Denser indexes → better plan choices
The limits are real but narrow: no defrag, no statistics update. Plan for those two and it's a clear win.
If you remember three things
Key takeaways
- One line, then forget it — ALTER DATABASE … = ON replaces a class of jobs
- Density is the win — ~98% fewer logical reads in the test, automatically
- Know the two "no"s — no defrag, no stats. Plan for them; clear net win
Thank you — go adjust a maintenance job
Resources
- Blog — “Stop defragmenting and start living” (Azure SQL Blog)
https://techcommunity.microsoft.com/blog/azuresqlblog/stop-defragmenting-and-start-living-introducing-auto-index-compaction/4500089/replies/4504851 - Docs — Automatic index compaction (preview), Microsoft Learn
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/automatic-index-compaction - Feedback — aka.ms/sqlfeedback · sqlaicpreview@microsoft.com
- Tool — SQLQueryStress: github.com/ErikEJ/SqlQueryStress
Questions?
Thank you!
Jeff Taylor
Thank you for attending my session today.
If you have any additional questions, please don't hesitate to reach out.
Automatic Index Compaction
By reviewmydb
Automatic Index Compaction
Automatic Index Compaction
