Identity Performance in Microsoft SQL Server

A twitter post about using hi-lo in Entity Framework Core got me thinking about performance impact to inserts when choosing different ways of generating IDs. I was surprised at the results.

The performance falls into 4 quadrants:

Test setup:

• BenchmarkDotNet v0.15.2, Linux Ubuntu 24.04.3 LTS (Noble Numbat)
• 13th Gen Intel Core i7-1370P 5.20GHz, 1 CPU, 20 logical and 14 physical cores
• .NET SDK 9.0.109
• Runtime: .NET 9.0.8 (9.0.825.36511), X64 RyuJIT
• Concurrent Server GC
• SQL Server 2019 for Linux running in docker on the test machine.
• Query Store enabled in SQL Server with wait stats turned on.

  ALTER DATABASE IdentityPerformance SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, WAIT_STATS_CAPTURE_MODE = ON)
  

• IterationCount=10

Commonly, an identity column is used to autogenerate sequential integer ids, so this was my baseline.

id bigint not null identity(1,1) primary key`

Since I’m using Entity Framework Core, we’ll let EF take care of it.

entity.Property(e => e.Id).UseIdentityColumn();

For hi-lo, the application generates the id. Well, sort of. The implementation in Entity Framework Core uses a sequence with an increment value of the hi-lo range. so there is a database component involved in generating ids. However, the application assigns the id to the record and inserts the record with the id. For this, we just need an id column in the database.

id bigint not null primary key`

And of course, the sequence:

create sequence dbo.hilo_test_seq
    start with 1
    increment by 100
go

In EF, we have:

modelBuilder.HasSequence<long>("hilo_test_seq").StartsAt(1).IncrementsBy(100);
 entity.Property(e => e.Id).UseHiLo("hilo_test_seq");

For the database generated UUIDs, we’ll use a default constraint to call the SQL Server functions:

entity.Property(e => e.Id).ValueGeneratedOnAdd().HasDefaultValueSql("newid()");
...
entity.Property(e => e.Id).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");

For the application set UUIDs, we’ll just let EF know IDs will never be generated, though this is more to express intent:

entity.Property(e => e.Id).ValueGeneratedNever();

Setting the id’s was was just a simple:

Id = Guid.NewGuid()
...
Id = Ulid.NewUlid().ToGuid()

Yes, we could create a custom value generator that sets these in the application so devs don’t have to explicitly set the value when creating an instance. For simplicity, I just assign it on construction for the tests.

I tried to simulate concurrent inserts into the same table by multiple client connections by utilizing a Parallel.For:

    const int itemsCount = 20;
    const int parallelism = 10;

    [Benchmark]
    public void InsertIdentityTest()
    {
        Parallel.For(1, parallelism, t =>
        {
            var context = Setup();
            for (int i = 0; i < itemsCount; i++)
            {
                context.IdentityTests.Add(new IdentityTest
                {
                    Name = $"Test {i}",
                    Email = $"test{i}@example.com"
                });
            }
            
            context.SaveChanges();
        });
    }

This gives me 10 concurrent db connections/clients each inserting 20 records into the table.

The Results

I suspect this has to do with the how SQL Server deals with identity columns and unique values internally. Both identity and NEWSEQUENTIALID() need to perform checks to ensure the generated value is unique. This results in very high wait times, specifically on BUFFER LATCH. Total wait time for each scenario:

Conclusion

For insert-heavy applications, consider UUID. If read performance is also critical, application generated ULIDs can provide a happy medium. As with anything else performance related, test the options in your own product environment. Every system has different data and access patterns. Just because a raw insert with no business context is faster in this test, that doesn’t mean it is the optimal solution for you application.