Blog Post

Azure SQL Blog
6 MIN READ

Stop defragmenting and start living: auto index compaction is now generally available

Dimitri_Furman's avatar
Mar 18, 2026

Executive summary

Automatic index compaction is a built-in MSSQL database engine feature that compacts indexes in background and with minimal overhead.

Now you can:

  • Stop using scheduled index maintenance jobs.
  • Reduce storage space consumption and save costs.
  • Improve performance by reducing CPU, memory, and disk I/O consumption.

Automatic index compaction is now generally available in Azure SQL Database, Azure SQL Managed Instance with the always-up-to-date update policy, and SQL database in Fabric.

Index maintenance without maintenance jobs

Enable automatic index compaction for a database with a single T-SQL command:

ALTER DATABASE [database-name] SET AUTOMATIC_INDEX_COMPACTION = ON;

Once enabled, you no longer need to set up, maintain, and monitor resource intensive index maintenance jobs, a time-consuming operational task for many DBA teams today.

As the data in the database changes, a background process consolidates rows from partially filled data pages into a smaller number of filled up pages, and then removes the empty pages. Index bloat is eliminated – the same amount of data now uses a minimal amount of storage space.

A conceptual view of the index compaction process

Resource consumption is reduced because the database engine needs fewer disk IOs and less CPU and memory to process the same amount of data.

By design, the background compaction process acts on the recently modified pages only. This means that its own resource consumption is much lower compared to the traditional index maintenance operations (index rebuild and reorganize), which process all pages in an index or its partition.

For a detailed description of how the feature works, a comparison between automatic index compaction and the traditional index maintenance operations, and the ways to monitor the compaction process, see automatic index compaction in documentation.

Let the numbers speak

As auto index compaction becomes generally available, it is already enabled in more than 6 million databases worldwide, most of them from Microsoft internal customers who helped validate the feature during preview. Hundreds of external customers also enabled auto compaction during preview and have been enjoying the benefits, with zero issues reported.

Looking at our worldwide telemetry data for a 28-day window, automatic index compaction freed up approximately 14.3 petabytes of space in data files by consolidating 547.5 trillion rows on fewer pages, saving resources and improving query performance.

Looking at the space freed up per database, the benefits range from a few megabytes per day for databases with already dense pages, to more than 500 gigabytes per day for databases that have gone through one-time extensive data modifications.

Compaction in action

To see the effects of automatic index compaction, we wrote a stored procedure that simulates a write-intensive OLTP workload. Each execution of the procedure inserts, updates, deletes, or selects a random number of rows, from 1 to 100, in a 50,000-row table with a clustered index.

We executed this stored procedure using a popular SQLQueryStress tool, with 30 threads and 400 iterations on each thread.

The SQLQueryStress tool running an OLTP-like workload

We measured the page density, the number pages in the leaf level of the table’s clustered index, and the number of logical reads (pages) used by a test query reading 1,000 rows, at three points in time:

  1. After initially inserting the data and before running the workload.
  2. Once the workload stopped running.
  3. Several minutes later, once the background process completed index compaction.

Here are the results:

 

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 πŸŸ’β¬‡οΈ

Before the workload starts, page density is high because nearly all pages are full. The number of logical reads required by the test query is minimal, and so is its resource consumption.

The workload leaves a lot of empty space on pages and increases the number of pages because of row updates and deletions, and because of page splits. As a result, immediately after workload completion, the number of logical reads required for the same test query increases more than 60 times, which translates into a higher CPU and memory usage.

But then within a few minutes, automatic index compaction removes the empty space from the index, increasing page density back to nearly 100%, reducing logical reads by about 98% and getting the index very close to its initial compact state. Less logical reads means that the query is faster and uses less CPU. All of this without any user action.

With continuous workloads, index compaction is continuous as well, maintaining higher average page density and reducing resource usage by the workload over time.

The T-SQL code we used in this demo is available in the Appendix.

Conclusion

Automatic index compaction delegates a routine database maintenance operation to the database engine itself, letting administrators and engineers focus on more important work without worrying about index maintenance.

Making this feature generally available doesn’t mean that we stop working on it. Your feedback during preview helped us find new opportunities to fine-tune the compaction process. We thank you for that feedback and look forward to announcing new improvements in auto index compaction in the future.

Appendix

Here is the T-SQL code we used to demonstrate automatic index compaction.

The type of executed statements and the number of affected rows is randomized to better represent an OLTP workload. While the results demonstrate the effectiveness of automatic index compaction, exact measurements may vary from one execution to the next.

/* Enable automatic index compaction */
ALTER DATABASE CURRENT SET AUTOMATIC_INDEX_COMPACTION = ON;

/* Reset to the initial state */
DROP TABLE IF EXISTS dbo.t;
DROP SEQUENCE IF EXISTS dbo.s_id;
DROP PROCEDURE IF EXISTS dbo.churn;

/* Create a sequence to generate clustered index keys */
CREATE SEQUENCE dbo.s_id 
AS int
START WITH 1 INCREMENT BY 1;

/* Create a test table */
CREATE TABLE dbo.t
(
id int NOT NULL CONSTRAINT df_t_id DEFAULT (NEXT VALUE FOR dbo.s_id),
dt datetime2 NOT NULL CONSTRAINT df_t_dt DEFAULT (SYSDATETIME()),
u uniqueidentifier NOT NULL CONSTRAINT df_t_uid DEFAULT (NEWID()),
s nvarchar(100) NOT NULL CONSTRAINT df_t_s DEFAULT (REPLICATE('c', 1 + 100 * RAND())),
CONSTRAINT pk_t PRIMARY KEY (id)
);

