Hvorfor UUIDv7 ikke er svaret på fragmentering i SQL Server

Denne artikkelen tar for seg noen av utfordringene, og eventuelle løsninger, knyttet til bruk av uniqueidentifier for lagring av GUIDs i SQL Server inn i uniqueidentifier kolonne typen. Fokuset er på hvorfor fragmentering oppstår ved bruk av uniqueidentifier typen, og hva man kan gjøre for å unnvike dette.

.Net UUIDv7 og Uniqueidentifier typen i SQL Server

At man skal være forsiktig med bruk av uniqueidentifier typen for lagring av GUIDs i SQL Server bør i dag være godt kjent. Med .Net 9 (foreløpig i RC1) kommer det støtte for UUIDv7. Med denne støtten har det begynt å dukke opp informasjon rundt hvor bra dette er for databaser. Dette stemmer riktig nok ikke for SQL Server.

En GUID ved bruk av Guid.NewGuid() er en UUIDv4, det samme er bruk av NEWID() i SQL Server. I følge RFC9562, er denne på 128 bits, hvor 4 bits er brukt til versjon og 2 til variant. Dette er likt for alle versjoner, mens hva som skjer med de resterende 122 bitsene vil variere. I UUIDv4 er disse tilfeldig generert. Dette har en stor fordel med at IDene er helt unike, men skaper store problemer med fragmentering i SQL Server.

SQL Server har derfor en funksjon NEWSEQUENTIALID() som vil løse opp i mange av problemene som man får ved UUIDv4. Ved å inspisere versjonsnummeret av GUIDen generert her får man opp at dette er UUIDv1, og her starter mye av hvorfor akkurat SQL Server er litt spesiell.

For å hente ut versjonsnummer må man lese ut bit 48 til 51, men siden UUID er Big-Endian og .Net er Little-Endian, blir dette fra bit 56 til bit 60:

‍

UUIDv1 i RFC9562 kan man se at i likhet med UUIDv7 så er ikke denne versjonen helt tilfeldig, og har tid i starten, man skulle derfor tro at disse er kompatible med hverandre. Hovedforskjellen ligger derimot i at UUIDv7 avsluttes med tilfeldig data, mens UUIDv1 har noe som heter node (node er en unik adresse per datamaskin).

SQL Server sin uniqueidentifier type kan derfor virke til å være optimalisert for UUIDv1, selv om det her er veldig vanskelig å finne noe dokumentert fra Microsoft. Det som derimot er dokumentert er hvordan GUIDs i SQL Server og i .Net ikke sorteres på samme måte. Se https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/comparing-guid-and-uniqueidentifier-values

‍

Hva kan man gjøre?

Dersom man lagrer en UUIDv7 som CHAR(36) eller BINARY(16), så vil man ikke få fragmentering. Det samme vil gjelde for ULID. Ved å bruke CHAR får man en del ekstra data som kan være uheldig.

En annen ting man kan gjøre er å generere en UUIDv7, for så å bytte om de første og siste bytes i GUIDen før man lagrer som en uniqueidentifier. Ved min testing fungerte dette fint med å bytte de første 6 bytesene med de 6 siste. Dette er tilsvarende hva RT.Comb gjør.

.Net biblioteker for å generere SQL Server vennlige GUIDs:

UUIDNext, GUID blir generert som UUIDv8 som tillater brukerdefinert data i alle bits med unntak av versjon og variant.

https://github.com/mareek/UUIDNext

RT.Comb, GUID blir generert som UUIDv4, men hvor man bryter standarden med å putte dagens dato i de siste 6 bytes på GUIDen.

https://github.com/richardtallent/RT.Comb

‍

Hvordan teste?

For å teste bør man først fylle inn noe data i databasen for å få et mer realistisk grunnlag, f.eks. 1 million rader kan legges inn via bulk insert. Denne trenger pakken ‘Microsoft.Data.SqlClient’.

Etter dette kan man legge inn 10 000 rader med vanlig insert.

På dette tidspunktet bør man kunne se eventuell fragmentering ganske så godt. For å sjekke fragmentering kan man bruke en spørring som:

Fragmentering:

‍

Om du har tanker, innspill eller bare ønsker å nerde om temaet, ta kontakt med meg på oddbjorn@enso.no

-Oddbjørn