I was watching Microsoft youtube video’s, and came over the following video. So I asked my AI to create a blogpost on it. Looks promising 🙂

Microsoft’s Azure SQL team recently announced the public preview of Automatic Index Compaction.

The headline is appealing: stop running disruptive index-maintenance jobs, reduce storage consumption, and let the database engine continuously keep indexes compact.

What is Automatic Index Compaction?

As records are inserted, updated, and deleted, index pages can become partially empty. The same business data then occupies more pages than necessary. Queries must read and cache those additional pages, increasing I/O, CPU, and memory consumption.

Automatic Index Compaction works continuously in the background. As part of the Persistent Version Store cleaner, it examines recently modified pages, moves rows into available space on neighboring pages, and deallocates pages that become empty.

The expected result is:

• Higher page density
• Fewer used database pages
• Less logical I/O
• Lower CPU and memory consumption for affected queries
• Less need for resource-intensive index rebuilds and reorganizations

The feature is enabled at database level:

ALTER DATABASE CURRENT
SET AUTOMATIC_INDEX_COMPACTION = ON;

It is currently disabled by default and remains in public preview for Azure SQL Database, Azure SQL Managed Instance using the Always-up-to-date update policy, and SQL Database in Microsoft Fabric. Microsoft’s technical documentation contains the detailed behavior and limitations.

Microsoft’s impressive—but synthetic—test

Microsoft demonstrated the feature with a write-intensive workload against a 50,000-row table:

After the workload created substantial empty space, compaction reduced logical reads by approximately 98% and brought page density close to its original level.

These results demonstrate the mechanism very effectively—but they are not a D365 F&O benchmark. The test used one table, one test query, and a synthetic workload that stopped before the final measurement. It did not measure posting routines, inventory transactions, batch execution, integrations, blocking, or end-user response times.

The numbers should therefore be treated as evidence that the feature can work, not as a prediction of an equivalent F&O performance improvement.

Compaction is not the same as defragmentation

Despite the announcement’s title, Automatic Index Compaction does not actually remove logical index fragmentation. In some cases, fragmentation can even increase when empty pages are deallocated.

What it improves is page density—and Microsoft argues that page density is generally more important for most modern Azure SQL workloads because a compact index requires fewer pages to store and read the same data.

Automatic compaction also:

• Does not update index statistics
• Does not create missing indexes
• Does not remove unnecessary indexes
• Does not fix inefficient queries
• Does not compact heaps, LOB data, row-overflow data, memory-optimized tables, or compressed columnstore rowgroups
• Only considers eligible pages modified after the feature is enabled
• Does not shrink the allocated database files

An existing database with low page density might therefore still need a one-time rebuild or reorganization. Statistics maintenance might also remain necessary.

This is best understood as a continuous page-density keeper—not a universal replacement for every form of database maintenance.

Why this is relevant to D365 F&O

F&O workloads commonly contain tables with substantial transactional churn:

• Inventory and warehouse transactions
• Financial posting
• Settlements and updates
• Batch processing
• Data imports and integrations
• Periodic cleanup and retention processes

Such activity can leave unused space inside existing index pages. If Microsoft enables Automatic Index Compaction for managed F&O databases, higher page density could mean fewer pages to read and cache.

Possible benefits include:

• More consistent performance between maintenance cycles
• Reduced SQL I/O, CPU, and memory pressure
• Less bursty resource consumption from large rebuild operations
• Slower growth in used database space
• Additional platform headroom during posting, batch, and integration workloads

These are reasonable technical implications, but they are not currently documented F&O capabilities.

That means the benefit might be substantial while remaining almost completely invisible to customers and partners.

Can an F&O customer enable it?

For the production transaction database, the practical answer is no.

Microsoft does not give customers or partners direct SSMS access to an F&O production database. The ALTER DATABASE command shown in the Azure SQL announcement is therefore not a supported customer action against AXDB. Microsoft’s go-live documentation explicitly states that customers cannot connect to the production database through SSMS.

Tier 2 and higher sandboxes can offer temporary just-in-time database access for troubleshooting. However, the JIT access documentation does not state that customers may change platform-level database settings. Temporary read-write access should not be interpreted as permission to enable an Azure SQL preview feature.

Will it reduce F&O storage costs?

Possibly—but this must not be promised.

Automatic Index Compaction reduces the number of used pages inside database files. It does not automatically reduce the allocated file size.

Microsoft has not documented how pages reclaimed by Automatic Index Compaction would affect that capacity calculation. It might slow reported storage growth or postpone an additional-capacity requirement, but that remains an open question.

It will not rescue a poorly designed solution

Even if Microsoft adopts the feature for F&O, it will not compensate for:

• Inefficient X++ queries
• Missing or poorly designed indexes
• Excessive row-by-row processing
• Badly scheduled batch workloads
• Long-running transactions
• Integration designs that create unnecessary database activity
• Uncontrolled transactional or staging-table growth

Automatic compaction maintains existing eligible indexes. It does not determine which indexes should exist.

Customer-defined indexes that are genuinely required should still be created through supported application metadata and table extensions, tested with representative data, and deployed through the normal application lifecycle. Microsoft’s table-extension guidance also warns against adding unique indexes to existing tables through extensions.

What should F&O customers do now?

First, do not treat an Azure SQL announcement as authorization to change the F&O production database.

Instead:

1. Continue monitoring business-level performance indicators such as posting duration, batch completion time, integration throughput, blocking symptoms, and storage growth.
2. Continue improving custom queries, index design, batch scheduling, integrations, cleanup routines, and data-retention policies.
3. Ask Microsoft whether Automatic Index Compaction is being evaluated for AXDB and whether it will complement or replace parts of the existing managed maintenance process.
4. Ask how statistics maintenance, monitoring, and existing low-density indexes would be handled.
5. Ask whether reclaimed pages would be reflected in Operations database capacity reporting.
6. If evaluating the feature in a customer-owned Azure SQL database such as BYOD, use a non-production workload and remember that Azure public previews are generally not intended for production unless Microsoft explicitly states otherwise. Azure preview lifecycle guidance

The bottom line

Automatic Index Compaction fits the direction D365 F&O has followed since 2021: move database maintenance away from customer-run jobs and into an automated, managed platform.

Its potential is real, especially for high-churn transactional indexes. If Microsoft enables and validates it for F&O, customers could benefit from denser indexes, lower resource consumption, slower storage growth, and more consistent performance—without receiving another configuration setting to manage.

But today it remains an Azure SQL preview, not an announced D365 F&O feature.

PS! It works on Tier-2 environments:

ALTER DATABASE CURRENT
SET AUTOMATIC_INDEX_COMPACTION = ON;