/* Insert 50,000 rows */
INSERT INTO dbo.t (s)
SELECT REPLICATE('c', 50) AS s
FROM GENERATE_SERIES(1, 50000);
GO

/*
Create a stored procedure that simulates 
a write-intensive OLTP workload.
*/
CREATE OR ALTER PROCEDURE dbo.churn
AS
SET NOCOUNT, XACT_ABORT ON;

DECLARE @r float = RAND(CAST(CAST(NEWID() AS varbinary(4)) AS int));

/* Get the type of statement to execute */
DECLARE @StatementType char(6) = CASE
                                 WHEN @r <= 0.15 THEN 'insert'
                                 WHEN @r <= 0.30 THEN 'delete'
                                 WHEN @r <= 0.65 THEN 'update'
                                 WHEN @r <= 1 THEN 'select'
                                 ELSE NULL
                                 END;

/* Get the maximum key value for the clustered index */
DECLARE @MaxKey int = (
                      SELECT CAST(current_value AS int)
                      FROM sys.sequences
                      WHERE name = 's_id'
                            AND
                            SCHEMA_NAME(schema_id) = 'dbo'
                      );

/* Get a random key value within the key range */
DECLARE @StartKey int = 1 + RAND() * @MaxKey;

/* Get a random number of rows, between 1 and 100, to modify or read */
DECLARE @RowCount int = 1 + RAND() * 99;

/* Execute a statement */

IF @StatementType = 'insert'
INSERT INTO dbo.t (id)
SELECT NEXT VALUE FOR dbo.s_id
FROM GENERATE_SERIES(1, @RowCount);

IF @StatementType = 'delete'
DELETE TOP (@RowCount) dbo.t
WHERE id >= @StartKey;

IF @StatementType = 'update'
UPDATE TOP (@RowCount) dbo.t
SET dt = DEFAULT,
    u = DEFAULT,
    s = DEFAULT
WHERE id >= @StartKey;

IF @StatementType = 'select'
SELECT TOP (@RowCount) id, dt, u, s
FROM dbo.t
WHERE id >= @StartKey;
GO

/*
The remainder of this script is executed three times:

1. Before running the workload using SQLQueryStress.
2. Immediately after the workload stops running. 
3. Once automatic index compaction completes several minutes later.
*/

/*
Monitor page density and the number of pages and records
in the leaf level of the clustered index.
*/
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;

/* Run a test query and measure its logical reads. */
DROP TABLE IF EXISTS #t;

SET STATISTICS IO ON;

SELECT TOP (1000) id, dt, u, s
INTO #t
FROM dbo.t
WHERE id >= 10000

SET STATISTICS IO OFF;

 

Updated Sep 28, 2026
Version 2.0

7 Comments

  • srikanthbandaru's avatar
    srikanthbandaru
    Copper Contributor

     Hi Dimitri  Is this configuration available for any Database compatability level or only for newer version?




  • Jeff_Moden's avatar
    Jeff_Moden
    Tin Contributor

    Where are the tests that show the Before'n'After for Middle-of-the-index page splits on non-ever increasing indexes (non-clustered indexes big here) in a rapid fire oltp environment and a large insert batch-job environment?  What does the blocking look like there while waiting for the page splits to happen on the now-too-full pages?

     

    • Dimitri_Furman's avatar
      Dimitri_Furman
      Icon for Microsoft rankMicrosoft

      Hi Jeff - we are aware of the overfull-pages-in-rapid-fire-oltp scenario. Thanks for your feedback on that here and elsewhere - hear you loud and clear. When I said in my LinkedIn post that we are not done, this was one of the things that "will make auto index compaction work even better". We ask to please give us the time to do it properly.

      At the same time, as important as that scenario is, it's not the most prevalent one across all SQL workloads. We do not want to block on that future optimization and withhold the feature from customers who have been waiting for the GA label to get the benefits.

      • Jeff_Moden's avatar
        Jeff_Moden
        Tin Contributor

        Dimitri,

        Thank you for your feedback.  I appreciate what you and your team do but, according to the 'tube below from timestamps 0:38 through 0:49, you folks aren't waiting and I can’t help but be concerned.

        https://www.linkedin.com/feed/update/urn:li:activity:7511422952161132544/

        With that, I'd suggest that you provide folks with the tools or the knowledge on how to use existing methods to measure the before and after for a week of compute, memory, and I/O usage measurements and costs to make sure that they don't have the not-so-rare conditions that could cause a problem with running the near equivalent of ALTER INDEX REORGANIZE in an endless cycle due to perpetuation of page splits caused by the removal of free space without maintaining the fill factor once the area on pages above the fill factor has been filled.

        For pages that have the default of "0" (same as 100 in all other aspects) for a Fill Factor, the problem is just as bad as a too-full Fill Factor.

        People need to be able to measure such a thing.

  • m60freeman's avatar
    m60freeman
    Brass Contributor

    It would be interesting to know how this compares with INDEX REBUILD, especially comparing the net performance impact on scans of the index on SSD vs. a magnetic drive. My hypothesis is that it would be reasonably close on SSD, but that INDEX REBUILD would have better results on magnetic drives, where random IO for a compacted index would be higher than for one rebuilt to be contiguous rather than only densely packed.