Azure SQL Automatic Index Compaction: Less Index Maintenance

Introduction

Index maintenance has always been an important part of database administration. Indexes improve read performance, but inserts, updates, and deletes can create empty space inside index pages, increase storage consumption, and affect how efficiently the database reads data.

Traditionally, database teams have handled this through scheduled index maintenance jobs. Depending on the workload, those jobs may reorganize or rebuild indexes at regular intervals.

Azure SQL now provides Automatic Index Compaction, a background capability designed to keep recently modified index pages compact without requiring the same kind of recurring maintenance job. The feature works by gradually moving rows between pages and deallocating pages that become empty. It is currently available as a preview capability for Azure SQL Database, Azure SQL Managed Instance with the Always-up-to-date update policy, and SQL database in Microsoft Fabric.

The important part is that automatic compaction is not simply another name for index rebuild. It solves a different problem. It focuses on page density and storage efficiency while operating continuously in the background, rather than scanning and rebuilding an entire index.

What Is Automatic Index Compaction?

Automatic index compaction is a background process that helps reduce empty space inside recently modified index pages.

When rows are inserted, updated, or deleted, an index can develop unused space. For example:

Before:

Page 1: [Row][Row][    ][    ]
Page 2: [Row][    ][    ][    ]
Page 3: [Row][Row][Row ][    ]

After Compaction:

Page 1: [Row][Row][Row ][Row ]
Page 2: [Row][Row][Row ][Row ]
Page 3: [Row][    ][    ][    ]

If a page becomes empty after rows are moved to another page, the empty page can be deallocated.

That means the database can potentially use fewer pages to store the same amount of logical data.

Microsoft describes automatic index compaction as a continuous, low-overhead process that works as data changes rather than waiting for a scheduled maintenance job.

Why Page Density Matters

To understand automatic compaction, it helps to understand page density.

SQL Server stores table and index data in pages. A page has a fixed amount of storage space. If the page contains relatively little useful data, the database may need to read more pages to retrieve the same amount of information.

Consider:

Low Page Density

Page 1 -> 2 rows
Page 2 -> 2 rows
Page 3 -> 3 rows
Page 4 -> 2 rows
Page 5 -> 3 rows


Higher Page Density

Page 1 -> 5 rows
Page 2 -> 5 rows
Page 3 -> 2 rows

The second arrangement can require fewer pages to be read.

Fewer pages can mean less disk I/O and less memory pressure for workloads that scan or read those pages.

This is one reason Microsoft focuses on page density when describing automatic index compaction.

Page Density vs Index Fragmentation

This is one of the most important concepts when using automatic compaction.

Database administrators often look at index fragmentation when deciding whether an index needs maintenance.

However, fragmentation and page density measure different things.

The sys.dm_db_index_physical_stats function exposes metrics such as:

avg_page_space_used_in_percent
avg_fragmentation_in_percent

The first measures page density.

The second measures fragmentation.

Automatic index compaction primarily improves page density. It does not exist to eliminate fragmentation. Microsoft specifically notes that compaction can result in higher fragmentation in some situations while still improving page density and reducing the number of pages that need to be read.

This changes an important part of traditional index-maintenance thinking.

A higher fragmentation percentage does not automatically mean that the database is performing worse.

How Automatic Index Compaction Works

Automatic index compaction is integrated with the background persistent version store cleaner process.

As the cleaner processes pages containing recently modified data, automatic compaction can look for available space and move rows from a following page into that free space.

Conceptually:

Page A                  Page B

[Row][Row][  ][  ]      [Row][Row][Row][  ]
       |
       | Move rows
       v

Page A                  Page B

[Row][Row][Row][Row]    [Row][  ][  ][  ]

If the second page becomes empty, it can be deallocated.

The process continues as a background operation rather than running a complete index rebuild.

Microsoft notes that automatic compaction considers recently modified pages and can skip pages when concurrent activity prevents compaction. Those pages can be considered again later.

How Is It Different From Index Reorganization?

Index reorganization is a traditional online maintenance operation that works through an index to improve its physical organization.

