Why UUIDv7 does not reduce fragmentation in SQL Server

This post came out of the 8kb conference. Andy Yun’s session pointed to Jeff Moden’s Black Arts Index Maintenance, a talk that argues fragmentation percentages are the wrong thing to watch and that page density is where the real cost sits. Watching it left me with a question it does not answer: if density is what matters, what happens when the clustering key is a GUID, and does UUIDv7 improve the picture the way it does elsewhere?

The answer turns on how SQL Server compares uniqueidentifier values, which is not explicitly documented. So it has to be measured.

Context

To showcase it, run this on any instance. No database required.

SELECT g
FROM (VALUES
    (CAST('ffffffff-ffff-ffff-ffff-000000000000' AS uniqueidentifier)),
    (CAST('00000000-0000-0000-0000-000000000001' AS uniqueidentifier))
) v(g)
ORDER BY g;

The value starting with ffffffff comes back first, ahead of the one that is almost entirely zeros.

That behaviour is intentional, and Microsoft covers it in a sentence that is easy to overlook: for uniqueidentifier, ordering is not implemented by comparing the bit patterns of the two values. The documentation stops there. It tells you the comparison is something other than a plain byte scan, then leaves the actual sequence unstated.

The omission has become expensive. Developers are now bringing UUIDv7, the time-ordered identifier standardised in RFC 9562, on the reasonable assumption that a leading timestamp turns scattered inserts into an append pattern. That holds where a database compares UUIDs lexicographically over the RFC byte sequence. SQL Server uses a different order, and nothing about the identifier warns you.

Since the order is undocumented, the only way to establish it is to measure it.

How the bytes are stored

A uniqueidentifier is a 16-byte value. The five hyphen-separated groups you see on screen are a display convention. Storage follows the Windows GUID structure, which is mixed-endian: the first three groups are byte-reversed on disk, while the last two keep the order you read them in.

SELECT CAST(CAST('01020304-0506-0708-090a-0b0c0d0e0f10' AS uniqueidentifier) AS binary(16));

Every byte here is distinct, so both halves of the rule are visible at once. The first group 01020304 is stored 04 03 02 01, the second 0506 becomes 06 05, the third 0708 becomes 08 07. The fourth and fifth groups appear exactly as written.

The comparison order

The ADO.NET documentation supplies part of the answer. SqlGuid implements CompareTo to match SQL Server behaviour, treating the last six bytes as the most significant, whereas System.Guid evaluates all sixteen.

Testing the rest against a live instance produces a sequence that held for every value I tried:

SQL Server reorders the sixteen stored bytes as 10-15, 8-9, 6-7, 4-5, 0-3, then compares them left to right.

A single permutation followed by an ordinary binary comparison, with no special handling per group.

Applied to the two values above:

A = ffffffff-ffff-0010-0000-aaaaaaaaaaaa
B = ffffffff-ffff-2000-0000-aaaaaaaaaaaa

stored     A : FFFFFFFF FFFF 1000 0000 AAAAAAAAAAAA
           B : FFFFFFFF FFFF 0020 0000 AAAAAAAAAAAA

reordered  A : AAAAAAAAAAAA 0000 1000 FFFF FFFFFFFF
           B : AAAAAAAAAAAA 0000 0020 FFFF FFFFFFFF

The first eight bytes are identical in both keys, the ninth settles the comparison, and the remaining seven are never examined.

The six bytes read first are stored positions 10 to 15. In an RFC 4122 UUIDv1 those hold the node field, historically derived from the MAC address, though implementations are free to substitute a random value there.

Testing it on 10,000 values

Hand-picked examples illustrate a rule without showing that it generalises. To check that, rebuild the proposed key in T-SQL and compare the order it predicts against the engine’s own.

