I think that is the clearest blog title I used in years. Why do I mention this? Let me explain...
Some time ago we stumbled over a performance issue in our applications. The root cause was a fragmented index, caused by the usage of a standard guid instead of a sequential guid.
While looking for the right fix, I had to revisit one of my own posts: Sequential GUIDs with .NET 9. In that post I mentioned that you can use Guid.CreateVersion7() to create a sequential guid. That is technically correct, but it is NOTa solution for SQL Server.
Why does a random guid hurt?
A clustered index in SQL Server is a sorted B-tree. When the key is random, every insert lands on a random page. If that page is full, SQL Server has to split it, which leaves you with half-empty pages, a fragmented index and more I/O. A sequential key always appends at the end, so pages fill up and stay put.
Why is a version 7 guid not sequential for SQL Server?
A UUID version 7 (RFC 9562) starts with a 48-bit Unix timestamp in milliseconds. The rest is random. Here are three values, generated a few milliseconds apart:
01a0ba47-e8d0-7198-9f36-28a04dcb9a21
01a0ba47-e8d6-771b-979a-a1ccc7581c77
01a0ba47-e8d9-7621-8d8b-bf27581ec544Read from left to right, they are in creation order. A database that compares a uuid byte by byte from left to right (PostgreSQL, for example) appends these values at the end of the index. SQL Server doesn't.
SQL Server does not compare a uniqueidentifier from left to right. The byte numbers below refer to the layout you get from Guid.ToByteArray(), which is also how SQL Server stores the value. From most to least significant, SQL Server compares:
- bytes 10-15 (last group): random
- bytes 8-9 (fourth group): variant + random
- bytes 6-7 (third group): version + random
- bytes 4-5 (second group): lower part of the timestamp
- bytes 0-3 (first group): upper part of the timestamp
The timestamp ends up in the least significant part of the sort key, and the random bits in the most significant part. From SQL Server's perspective, a v7 guid behaves like a random guid.
What are the options?
- Let EF Core generate the id. For a
Guidprimary key, the SQL Server provider usesSequentialGuidValueGeneratorby default, which generates guids optimized for clustered keys in SQL Server. If you assignGuid.CreateVersion7()yourself in your code, you bypass that default. - Let SQL Server generate the id through
NEWSEQUENTIALID():
CREATE TABLE Orders
(
Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
OrderDate DATETIME2 NOT NULL
);- Generate the id yourself, in SQL Server order. Libraries like RT.Comb do this. SequentialGuid offers
GuidV7.NewSqlGuid(), which rearranges the bytes to match SQL Server's sorting rules. You can also do the reshuffling yourself.
Sidetrack: Reshuffling a version 7 guid
The idea is simple: what if we could still generate a v7 guid and move the bytes around, so the timestamp lands in the bytes SQL Server looks at first.
public static Guid CreateVersion7ForSqlServer()
{
Span<byte> rfc = stackalloc byte[16];
Guid.CreateVersion7().TryWriteBytes(rfc, bigEndian: true, out _);
Span<byte> sql = stackalloc byte[16];
// SQL Server compares bytes 10-15 first, then 8-9, 6-7, 4-5 and finally 0-3
rfc[0..6].CopyTo(sql[10..]); // 48-bit timestamp becomes the most significant part
rfc[6..8].CopyTo(sql[8..]);
rfc[8..10].CopyTo(sql[6..]);
rfc[10..12].CopyTo(sql[4..]);
rfc[12..16].CopyTo(sql[0..]);
return new Guid(sql);
}new Guid(ReadOnlySpan<byte>) uses the same byte layout as SQL Server, so the value you generate is the value you find in the table. The timestamp now sits in the last group:
59ad233b-03fc-d091-77b3-01a0ba47e8dc
a16717c9-bf50-85a7-7aa6-01a0ba47e8df
68d1fa99-9500-90b4-72c8-01a0ba47e8e2Let's see this in action
I simulated a clustered index that receives one insert per millisecond, and counted how often a new value sorts after all previous values (meaning it is appended at the end of the index). I used SqlGuid for the comparison, as it follows SQL Server's ordering rules:
static double PercentAtEnd(Func<Guid> generate, int count = 5_000)
{
var max = new SqlGuid(generate());
var atEnd = 0;
for (var i = 0; i < count; i++)
{
Thread.Sleep(1); // roughly one insert per millisecond
var next = new SqlGuid(generate());
if (next > max) { max = next; atEnd++; }
}
return 100.0 * atEnd / count;
}
Console.WriteLine($"Guid.NewGuid(): {PercentAtEnd(Guid.NewGuid):F1}%");
Console.WriteLine($"Guid.CreateVersion7(): {PercentAtEnd(Guid.CreateVersion7):F1}%");
Console.WriteLine($"Reshuffled version 7: {PercentAtEnd(CreateVersion7ForSqlServer):F1}%");The result:
Guid.NewGuid(): 0.1%
Guid.CreateVersion7(): 0.2%
Reshuffled version 7: 100.0%Guid.CreateVersion7() is no better than a random guid here. The reshuffled version always appends.
Remark: The reshuffled value is no longer a valid UUIDv7 in its text form. Don't hand it to systems that expect the RFC 9562 layout or read the timestamp from it.
Not something that I would use in my applications, but a good test to understand why the CreateVersion7() is not what we need.
Check your own indexes
Changing the id generation only fixes new inserts. Existing fragmentation stays until you rebuild the index:
SELECT i.name, ps.avg_fragmentation_in_percent, ps.avg_page_space_used_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'SAMPLED') AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id;
ALTER INDEX ALL ON dbo.Orders REBUILD;That's it! Sequential is only sequential for the system that does the sorting. For SQL Server, that is not the order of RFC 9562.
More information
Avoiding index fragmentation with sequential guids
Using GuidCOMB in SQL Server and PostgreSQL
SequentialGuidValueGenerator Class | Microsoft Learn
NEWSEQUENTIALID (Transact-SQL) | Microsoft Learn
Add API to correctly insert GuidV7 to SQLServer · dotnet/SqlClient Discussion #2999
GitHub - richardtallent/RT.Comb
SequentialGuid/README.md at master · buvinghausen/SequentialGuid