Automatic compaction is more targeted.

A simplified comparison is:

Area	Automatic Compaction	Index Reorganization
Execution	Continuous background process	Explicit maintenance operation
Scope	Recently modified pages	Index-wide operation
Main goal	Improve page density	Reorganize fragmented index
Maintenance job	Not normally required	Usually scheduled
Resource usage	Low overhead	Can be higher
Fragmentation	Does not directly eliminate it	Designed to reduce fragmentation
Statistics update	No	No
Existing low-density pages	Not immediately fixed	Can process existing pages

This distinction is important.

If an index already has poor page density before automatic compaction is enabled, the feature does not suddenly rebuild the entire index.

Microsoft recommends considering a one-time reorganization or rebuild when an existing index needs its page density improved immediately. Automatic compaction can then help maintain the index as data changes.

How Is It Different From Index Rebuild?

An index rebuild is a much more comprehensive operation.

Conceptually:

Existing Index
      |
      v
Read Index
      |
      v
Create New Structure
      |
      v
Populate Pages
      |
      v
Replace Existing Index

A rebuild can substantially change the physical structure of an index and can also update index statistics.

Automatic compaction does not work this way.

It incrementally consolidates rows on pages that are being processed by the background cleaner.

This means the two operations should not be considered interchangeable.

Automatic Compaction Does Not Replace Every Maintenance Task

One of the easiest mistakes is assuming:

Automatic Compaction ON
        =
No Index Maintenance Ever

That is too broad.

Automatic compaction can reduce the need for recurring index maintenance focused on page density, but it does not perform every function of an index rebuild.

For example, automatic compaction does not update index statistics.

If your workload depends on statistics maintenance, you still need an appropriate statistics strategy.

Similarly, workloads with special fragmentation or fill-factor requirements may still benefit from occasional manual index maintenance.

Enabling Automatic Index Compaction

Automatic index compaction is disabled by default.

It can be enabled at the database level using T-SQL:

ALTER DATABASE [MyDatabase]
SET AUTOMATIC_INDEX_COMPACTION = ON;

To disable it:

ALTER DATABASE [MyDatabase]
SET AUTOMATIC_INDEX_COMPACTION = OFF;

You can check the current state through sys.databases:

SELECT
    database_id,
    name,
    is_automatic_index_compaction_on
FROM sys.databases;

Microsoft also documents the DATABASEPROPERTYEX option for checking whether automatic index compaction is enabled.

Because the feature is currently a preview capability, production teams should verify the current service support and preview terms for their exact Azure SQL configuration before enabling it broadly.

What Happens After You Enable It?

You do not need to restart the database or provide exclusive access just to enable automatic compaction.

Microsoft states that compaction starts or stops within minutes after changing the database setting.

The process then works in the background.

It does not immediately scan every index and compact everything.

This is important when setting expectations.

Suppose you have a large database with years of index history:

Database
|
+-- Index A: old low-density pages
+-- Index B: recently modified pages
+-- Index C: heavily updated pages
+-- Index D: rarely modified pages

Enabling automatic compaction does not mean all four indexes will be fully rebuilt.

The feature works progressively as eligible pages are processed.

Existing Index Bloat May Need a One-Time Operation

Imagine an index has already accumulated significant empty space.

Turning on automatic compaction may help maintain the index going forward, but it does not necessarily provide an immediate cleanup of all historical low-density pages.

A reasonable strategy can therefore be:

Existing Database
       |
       v
Measure Page Density
       |
       v
One-Time Maintenance if Needed
       |
       v
Enable Automatic Compaction
       |
       v
Continuous Background Maintenance

The exact decision should be based on the database workload and measured index health.

Do not rebuild every index simply because automatic compaction is being introduced.

What About Fragmentation?

You may notice something surprising after enabling automatic compaction.

An index can have:

Higher Fragmentation
+
Higher Page Density
+
Fewer Pages

At first this can look like a problem if your monitoring process focuses only on fragmentation percentage.

But the higher page density may be more valuable for the actual workload.

