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
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.
Compaction optimizes the metric that matters — and accepts the one that doesn't.
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;Availability
Where you can use it today (preview)
Under the hood
How compaction actually works
It rides the Persistent Version Store (PVS) cleaner — the same background process used by Accelerated Database Recovery.
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
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
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
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
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.
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.
