Performance Concerns with UniqueIdentifiers (GUIDs)

Question

I am a software developer/architect at my company and occasionally our DBA whenever our primary DBA quits, so I think I kind of know what I'm doing, but this has me stumped as to the best course of action.

We are working on moving a monolithic system into microservices. As part of this endeavor we've decided to use event streaming via Kafka. The problem we are facing is that we need to be able to come up with identifiers for any entity we create before we can send anything to Kafka (Kafka needs that identifier to keep any events for that entity ordered). These identifiers will need to be created on multiple machines that host our client facing applications (websites and APIs). So using a uniqueidentifier / GUID seems like a logical choice here.

If we were building this from scratch I wouldn't worry about this so much yet because we could separate various kinds of entities into their own data stores (users could be in one store / database, teams could be in another, etc.) which would keep the number of rows in a given table fairly well bounded and the querying load could be spread across multiple servers. However, our current system was poorly designed and every entity in our system lives in one large, very denormalized table (so any given row could represent a user, a team, a service, etc.). This table has somewhere between 8 million and 9 million rows in it. We also have to keep this table updated with any new data during our rewrite (which includes new GUIDs). And that's what has me worried.

We need to look these entities up by these identifiers and keep queries fast. And I know GUIDs make for horrible keys (for clustering or indexes) because they lead to huge fragmentation, page splitting, etc. We have a part of our existing system that uses GUIDs as a lookup for an entity. With a few hundred thousand rows the indexes fragmented to the point of uselessness and queries were relegated to table scans. We eventually were able to change the code to use sequential unique identifiers, but that isn't an option here. That makes me worried that using random GUIDs as identifiers is going to become a performance nightmare.

So the question is, is there a way I can use a random GUID as a non-clustering key on a large-ish table in such a way that keeps lookups via those GUIDs fast? What kinds of things can I do to mitigate the problem? I'm also open to alternative solutions here because we haven't reached a point of no return.

Answer 1

I would do something like the below example. Keep an integer (or bigint if you need it) as an identity column and the clustering key. This will keep the table neatly ordered with most recently added rows at the end, preventing fragmentation of your largest part of the table (the clustered index).

Then create a unique (if you need it to be unique) non-clustered index on the External ID (the random GUID), with a larger fill factor (to minimize fragmentation between re-org's).

This should make lookups on the ExternalID fairly fast (narrow index) and keep that index quickly re-organizable. You can play with the fill factor if needed to make room for your daily load.

EDIT (advice): You may need to keep an eye on the execution plans for this. If you ask for a small number of ExternalID's at once then it should be fine, but asking for lots of them may cause SQL to decide that a table scan is the best idea; which probably won't be.

CREATE TABLE dbo.LargishTable
    (
    ID INT NOT NULL IDENTITY(1,1)
    , ExternalID UNIQUEIDENTIFIER NOT NULL
    , OtherColumns NVARCHAR(MAX)
    , CONSTRAINT PK_LargishTable PRIMARY KEY CLUSTERED (ID)
    )

GO

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_LargishTable_ExternalID
    ON dbo.LargishTable (ExternalID)
    WITH (FILLFACTOR=70, SORT_IN_TEMPDB=ON)

GO

Answer 2

Generate new sequential GUIDs on the client end. This will reduce the frequency of page splits to a more manageable level.

In C# on Windows, the following will generate 100 sequential UUID values:

using System;
using System.Runtime.InteropServices;

namespace GUIDtest
{
    class Program
    {
        [DllImport("rpcrt4.dll", SetLastError = true)]
        static extern int UuidCreateSequential(out System.Guid guid);

        static void Main(string[] args)
        {
            Guid guid; ;
            for(var a = 0; a < 100; a++)
            {
                _ = UuidCreateSequential(out guid);

                Console.WriteLine($"guid: {guid}");
            }
        }
    }
}

Note that for cases where the security of the generated guid is not of paramount importance it's ok to generate GUIDs using this methodology since they are still going to be for all intents-and-purposes unique. However, if the generated guid will be used to obfuscate publicly visible parameters in a URL, for example, be aware that this method uses the MAC address of the computer running the code to generate a portion of the address, thus guaranteeing its uniqueness, but also providing a path to predict future GUIDs. The one gotcha here would be ensure you aren't running this code in multiple virtual machines configured to use the same MAC addresses (they would need to be on different networks for that to even work, so this is unlikely to be a problem).

Answer 3

is there a way I can use a random GUID as a non-clustering key on a large-ish table in such a way that keeps lookups via those GUIDs fast? What kinds of things can I do to mitigate the problem?

This table has somewhere between 8 million and 9 million rows in it.

Your best option here is to scale your database server. There's noting intractable about managing a table with 10 million rows with random GUID as the clustered key, it's just going to require more resources than a better design would.

Lookups on the GUID are going to be fast all the rows are cached in memory. Using a nonclustered index on the GUID won't really help if you need all the other columns, and introduces an extra layer of indirection.

Also you can look at generating sequential GUIDs on the client with either UUIDCreateSequential + byte shuffling, or other techniques.

Answer 4

First of all your table doesn't sound really large to me (based on the number of entries). Sizeable, but well within the limits that an index should work well enough even with non-optimized VARCHAR representation (depending on your requirements and server hardware of course).

That being said the problem for performance is mostly due to a) UUIDs being generated completely random (and thus not having a sequential ordering) and b) being stored as VARCHARs rather than in a more compact way (this blows up the index size). There are (inofficial) UUID variants that are sequential (there is a natural order that helps avoid constant index re-ordering to them). See here for instance, a java library that has a bit of an overview over (semi-) official ones and non-official uuid variants. UUID V6 for instance is time-ordered and thus plays nice with database systems, but since it arguably be enumerated you could have security concerns in principle. You could pick one of those and store the field in a compact format (in MySQL for instance BINARY is recommended and there are special methods to convert UUIDs from binary to their varchar representation for convenience).

However, since SQL server has the NEWID and particular NEWSEQUENTIALID methods to generate a GUID I would hope this is already optimized (sequential and stored in a compressed way). NEWSEQUENTIALID should at least be explicitly introduced to address the ordering issue. So maybe check that out and use the DB suggested approach.

From my perspective many recommendations against using UUIDs in databases come from ignorance and doing it wrong. There are drawbacks but they can mostly be reduced to a very small margin if done right and there are general non-performance advantages - potentially depending on application requirements. Here is for instance a performance analysis (for MySQL though) comparing using the "official" UUIDs in the wrong way as PK vs. using a custom UUID that has nice sequential properties vs. an approach that uses a BIGINT as PK and has the UUID as a separate field. Note that this only addresses INSERT speed as this is typically one of the biggest concerns.