Microsoft notes that for most workloads, the benefits of higher page density outweigh the performance impact associated with increased fragmentation.

This is an important shift from simplistic maintenance rules such as:

IF fragmentation > X
THEN rebuild

Database maintenance decisions should be based on actual workload behavior rather than a single percentage.

Write-Heavy Workloads

Automatic compaction is particularly interesting for workloads with frequent data modifications.

Consider an order-processing system:

INSERT
UPDATE
DELETE
UPDATE
INSERT
UPDATE

These operations can continuously change index pages.

Automatic compaction can work in the background as those pages are processed.

However, there is still a cost.

Microsoft notes that workloads with significant compaction activity can see additional transaction-log write I/O and larger transaction-log backups. A small increase in CPU utilization can also occur, although it is generally low.

Therefore, automatic does not mean free.

Does Automatic Compaction Cause Blocking?

Automatic compaction can acquire short-term exclusive page locks while moving rows.

However, the process is designed to minimize interference with normal workload activity.

If a page cannot be locked immediately, the compaction process can skip it rather than waiting and creating unnecessary blocking.

Microsoft notes that any blocking caused by compaction should generally be short-lived, measured in milliseconds, and uncommon.

This design is important for production databases where long-running maintenance operations can be disruptive.

What Happens During an Index Rebuild?

If an index is being rebuilt or reorganized, automatic compaction skips that index while the maintenance operation is active.

This means teams do not need to worry about automatic compaction fighting with an index rebuild at the same time.

After the maintenance operation is complete, eligible pages can again be considered by the background compaction process.

Fill Factor Still Matters

Fill factor controls how much free space is intentionally left on index pages during certain index operations.

For example:

FILLFACTOR = 80

means that an index rebuild can intentionally leave approximately 20 percent of a page available under the relevant conditions.

Automatic compaction does not simply ignore the concept of fill factor and fill every available byte.

Microsoft notes that the free space reserved by fill factor is not used for automatic compaction. However, if that reserved space has already been consumed by previous data modifications, compaction does not recreate that free space.

This matters for workloads that intentionally use a lower fill factor to reduce page splits.

Automatic Compaction and Page Splits

Page splits occur when a page does not have enough room for new data and SQL Server needs to create or redistribute pages.

For a heavily updated index, page splits can contribute to fragmentation.

Automatic compaction can later consolidate some page space, but that does not mean it eliminates the underlying workload behavior causing page splits.

If a workload repeatedly experiences page splits, investigate:

Index key design
Insert patterns
Fill factor
Page density
Update frequency
Hot pages

Do not use automatic compaction as a replacement for proper index design.

Monitoring Automatic Compaction

Enabling a feature without measuring its effect is not a good production strategy.

Track metrics such as:

Index page count
Average page density
Index fragmentation
Storage usage
CPU utilization
Data I/O
Log I/O
Query duration

The important question is:

Is the workload getting better?

For example:

Before

Pages:             1,000,000
Page density:          62%
Query latency:          High


After

Pages:               850,000
Page density:          78%
Query latency:          Lower

These numbers are only an example of the type of measurement you should make. They are not expected results or a benchmark.

Your actual workload may behave very differently.

Query Performance After Compaction

A compact index can reduce the number of pages a query needs to read.

For example:

Before:

Query
 |
 +--> Page 1
 +--> Page 2
 +--> Page 3
 +--> Page 4
 +--> Page 5
 +--> Page 6


After:

Query
 |
 +--> Page 1
 +--> Page 2
 +--> Page 3
 +--> Page 4

Fewer pages can reduce:

Disk I/O
Buffer-pool usage
CPU spent processing pages
Storage consumption

However, this does not mean every query will become faster.

A query that is already efficient may show little visible difference.

Automatic Compaction and Statistics

This is a critical limitation.

Automatic index compaction does not update index statistics.

If your database depends on updated statistics for good query plans, you should continue to manage statistics separately.

For example:

Index Compaction
        |
        +--> Page Density
        |
        +--> Storage Efficiency