WITH G AS (
    SELECT TOP (10000) g = NEWID() FROM sys.all_columns a CROSS JOIN sys.all_columns b
), B AS (
    SELECT g, b = CAST(g AS binary(16)) FROM G
), P AS (
    SELECT g, Cle = SUBSTRING(b,11,6) + SUBSTRING(b,9,2) + SUBSTRING(b,7,2)
                  + SUBSTRING(b,5,2)  + SUBSTRING(b,1,4)
    FROM B
), R AS (
    SELECT R1 = ROW_NUMBER() OVER (ORDER BY g),
           R2 = ROW_NUMBER() OVER (ORDER BY Cle)
    FROM P
)
SELECT Mismatches = SUM(CASE WHEN R1 <> R2 THEN 1 ELSE 0 END) FROM R;

Zero mismatches on 10,000 values. Substituting the naive key, sixteen bytes read straight through from left to right, returns 10,000 mismatches on the same set, which at least confirms the test discriminates between the two models.

That is strong evidence, but it stops short of proof. Repeated runs, with fresh values each time, returned zero mismatches every time. The implementation itself is undocumented, and what I ran covers one version of SQL Server (2022).

Where UUIDv7 puts its timestamp

RFC 9562 builds UUIDv7 around a 48-bit big-endian Unix timestamp in milliseconds, placed in the leading bytes. The remaining fields carry version and variant bits along with random data, optionally including a sub-millisecond fraction or a counter to improve monotonicity inside a single millisecond.

TTTTTTTT - TTTT - 7RRR - VRRR - RRRRRRRRRRRR
|_____________|                              48-bit timestamp

That timestamp occupies the first two textual groups, so in storage its bytes land in positions 0 to 3 and 4 to 5, both byte-reversed. Positions 4 to 5 are read only after 10 to 15, 8 to 9 and 6 to 7, and positions 0 to 3 come last of all.

Which means the fields SQL Server examines first contain version bits, variant bits and random data. Independently generated values separate on those long before the timestamp is reached, so for a clustered index taking single-row inserts the append behaviour UUIDv7 is known for simply does not materialise.

Measured on 25,000 inserts

Three tables, identical apart from the clustered key, FILLFACTOR 100, 25,000 rows inserted one row per statement. Bulk loading can hide the effect entirely. When SQL Server sets DMLRequestSort on a clustered index insert, it feeds the rows in key order, which promotes sequential writes and avoids page splitting. Whether it does so depends on the estimated row count, the locking hint and a cost decision, so the safest way to reproduce an OLTP pattern is to insert one row per statement.

About one percentage point separates NEWID() from UUIDv7, and the sign changes between runs, so under this workload UUIDv7 buys you nothing at all.

The sequential key holds the same rows in 391 pages rather than 560, roughly 30 % fewer. Since pages are the unit SQL Server reads and caches, a sparse index needs more buffer pool to hold the same rows and more reads to scan them, which puts the cost on memory and I/O well before it shows up as disk space.

A few things are worth knowing if you reproduce this. The measurements come from the sys.dm_db_index_physical_stats function, called in DETAILED mode: avg_page_space_used_in_percent comes back NULL under LIMITED, and passing an index_id while object_id is NULL raises an error rather than returning every index. Read page fullness in preference to fragmentation, since a mid-page split leaves both halves partly empty and fullness is the figure that registers it.

Practical options

The costly combination here is quite specific: a GUID that is random under SQL Server’s comparison order, used as the clustering key, fed by sustained single-row inserts. Change any one of those three and the physical consequences shift. GUIDs still earn their place whenever identity has to be generated away from the database, by clients, by distributed services, or by merge replication, which relies on uniqueidentifier to keep rows distinct across copies.

A sequential clustered key sends every insert to the same last page, and under concurrency those threads all queue on the same PAGELATCH_EX. Microsoft describes what happens next: the insert that triggers a new page holds the latch longer than usual, the queue builds up behind it, and throughput falls off a cliff. OPTIMIZE_FOR_SEQUENTIAL_KEY caps how many threads may queue for the latch, which keeps throughput steadier without removing the contention.

