SQL Server fragmentation

re: SQL Server fragmentation

Anthony Bloesch — Sat, 20 Dec 2008 00:21:02 GMT

I know the COMBs approach has a following but I believe UuidCreateSequential is a more solid approach especially 6 years after the original article. UuidCreateSequential has great properties and conforms to the UUID standard http://www.itu.int/ITU-T/studygroups/com17/oid.html. If you like the essential idea of COMBs the standard defines how to do it so you have a low chance of UUIDs colliding.

Jimmy Nilsson is right to urge caution around bechmarks. The best approach for performance critical code is to implement it and test with realistic workloads. In my insert tests ints and bigints have 5 times the throughput of sequential GUIDs and sequential GUIDs have 50 times the throughput of nonsequential GUIDs. In my lookup tests ints and bigints have 20% better throughput than sequential GUIDs and sequential GUIDs have 50% better throughput than nonsequential GUIDs. Because sequential GUIDs compress well you also get an added boost by turning on row compression.

re: SQL Server fragmentation

Chris — Fri, 19 Dec 2008 22:35:55 GMT

What are your thoughts on COMBS in Jimmy Nilsson's article "The Cost of GUIDs as Primary Keys"?

http://www.informit.com/articles/article.aspx?p=25862&seqNum=7

re: SQL Server fragmentation

Anthony Bloesch — Fri, 19 Dec 2008 20:08:08 GMT

If you can, use the SQL Server function newsequentialid() in the default clause of the primary key. Because these are allocated sequentially, they minimize fragmentation. If the GUIDs are allocated externally in the middle-tier then having the middle-tier use the Win32 API UuidCreateSequential will minimize fragmentation. I do not think there is a .Net equivalent so you need to wrap the call. In VB:

Declare Function UuidCreateSequential Lib "rpcrt4.dll" (ByRef id As Guid) As Integer

in C#

[DllImport("rpcrt4.dll", SetLastError=true)]

static extern int UuidCreateSequential(out Guid guid);

re: SQL Server fragmentation

Chris — Fri, 19 Dec 2008 01:02:48 GMT

If one project requires GUIDs what is the best way to use them without causing a lot of page fragmentation? Is using internal GUID better than external?