Statistics Maintenance
        |
        +--> Cardinality Information
        |
        +--> Query Plan Quality

These are related database-performance concerns, but they solve different problems.

Common Mistakes

Treating Compaction as an Index Rebuild

Automatic compaction does not rebuild the entire index.

It works continuously on eligible recently modified pages.

If an existing index requires a complete restructuring, a rebuild or reorganization may still be appropriate.

Monitoring Only Fragmentation

A high fragmentation percentage does not automatically mean the index needs rebuilding.

Look at page density, query behavior, page count, and actual workload performance together.

Assuming Statistics Are Updated

Automatic compaction does not update statistics.

If your maintenance process previously depended on index rebuilds to refresh statistics, review that dependency.

Enabling It Everywhere Without Measurement

Even though the feature is designed for low overhead, it is still a database workload.

Start with representative databases, monitor the effect, and expand deliberately.

Ignoring Transaction Log Impact

Write-heavy workloads can experience additional transaction-log I/O when rows are moved during compaction.

This should be included in production monitoring.

Best Practices

Establish a Baseline First

Before enabling automatic compaction, collect index page counts, page density, fragmentation, storage usage, and representative query performance.

Without a baseline, you cannot confidently determine whether the feature is helping.

Focus on Page Density

Do not create an automated process that reacts only to fragmentation percentage.

Page density can be more important for workloads where reducing the number of pages read is the main performance concern.

Keep Statistics Maintenance Separate

If your workload requires regular statistics updates, continue managing statistics independently.

Use One-Time Maintenance Where Necessary

If an existing index already has poor page density, consider whether a one-time reorganization or rebuild is appropriate before relying on automatic compaction for ongoing maintenance.

Monitor Log I/O

For write-heavy databases, watch transaction-log behavior after enabling compaction.

Test Before Broad Deployment

Preview features should be introduced carefully. Start with workloads where you can measure the effect and where a controlled rollback is available.

Advantages

Less Scheduled Index Maintenance

The most obvious benefit is that teams do not need to rely entirely on recurring maintenance jobs to keep recently modified pages compact. Instead of waiting for a nightly or weekly maintenance window, automatic compaction works continuously as the database processes changes.

Lower Maintenance Overhead

Traditional index maintenance can consume significant resources because reorganization and rebuild operations may process large portions of an index. Automatic compaction focuses on recently modified pages, which can reduce the amount of work required to maintain page density continuously.

Better Storage Efficiency

When rows are consolidated and empty pages can be deallocated, the database may need fewer pages to store its active data. This can reduce used storage inside data files and can also reduce the amount of data that needs to be read by some queries.

Reduced I/O and Memory Consumption

A more compact index can require fewer pages to be read. That can reduce disk I/O and the amount of buffer-pool memory needed for the same logical data, particularly for workloads where page density was previously poor.

Continuous Maintenance

Scheduled jobs create a cycle where indexes may become less compact and then receive maintenance later. Automatic compaction changes this model by continuously addressing eligible modified pages, allowing maintenance to happen incrementally instead of relying entirely on large periodic operations.

Disadvantages

It Does Not Replace All Index Maintenance

Automatic compaction is focused on page density and does not perform every function of an index rebuild or reorganization. It does not update statistics, and it does not guarantee that all existing fragmentation or historical low-density pages will be corrected immediately.

It Can Increase Transaction Log Activity

Moving rows between pages requires database work and can result in additional transaction-log write activity. Write-intensive workloads should therefore monitor transaction-log throughput and backup size after enabling the feature.

Fragmentation May Increase

Automatic compaction can improve page density while leaving or even increasing fragmentation in some workloads. This can look counterintuitive if an existing maintenance strategy treats fragmentation as the primary health metric.

Existing Index Problems May Remain

Enabling automatic compaction does not instantly repair every index in a database. If an index already has poor physical characteristics, a one-time rebuild or reorganization may still be necessary before automatic compaction can maintain it effectively.

The Feature Is Still a Preview Capability

