SQL Server: Automatic index compaction in Azure (preview) – Part 2

Why does index fragmentation matter less than before ?

Logical fragmentation was a problem on spinning disks: an ordered scan on a fragmented index broke the read-ahead into smaller I/Os and each jump cost a disk head movement. On SSD, NVMe and cloud storage, the penalty of non-sequential reads is marginal. And the “physical order” we used to restore so carefully is only the order of the pages inside the data file: below it, the file system, the SSD controller and the storage layer place the blocks wherever they want anyway.

Here is the mapping to remember

• Internal fragmentation = page density = fixed by automatic index compaction
• External fragmentation = page order = ignored by automatic index compaction (it can even increase slightly and we will see it in the demo)

How does it work ?

Automatic index compaction is not a hidden maintenance job. It is an additional task performed by a background process that already exists: the PVS cleaner is a component of Accelerated Database Recovery (ADR).

Here is the principle

• The PVS cleaner periodically visits the pages that were recently modified (insert, update, delete) to remove obsolete row versions
• When automatic index compaction is enabled the cleaner also checks if the visited page has free space excluding the space reserved by the fill factor
• If so, it moves rows from the next page into the current page as long as they fit and repeats the operation on a few consecutive page pairs
• A page that becomes empty is deallocated

The result: the number of pages decreases, the page density increases, and the storage space, I/O, CPU and buffer pool consumption decrease.

The overhead is minimal because the process only considers recently modified pages, unlike a rebuild or a reorganize which process all the pages. Like a reorganize, the compaction acquires short-term exclusive page locks to move the rows. If a lock cannot be acquired immediately, the page is simply skipped and will be considered again later.

One command per database, no restart, no exclusive access. The compaction starts within minutes:

-- Enable the feature
ALTER DATABASE [SQL-DB-1] SET AUTOMATIC_INDEX_COMPACTION = ON;

-- Check if it's enabled
SELECT name, is_automatic_index_compaction_on FROM sys.databases;

One important point before you enable it

The compaction process only considers the pages modified after you enable the feature. If the page density of your indexes is already low, run a one-time reorganize or rebuild to fix the existing situation. From that point on, the automatic compaction keeps the indexes compact without any action on your side.

Demo

• We create a table with a clustered primary key and we insert 5 million rows
• We measure the baseline: page count, page density, fragmentation
• We delete 2 rows out of 3, scattered over the whole table, to simulate index bloat
• We measure again and we let the engine work

We create the table and we insert 50K rows:

CREATE TABLE dbo.Demo (
    Id      int IDENTITY CONSTRAINT PK_Demo PRIMARY KEY CLUSTERED,
    Payload char(400) NOT NULL DEFAULT REPLICATE('X', 400)
);

INSERT INTO dbo.Demo (Payload)
SELECT TOP (50000) REPLICATE('X', 400)
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

We measure the baseline:

SELECT index_level, page_count, avg_page_space_used_in_percent,
       avg_fragmentation_in_percent, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Demo'),1, NULL, 'SAMPLED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

We measure the baseline:

SELECT index_level, page_count, avg_page_space_used_in_percent,
       avg_fragmentation_in_percent, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Demo'),1, NULL, 'SAMPLED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

The internal fragmentation is low.

Here is what we have:

Here is what we have:

However to observe the automatic index compaction in action we need to create the following situation:

Here is the baseline:

We create the bloat

We delete 2 rows out of 3 scattered uniformly over the table. No page becomes empty. Every page keeps around one third of its rows. We change page density:

DELETE FROM dbo.Demo WHERE Id % 3 <> 0;

Without automatic index compaction, the expectation is simple: the page count stays at 277778 (deletes alone do not deallocate non-empty pages) and the density drops to around 31% (one third of 94.93%).

We measure again

We let the engine finish its job and we check again

The engine did the work in the background while the database stayed fully available.

This feature replaces the compaction part of your maintenance strategy not the whole strategy.

A summary:

• It does not update the statistics. A rebuild updates them the automatic compaction does not. If you rely on your rebuild jobs to refresh the statistics, keep a statistics update job.
• It does not reduce the logical fragmentation and it can even increase as we saw in the demo. For most workloads this has no measurable impact
• It does not recreate the free space reserved by the fill factor. Only a rebuild does. Workloads that need a fill factor below 100 to reduce page splits can still benefit from an occasional rebuild
• It does not shrink the data files. The used space decreases, the allocated size does not change
• It only applies to the leaf level of B-tree indexes in IN_ROW_DATA allocation units. Heaps, LOB data, row-overflow data, compressed columnstore rowgroups and memory-optimized tables are not concerned
• Indexes with page locks disabled (ALLOW_PAGE_LOCKS = OFF) are not eligible

Reference

Automatic Index Compaction – SQL Server | Microsoft Learn

Thank you. Amine Haloui