Azure SQL Database: does automatic index compaction fix the UUIDv7 density problem?

Where part 1 left off

In part 1, UUIDv7 did not fix the page density of a GUID clustered index in SQL Server. SQL Server sorts uniqueidentifier values starting from the last six bytes, which are random in a UUIDv7, so new rows land anywhere in the index. After 25,000 single-row inserts, the UUIDv7 and NEWID() indexes were both ~68 % full, close to the 69 % expected for random inserts into a B-tree, against ~98 % for NEWSEQUENTIALID().

Since then, automatic index compaction has become generally available in Azure SQL Database. This post checks whether it closes that gap.

This is deliberately an unfavorable case for the feature. A random clustering key on a small table under steady inserts is close to the worst workload for page density. It is also a case that is spreading in production, as more applications generate GUID or UUIDv7 keys on their side.

Automatic index compaction in short

My colleague Amine Haloui covered the feature in two posts: part 1 explains the difference between page density and fragmentation, part 2 shows how compaction works with a demo. The short version:

Automatic index compaction runs inside the persistent version store cleaner of Accelerated Database Recovery. It visits recently modified pages, moves rows from the next page into a page that has free space, and deallocates the pages that end up empty. You enable it per database with ALTER DATABASE ... SET AUTOMATIC_INDEX_COMPACTION = ON, without a restart.

It only acts on pages modified after it was enabled, does not move rows into the space reserved by the fill factor (nor free it once inserts have used it), does not reduce fragmentation and does not update statistics.

It has been generally available since September 2026 on Azure SQL Database, Azure SQL Managed Instance with the always-up-to-date update policy, and SQL database in Fabric. It is not in SQL Server 2025 on-premises, so all the tests below run on Azure SQL Database.

In short: when inserts pause, it closes most of the gap. While they keep coming, it cannot fix the underlying problem, a key that sends inserts to random positions. At the default fill factor the index even ends up larger than without compaction, and a lower fill factor limits the effect. Measuring page splits shows why: each compaction pass is followed by a wave of splits.

What has already been shown

I found two representative public tests. In both the clustering key is ordered (Microsoft’s test table has a NEWID() column, but not in the key) and the empty space comes from deletes or from a workload that then stops:

Sources: Microsoft announcement post, Amine Haloui part 2.

In both cases, compaction brings page density back to about 95 % with no maintenance job. This post looks at the case these tests leave out: a random key under steady inserts.

Lab setup

Two Azure SQL databases on the same server, General Purpose serverless, 2 vCores: GuidLab_On with compaction, GuidLab_Off as the control. Every test runs in both at the same time. The tables are those of part 1: a uniqueidentifier clustered primary key filled by NEWID(), a UUIDv7 (RFC 9562) or NEWSEQUENTIALID(), plus a char(100) payload, with rows inserted one per statement.

Each run preloads 5,000 rows per table. The occasional-insert run then measures every 10 seconds for 3 minutes. The continuous-insert runs wait 2 minutes, insert a burst every 10 seconds for 3 minutes, then measure one more minute without inserts. Two parameters vary: the fill factor (100, 90 or 80) and the burst size, 300 rows (6 % of the table) or 50 rows (1 %). Pages and fullness come from sys.dm_db_index_physical_stats in DETAILED mode, rows moved from the compaction_row_move_count column of sys.dm_db_index_operational_stats.

Occasional inserts: compaction does the job

5,000 rows inserted, then no more writes, as when inserts come in batches with quiet periods between them.

Without compaction, the index stays at 123 to 128 pages, 67 to 70 % full. With it, it drops to 92 to 94 pages, 91 to 93 % full, in under two minutes, close to the 87 pages of the sequential key. Most of the drop happens in a single pass.

Compaction also moves some rows more than once: 6,345 and 8,991 moves for 5,000 rows.

Continuous inserts, 6 % of the table every 10 seconds

Now 300 rows per table every 10 seconds, 6 % of the table per burst, for three minutes, then one minute without inserts. Each configuration ran at least twice; the figures below cover all runs and both random keys. The chart shows the first run, for UUIDv7.

At fill factor 100, compaction cannot keep up with this pattern. In the first run, the first burst takes the compacted index from 93 to 172 pages. At the end of the inserts it holds 326 to 334 pages, against 253 to 271 without compaction, and average fullness during the inserts is 56 %, against 68 to 70 %. Over the run, compaction moved more than 51,000 rows for a table of 10,400.

The explanation is that an index nobody compacts keeps about 31 % (100 minus 69) free space, which absorbs part of the next inserts. At fill factor 100, compaction moves rows into a page as long as they fit, so most pages are left with little room, and the next random insert splits them. Each split also writes far more log than a plain insert, about 18 times more for rows of similar size in Paul Randal’s test.

A lower fill factor helps. When pages split again soon after compaction, the documentation suggests a fill factor of 70 to 95 %, while noting that most workloads do best at 100, and the index maintenance guidance gives nonsequential GUID keys as its example. At 80, compaction leaves 20 % of each page free, at 90 only 10 %. That free space lets a page absorb a few random inserts before it splits.

At 80, average fullness during the inserts is 76 % in all three runs, against 68 to 70 % without compaction, and one minute after the inserts the index holds 223 to 230 pages, against 253 to 271. The gain is real but modest at this rate. At 90, the margin is too thin: average fullness is 66 to 69 %, and one run ended the inserts with 324 pages, more than without compaction.