Preview features should not be treated exactly like mature, long-established database behavior. Organizations with strict production requirements should validate the current support status, limitations, and operational characteristics before making automatic compaction part of a critical maintenance strategy.

Troubleshooting

Page Density Is Not Improving

Check whether the affected pages are being modified and are eligible for compaction. Automatic compaction does not scan every historical page simply because the feature has been enabled.

If an index already has poor page density, consider whether a one-time reorganization or rebuild is required.

Fragmentation Increased

Do not immediately disable the feature.

Compare fragmentation with:

Page density
Page count
Query performance
I/O
CPU
Storage usage

A higher fragmentation percentage can coexist with improved overall workload behavior.

Transaction Log Usage Increased

Review whether compaction is active on heavily modified indexes.

Additional log I/O can occur when rows are moved during compaction.

Queries Are Still Slow

Check the execution plan and query design.

Automatic compaction does not fix:

Missing indexes
Poor joins
Bad predicates
Blocking
Incorrect statistics
Inefficient application queries

Index physical maintenance is only one part of query performance.

Compaction Appears to Be Skipping Pages

Pages can be skipped because of concurrent activity or other maintenance operations. They can be considered again when the background process encounters them later.

A Practical Monitoring Query

You can use sys.dm_db_index_physical_stats to examine index page density and fragmentation.

For example:

SELECT
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    avg_page_space_used_in_percent AS PageDensity,
    avg_fragmentation_in_percent AS Fragmentation,
    page_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'SAMPLED'
)
WHERE index_id > 0
ORDER BY page_count DESC;

This provides a useful starting point for comparing index health before and after enabling automatic compaction.

Do not use one query result as the only basis for deciding whether an index needs maintenance. Combine physical metrics with actual workload performance.

Automatic Compaction vs Traditional Maintenance

A modern Azure SQL maintenance strategy can look like this:

                   Azure SQL Database
                          |
             +------------+------------+
             |                         |
             v                         v
    Automatic Compaction       Automatic Tuning
             |                         |
             v                         v
     Page Density              Index Recommendations
             |                         |
             +------------+------------+
                          |
                          v
                  Manual Maintenance
                  When Actually Needed

Automatic compaction and automatic tuning solve different problems.

Azure SQL automatic tuning can identify opportunities to create or drop indexes and can also force a last known good query plan. Automatic index compaction, on the other hand, works on the physical organization of eligible pages.

These capabilities can complement each other rather than replacing one another.

When Should You Consider Automatic Index Compaction?

It is particularly interesting for databases where:

Data changes frequently
Index pages accumulate empty space
Storage efficiency matters
Index maintenance jobs consume resources
Teams want to reduce recurring maintenance work
Query workloads benefit from improved page density

It may require more careful evaluation when:

The workload has unusual fill-factor requirements
Statistics maintenance depends heavily on index rebuilds
The database has highly sensitive transaction-log workloads
Existing indexes require significant restructuring
The organization has strict policies around preview features

Summary

Azure SQL Automatic Index Compaction introduces a different approach to index maintenance. Instead of waiting for a scheduled reorganization or rebuild, the database can continuously compact eligible recently modified pages in the background.

The key concept is page density, not simply fragmentation.

Automatic compaction can reduce the number of pages used by an index, improve storage efficiency, and potentially reduce I/O, CPU, and memory consumption. At the same time, it does not replace every form of index maintenance. It does not update statistics, does not immediately repair all historical low-density pages, and does not eliminate every reason an administrator might need a rebuild or reorganization.

For developers and database administrators, the best approach is to treat automatic compaction as another tool in the Azure SQL performance toolbox. Establish a baseline, measure page density and workload behavior, monitor transaction-log activity, and keep statistics maintenance separate.

The important shift is to stop thinking about index maintenance as a simple rule such as "fragmentation is high, so rebuild the index." Instead, look at page density, query performance, I/O, storage, workload patterns, and the actual reason maintenance is needed.

Used with the right monitoring and workload analysis, automatic index compaction can reduce routine index-maintenance work while keeping Azure SQL indexes more space-efficient as data changes.