Benchmarking Index Fragmentation With Popular Sequential GUID Algorithms


In a multi-tenant data insert scenario, GUID is a great data type for primary key. Since it is unique globally, you will not need to rely on any range or custom logic. However, if you extend these columns as index, you will end up with frequent index fragmentation and hence bad performance. In this post, I will be benchmarking index fragmentation with popular sequential Guid algorithms.

Option 1 (GUID using NewGuid): In this option, we will create a GUID from the application and then insert the same into the table. We will use this as the base benchmark for the comparison. We are already aware of the fact that this option ends up in huge defragmentation. This option is no different from calling NewId() function at SQL.

Option 2 (Sequential GUID using SQL’s NewSequentialId): In this option, we will let the SQL to create the sequential GUID using the inbuilt function.

Option 3 (Sequential GUID based on UuidCreateSequential): In this option, we will write an algorithm which is equivalent to NewSequentialId. This will use the UuidCreateSequential; a COM system method. NewSequentialId also shares the same COM method. This algorithm is posted by David Browne (fellow MS employee) and his original post could be found at https://blogs.msdn.microsoft.com/dbrowne/2012/07/03/how-to-generate-sequential-guids-for-sql-server-in-net/

Option 4 (GUID based on NHibernate’s GuidCombGenerator): In this option, we will use the algorithm NHibernate uses to generate a GUID for certain features. Many developers have claimed to use this algorithm to overcome the issue we are discussing. The source code for the same could be found at https://github.com/nhibernate/nhibernate-core/blob/3087d48640eb64f573c0011e9a9b1567afe6adde/src/NHibernate/Id/GuidCombGenerator.cs

 

Output

There was latency of 220ms to the Azure data center from my machine.

Option 1
(Guid using NewGuid)
Option 2
(Sequential Guid using SQL’s NewSequentialId)
Option 3
(Sequential Guid based on UuidCreateSequential)
Option 4
(Guid based on NHibernate’s GuidCombGenerator)
Duration 346s 300s 343s 359s
Application Memory

 

22 – 23MB (No Leak) 17 – 19MB (No Leak) 21 – 23MB (No Leak) 21 – 23MB (No Leak)
Application CPU (8 Core)

 

< 1% < 1% < 2% < 2%
DTU Consumption (DTU / CPU / Data / Log)

 

62 / 44 / 0 / 62 56 / 29 / 0 / 56 47 / 42 / 0 / 47 59 / 41 / 7 / 59
Pages (Clustered Index / Non-Clustered Index)

 

14335 / 13467 9901 / 10527 9901 / 10527 15868 / 14994
Page Density % (Clustered Index / Non-Clustered Index)

 

68.9 / 68.75 99.8 / 99.73 99.8 / 99.73 66.16 / 65.89
Fragmentation % (Clustered Index / Non-Clustered Index) 99.27 / 99.03 1.87 / 0.76 1.88 / 0.77 91.66 / 91.49

 

If you are interested in the complete benchmark process, code I have used and my suggestion, please visit my full article posted at http://microsoftprogrammers.jebarson.com/benchmarking-index-fragmentation-with-popular-sequential-guid-algorithms/

Comments (0)

Skip to main content