Once the inserts stop, fill factor 100 catches up in one pass and ends the smallest: 202 to 207 pages.

Page splits at fill factor 100

To check the explanation above, I ran the 6 % test twice more at fill factor 100 and also recorded two counters from sys.dm_db_index_operational_stats: leaf_allocation_count, the number of leaf pages allocated, and compaction_page_deallocation_count, the number of pages compaction freed. Compaction only moves rows into pages that have room and frees the pages that end up empty, so the allocations come from the inserts. With a random key, new rows land anywhere in the index, so almost every allocation is a page split in the middle of it; with NEWSEQUENTIALID(), rows always go to the end, so every allocation is a new page there.

Without compaction, the 5,400 inserts split about 130 pages. With compaction, about 460: 3.5 times more.

The chart shows where they come from. Compaction ran about once a minute, and each time, the next burst split 78 to 133 pages. The bursts in between split 0 to 21, because the pages split by the previous wave were still half full and could take the new rows. The four bursts that followed a pass account for 90 % of the splits, and the page count follows the same steps.

From the first burst to the end of the run, compaction moved 46,000 to 47,600 rows to free about 350 pages, about 130 rows per page freed. The counters add up: in the first run of this test (UUIDv7), 94 pages at the start, plus 461 allocations, minus 224 pages freed during the inserts, gives the 331 pages measured at the end of the inserts.

Continuous inserts, 1 % of the table every 10 seconds

The same test at 50 rows per burst, 1 % of the table, also run at least twice. The chart shows the first run, for UUIDv7.

Fill factor 100 still degrades. In the first run, the first 50 inserts turned 95 pages into 128: about two inserts in three split a page. The index ends the inserts at 176 to 194 pages, against 143 to 153 without compaction. The lower rate only slows the degradation.

At 90 and 80, compaction helps throughout. Average fullness stays at 81 to 85 %, against 66 to 70 % without compaction, and the index ends at 115 to 121 pages, about 20 % fewer. Fullness never drops below 77 % at 90 and 74 % at 80. The two fill factors end at the same size.

At fill factor 80, the inserts slowly fill the 20 % that compaction leaves free: fullness rose from 73-76 % before the first burst to about 85 % in three minutes. Compaction does not give that space back. As the documentation puts it, “if that reserved space is already filled by the previous data modifications, then compaction doesn’t currently free it up”. Only a rebuild restores the margin.

All results

Each cell gives the range over all runs and both random keys (NEWID() and UUIDv7). The occasional-insert case ran once; its cells read NEWID() / UUIDv7.

Occasional inserts (5,000 rows, then 3 minutes without writes)

Continuous inserts, 6 % per burst (300 rows every 10 s, 10,400 rows at the end)

Continuous inserts, 1 % per burst (50 rows every 10 s, 5,900 rows at the end)

“Rows moved” counts every row compaction moved during the run: after the preload, during the inserts and in the final pass after them. “Pages at the end of the inserts” is a snapshot that depends on when the last compaction pass ran, so average fullness is the more stable measure. NEWSEQUENTIALID() gives the same figures with and without compaction: there is nothing to compact.

What I expected, and what held

1. Once the inserts stop, compaction brings the random keys well above 68 %. Held: 91 to 93 % within two minutes.
2. Under continuous inserts, fullness drops after each burst and recovers between them, averaging somewhere in between. Wrong at the default fill factor: it averages below the uncompacted index, not above it.
3. NEWID() and UUIDv7 behave the same. Held for pages and fullness: the averages stay within three points. Rows moved differ by up to 50 % between the two keys, in no consistent direction; probably because the rows moved depend on how free space is spread across the index, which is random.
4. Compaction finds nothing to do on NEWSEQUENTIALID(). Held: zero rows moved in every run.

So, should you rely on it?

For a random clustering key, automatic index compaction is worth enabling, but it does not address the root cause: inserts that land at random positions. The fill factor decides how much it can help.

• Inserts in batches with quiet periods: the default fill factor of 100 works. Compaction catches up within a minute or two and gives the densest random-key index. Each new batch still splits many of the freshly compacted pages.
• A steady stream of inserts: at the default fill factor, compaction makes the index larger than doing nothing. A fill factor of 80 helped at both rates tested; 90 helped only at the gentler one.
• Existing indexes: compaction ignores pages not modified since you enabled it, so rebuild once first. The rebuild is also where you set the fill factor.
• Watch page fullness and log volume, not fragmentation, which can go up with compaction.
• The key choice from part 1 still stands: the sequential key stayed the smallest in every run, and compaction never fully closed the gap. These results emphasize how much the application side matters. The clustering key is usually chosen by developers, often for good reasons, so it is a decision worth taking together with whoever runs the database, early, before the table holds millions of rows.
• And on-premises nothing changes for now, since the feature is not in SQL Server 2025.

Limits of these tests

• Small tables, few runs per configuration. 5,000 to 10,400 rows. Control runs of the same setup differ by up to 18 pages.
• Intense rates. 1 % and 6 % of the table every 10 seconds. On a large table, the same inserts per second touch a far smaller share of its pages; how that changes the outcome was not tested.
• Short runs. Three minutes of inserts; how the index behaves at 80 or 90 over hours was not measured.
• An unfavorable case by design. These results say nothing about how the feature behaves on other workloads.
• One platform. Azure SQL Database, General Purpose serverless, 2 vCores, one inserting session.

If you want to reproduce these tests, feel free to contact me and I will share the full scripts.