The useful question, then, is where the GUID sits in the physical design and which of those two costs your workload actually pays.

You can move it off the clustering key while leaving it as the primary key. SQL Server lets you declare PRIMARY KEY NONCLUSTERED on the GUID and cluster on a narrow IDENTITY column instead. The physical result matches a clustered IDENTITY with a unique constraint on the GUID, but the declaration matches the logical model: the GUID identifies the row, the IDENTITY only orders it on disk.

CREATE TABLE dbo.T (
    RowId bigint           IDENTITY NOT NULL,
    Id    uniqueidentifier NOT NULL DEFAULT NEWID(),
    CONSTRAINT PK_T PRIMARY KEY NONCLUSTERED (Id),
    INDEX CX_T CLUSTERED (RowId)
);

Either way the table stays dense and the 16-byte cost is confined to one index. The question is also narrower than it looks, since it applies to OLTP tables taking single-row inserts. On the analytical side a clustered columnstore index changes the storage model entirely, page density in the rowstore sense stops being the right measure, and this is a typical solution for some Data Warehouse use-cases (thanks to Uwe Ricken for the reminder 😉).

You can make it sequential instead. NEWSEQUENTIALID() works because it puts the increasing part where SQL Server reads first. Its documented limits are worth reading before you commit: the values are guessable and unsuitable where privacy matters, it only functions as a column DEFAULT, and the sequence can restart from a lower range after a Windows restart.

Or accept the cost. That is easy on a small table with rare inserts, where a single rebuild holds and the wasted space is trivial anyway, and harder on a busy random-key table that drifts back to the same density (or higher) after every rebuild.

Another solution could be a heap, because it removes the ordering question from the table itself: rows land wherever free space allows, and a nonclustered index on the GUID locates them by RID rather than by the clustering key. It suits tables with little DML. An update that grows a row past its page leaves a forwarding record behind, and the RID then costs an extra read on every lookup that follows it. Deletes have a different drawback: the rows are marked as ghosted, but the emptied pages stay allocated to the table unless the delete takes a table lock or the heap is rebuilt.

Whichever way you go, audit what is already in production. A large uniqueidentifier-keyed index sitting near 69 % fullness deserves a look, since it may be taking values whose insertion order is effectively random for SQL Server, whether they come from NEWID(), UUIDv7 or something else. Density only points somewhere. Check fill factor, insert pattern and rebuild history before drawing a conclusion.

SELECT
    [Schema]      = OBJECT_SCHEMA_NAME(ps.object_id),
    [Table]       = OBJECT_NAME(ps.object_id),
    [Index]       = i.name,
    [FillFactor]  = NULLIF(i.fill_factor, 0),
    Pages         = ps.page_count,
    Fullness      = CAST(ps.avg_page_space_used_in_percent AS decimal(5,1)),
    Fragmentation = CAST(ps.avg_fragmentation_in_percent  AS decimal(5,1))
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
JOIN sys.index_columns ic ON ic.object_id = i.object_id
                         AND ic.index_id  = i.index_id
                         AND ic.key_ordinal = 1
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ps.index_level = 0
  AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
  AND ps.page_count > 500
  AND i.type IN (1, 2)
  AND TYPE_NAME(c.system_type_id) = 'uniqueidentifier'
ORDER BY ps.avg_page_space_used_in_percent;

Where that leaves us

An RFC specifies what the bits mean, and nothing in it governs how a storage engine chooses to order them.

UUIDv7 delivers what it promises, a sortable timestamp at the start of the RFC byte sequence, and SQL Server applies the comparison order it has always applied, in which that timestamp carries almost no weight. Both behaviours are internally consistent. Put together, under the workload measured here, they produce no clustered-index locality whatsoever, and the version number in the identifier gives you no hint that the temporal ordering has been lost along the way.

So when the next identifier scheme arrives described as sequential, the version number and the printed form will settle very little. What counts is where the increasing portion ends up once the engine has applied its storage layout and its comparison order.