This is the first part of a series of two blog posts.<\/p>\n\n\n\n
Which problems does it address?<\/h2>\n\n\n\n
Automatic index compaction is Microsoft’s answer to a routine operational task: index maintenance jobs.<\/p>\n\n\n\n
Here are the problems with these jobs<\/p>\n\n\n\n
- \n
- They are expensive in CPU and I\/O. A rebuild reads and rewrites the entire index<\/li>\n\n\n\n
- They take time to run, and maintenance windows keep shrinking<\/li>\n\n\n\n
- They generate a lot of transaction log, which impacts log backups and Always On replication<\/li>\n\n\n\n
- They can block applications. An offline rebuild locks the table and even an online rebuild needs a short exclusive lock at the beginning and at the end<\/li>\n\n\n\n
- Someone has to set them up, monitor them and fix them when they fail<\/li>\n<\/ul>\n\n\n\n
There is also a less obvious problem: for years, these jobs have been optimizing the wrong metric. We will come back to this point in a moment.<\/p>\n\n\n\n
Requirements<\/h2>\n\n\n\n
The feature is currently in public preview and available on<\/p>\n\n\n\n
- \n
- Azure SQL Database<\/li>\n\n\n\n
- Azure SQL Managed Instance, with the Always-up-to-date update policy<\/li>\n\n\n\n
- SQL database in Microsoft Fabric<\/li>\n<\/ul>\n\n\n\n
It is not available on SQL Server on-premises, including SQL Server 2025.<\/p>\n\n\n\n
There is nothing to install. The feature relies on a background process that already runs on these platforms (the ADR cleaner, more on this below), and Accelerated Database Recovery is always enabled on Azure SQL Database, Azure SQL Managed Instance and SQL database in Fabric.<\/p>\n\n\n\n
Internal and external fragmentation<\/h2>\n\n\n\n
When we talk about index fragmentation, we are actually talking about two different phenomena. They are measured by two different columns of sys.dm_db_index_physical_stats, they have different causes, and above all they have very different costs.<\/p>\n\n\n\n
Before we start: how is a data file organized ?<\/p>\n\n\n\n
To understand fragmentation, we need one prerequisite: how SQL Server sees its data files.<\/p>\n\n\n\n
- \n
- For any program, a file is an array of bytes provided by the operating system. Byte 0, byte 1, byte 2, and so on. Reading means asking for a range: an offset and a length<\/li>\n<\/ul>\n\n\n\n
- \n
- SQL Server slices this array into pages of 8 KB. The page N is, by definition, the bytes N \u00d7 8192 to (N+1) \u00d7 8192 \u2212 1. The page ID is not a label, it is an address in the file<\/li>\n<\/ul>\n\n\n\n
- \n
- Everything below the file (NTFS clusters, SSD, SAN volumes) is translated and hidden by the storage stack.<\/li>\n<\/ul>\n\n\n\n
Every time we say “physical order” in the rest of this post, we mean the order of the pages inside the data file not their placement on the hardware.<\/p>\n\n\n\n
What does it look like?<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-26.png\")
<\/figure>\n\n\n\nInternal fragmentation: the pages are not full<\/h2>\n\n\n\n
Internal fragmentation means that the pages of an index contain free space. It is measured by avg_page_space_used_in_percent, also called page density.<\/p>\n\n\n\n
Where does it come from?<\/strong><\/p>\n\n\n\n
- \n
- Deletes, especially scattered deletes: the rows disappear, the pages stay<\/li>\n\n\n\n
- Page splits: when a page is full, an insert in the middle of the key range or an update that makes a row grow moves half of the rows to a newly allocated page. Both pages end up around 50% full<\/li>\n\n\n\n
- Updates that shrink variable length columns<\/li>\n\n\n\n
- Large rows that do not pack well: a 5000 bytes row means one row per page, and around 38% of every page is lost mechanically<\/li>\n<\/ul>\n\n\n\n
Note that some free space is normal, and even intentional: the space reserved by the fill factor is internal fragmentation on purpose to absorb future inserts without page splits.<\/p>\n\n\n\n
What does it look like?<\/strong><\/p>\n\n\n\n
CREATE DATABASE DemoFrag\nGO\n\nALTER DATABASE DemoFrag SET RECOVERY SIMPLE\nGO\n\nUSE DemoFrag\nGO\n\nCREATE TABLE dbo.SplitDemo (\n Id int NOT NULL CONSTRAINT PK_SplitDemo PRIMARY KEY CLUSTERED,\n Payload varchar(8000) NOT NULL\n);\n\n-- 2 lines about 4000 bytes per page\nINSERT INTO dbo.SplitDemo VALUES\n(1, REPLICATE('A', 4000)), (2, REPLICATE('B', 4000));<\/code><\/pre>\n\n\n\nMy page is almost full:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-25.png\")
<\/figure>\n\n\n\nWhy does it cost ?<\/strong><\/p>\n\n\n\n
Because the same rows are spread over more pages than necessary and everything in SQL Server is done at the page level:<\/p>\n\n\n\n
- \n
- The buffer pool caches pages, not rows. A page at 40% density wastes 60% of the memory it occupies<\/li>\n\n\n\n
- Every read, logical or physical, handles more pages for the same data<\/li>\n\n\n\n
- Backups are bigger, integrity checks are longer<\/li>\n<\/ul>\n\n\n\n
This is the key point: internal fragmentation follows the data everywhere, including in RAM. It is a permanent tax on memory, I\/O and CPU. However it’s not the case with external fragmentation.<\/p>\n\n\n\n
External fragmentation: the offsets do not follow the logical order<\/h2>\n\n\n\n
At the leaf level of a B-tree, the pages are chained together in the order of the index keys with two pointers stored in every page header (m_nextPage, m_prevPage). This chain is the logical order and it cannot be wrong: it is the order of the keys by definition.<\/p>\n\n\n\n
External fragmentation (also called logical fragmentation) means that following this chain no longer corresponds to reading the file sequentially. The pages are logically ordered, but their offsets in the file are not. It takes two forms:<\/p>\n\n\n\n
- \n
- Holes<\/strong>: the pages of the index are not at consecutive offsets. Pages of other objects, or free pages, sit in between<\/li>\n\n\n\n
- Disorder<\/strong>: the pages are at consecutive offsets, but chained in a different order<\/li>\n<\/ul>\n\n\n\n
avg_fragmentation_in_percent counts both forms without distinction. The columns fragment_count and avg_fragment_size_in_pages are closer to the real cost: they measure the length of the contiguous runs.<\/p>\n\n\n\n
Where does it come from?<\/strong><\/p>\n\n\n\n
- \n
- Several objects growing at the same time: the engine allocates the extents in the order the requests arrive, so the objects interleave in the file. This is the normal life of a database<\/li>\n\n\n\n
- Page splits: the new page is allocated where free space is found, rarely next to the original page<\/li>\n\n\n\n
- Page deallocations: massive deletes, and the automatic index compaction itself<\/li>\n<\/ul>\n\n\n\n
What does it look like?<\/strong><\/p>\n\n\n\n
USE DemoFrag\nGO\n\nCREATE TABLE dbo.SplitDemo (\n Id int NOT NULL CONSTRAINT PK_SplitDemo PRIMARY KEY CLUSTERED,\n Payload varchar(8000) NOT NULL\n);\n\n-- 2 lines about 4000 bytes per page : lines 1 and 2 on 1 page, 3-4 on next page\n\nINSERT INTO dbo.SplitDemo VALUES\n(1, REPLICATE('A', 4000)), (2, REPLICATE('B', 4000)),\n(3, REPLICATE('C', 4000)), (4, REPLICATE('D', 4000))\n\n-- Lines location (file:page:slot)?\nSELECT Id, sys.fn_PhysLocFormatter(%%physloc%%) AS physical_location\nFROM dbo.SplitDemo;<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-21.png\")
<\/figure>\n\n\n\nMy first 2 rows are located on page 376 and the last 2 rows on page 378.<\/p>\n\n\n\n
What does page 377 contain? I thought the pages were contiguous?<\/p>\n\n\n\n
Since my table has a clustered index on Id it is stored as a B-tree. Can we see it?<\/p>\n\n\n\n
SELECT allocated_page_page_id, page_type_desc, page_level, previous_page_page_id, next_page_page_id\nFROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.SplitDemo'),1, NULL, 'DETAILED');<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-16.png\")
<\/figure>\n\n\n\nWhat does it look like internally?<\/strong><\/p>\n\n\n\n
SELECT * FROM sys.dm_db_page_info (10, 1, 376, DEFAULT);<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-19.png\")
<\/figure>\n\n\n\nSELECT * FROM sys.dm_db_page_info (10, 1, 378, DEFAULT);<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-18-1024x45.png\")
<\/figure>\n\n\n\nPage split to generate external fragmentation:<\/strong><\/p>\n\n\n\n
-- Split happens\nUPDATE dbo.SplitDemo SET Payload = REPLICATE('B', 4100) WHERE Id = 2;\n\n-- Check page density\nSELECT index_level, page_count, avg_page_space_used_in_percent,\navg_fragmentation_in_percent, record_count,\nghost_record_count, version_ghost_record_count\nFROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.SplitDemo'),1, NULL, 'SAMPLED')\nWHERE alloc_unit_type_desc = 'IN_ROW_DATA';<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-17.png\")
<\/figure>\n\n\n\nAfter the page split:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-22.png\")
<\/figure>\n\n\n\nLine 1 is located at page 376<\/p>\n\n\n\n
Line 2 is located at page 379<\/p>\n\n\n\n
Line 3 is located at page 378<\/p>\n\n\n\n
Line 4 is located at page 378<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-20.png\")
<\/figure>\n\n\n\nWhy does it cost so little today?<\/strong><\/p>\n\n\n\n
There is exactly one mechanism through which external fragmentation ever costed something: the read-ahead. During a scan, the engine prefetches pages with large I\/Os and an I\/O is a contiguous range of the file: one offset, one length, up to 512 KB. When the index is contiguous one I\/O brings back 64 pages. When it is not the same pages arrive in several smaller I\/Os.<\/p>\n\n\n\n
When do these smaller I\/Os hurt ? Three conditions must be met at the same time:<\/p>\n\n\n\n
- \n
- The data is cold: a page already in the buffer pool generates no physical I\/O at all<\/li>\n\n\n\n
- The operation is a large scan: a seek navigates the B-tree by page ID and does not care about the order<\/li>\n\n\n\n
- Each additional I\/O has a price<\/li>\n<\/ul>\n\n\n\n
On SSD, NVMe and cloud storage, an additional I\/O costs a few dozen microseconds and the physical placement below the file is arbitrary anyway (SSD controllers and SANs put the blocks wherever they want).<\/p>\n\n\n\n
External fragmentation survived its own usefulness for another reason: it was a good symptom. It increases because of page splits, and page splits destroy the page density and inflate the transaction log. For twenty years, we treated the thermometer because it was correlated with the disease. The disease was the density.<\/p>\n\n\n\n
Reference<\/h2>\n\n\n\n
Automatic Index Compaction – SQL Server | Microsoft Learn<\/a><\/p>\n\n\n\n
- Disorder<\/strong>: the pages are at consecutive offsets, but chained in a different order<\/li>\n<\/ul>\n\n\n\n
- Holes<\/strong>: the pages of the index are not at consecutive offsets. Pages of other objects, or free pages, sit in between<\/li>\n\n\n\n
- Everything below the file (NTFS clusters, SSD, SAN volumes) is translated and hidden by the storage stack.<\/li>\n<\/ul>\n\n\n\n
- SQL Server slices this array into pages of 8 KB. The page N is, by definition, the bytes N \u00d7 8192 to (N+1) \u00d7 8192 \u2212 1. The page ID is not a label, it is an address in the file<\/li>\n<\/ul>\n\n\n\n
- For any program, a file is an array of bytes provided by the operating system. Byte 0, byte 1, byte 2, and so on. Reading means asking for a range: an offset and a length<\/li>\n<\/ul>\n\n\n\n