I’ve had the unique opportunity recently to talk with a few different companies about their technical challenges. During one of those conversations, GUIDs as primary keys came up.
I wasn’t really prepared to speak at length about it at the moment but I think I mentioned that the primary key defaults to the clustered key and traditional SQL Server tribal knowledge is that GUIDs as clustered keys are a bad idea. The primary key defaults to the clustered key. GUIDs are big: 16 bytes versus 8 for a BIGINT versus 4 for an INT. GUIDs are random, random causes fragmentation, and fragmentation could cause issues. I wrapped it all up by saying that using GUIDs as primary keys aren’t preferable, I’ve created systems with GUIDs as PKs, but I wouldn’t ideally start there.
But then I had a chance to think about it. In the year of Rizgod, 2026, does traditional SQL Server tribal knowledge hold up? Can you use GUID as a primary key and the system purr like a kitten? Does a super-fragmented clustered key bloat the clustered index size to the point where we need to resurrect Richard Simmons?
I decided to stop guessing and crack open SSMS and put it to the test. Now my test platform is a 2019 Macbook Pro running SQL Server 2022 on a Windows 11 VM. I think we can all agree that this isn’t the optimal SQL Server setup and your db in a data center is going to be a whole lot more performant.
The Tribes
So I wanted to test four different scenarios:
- INT as primary key and clustered key
- BIGINT as primary key and clustered key
- GUID with NEWID as primary key and clustered key
- GUID with NEWSEQUENTIALID as primary key and clustered key
Along with the key, each table gets a TestNumber column holding a random integer (so we can hang a nonclustered index on it) and a TestText column holding 100 characters of nothing in particular, so there’s some actual data on the page.
-- INT Table
DROP TABLE IF EXISTS TestInt;
CREATE TABLE TestInt (
Id INT IDENTITY(1,1) NOT NULL ,
TestNumber INT NOT NULL ,
TestText VARCHAR(100) NOT NULL ,
CONSTRAINT PK_TestInt PRIMARY KEY CLUSTERED (Id)
);
CREATE INDEX IX_TestInt_TestNumber ON TestInt(TestNumber);
-- BIGINT Table
DROP TABLE IF EXISTS TestBigInt;
CREATE TABLE TestBigInt (
Id BIGINT IDENTITY(1,1) NOT NULL ,
TestNumber INT NOT NULL ,
TestText VARCHAR(100) NOT NULL ,
CONSTRAINT PK_TestBigInt PRIMARY KEY CLUSTERED (Id)
);
CREATE INDEX IX_TestBigInt_TestNumber ON TestBigInt(TestNumber);
-- GUID RANDOM
DROP TABLE IF EXISTS TestGuidRandom;
CREATE TABLE TestGuidRandom (
Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ,
TestNumber INT NOT NULL ,
TestText VARCHAR(100) NOT NULL ,
CONSTRAINT PK_TestGuidRandom PRIMARY KEY CLUSTERED (Id)
);
CREATE INDEX IX_TestGuidRandom_TestNumber ON TestGuidRandom(TestNumber);
-- GUID Sequential
DROP TABLE IF EXISTS TestGuidSeq;
CREATE TABLE TestGuidSeq (
Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() ,
TestNumber INT NOT NULL ,
TestText VARCHAR(100) NOT NULL ,
CONSTRAINT PK_TestGuidSeq PRIMARY KEY CLUSTERED (Id)
);
CREATE INDEX IX_TestGuidSeq_TestNumber ON TestGuidSeq(TestNumber);And one little table to keep score:
DROP TABLE IF EXISTS _Results;
CREATE TABLE _Results (table_name sysname, rows_loaded int, load_ms int);Physical Challenge
Now we load 10,000,000 rows into each table, 1,000 rows at a time, and look at load times, page counts, index size, fragmentation, and logical reads on a SELECT. Here’s the load for TestInt. Same pattern for the other three.
DECLARE @RowCount INT = 10000000, @BatchSize INT = 1000;
DECLARE @loaded INT = 0, @start DATETIME2(3) = SYSUTCDATETIME();
WHILE @loaded < @RowCount
BEGIN
INSERT INTO TestInt (TestNumber, TestText)
SELECT ABS(CHECKSUM(NEWID())) % 1000000, REPLICATE('x',100)
FROM GENERATE_SERIES(1, @BatchSize);
SET @loaded += @BatchSize;
END
DELETE _Results WHERE table_name = 'TestInt';
INSERT _Results VALUES ('TestInt', @RowCount, DATEDIFF(ms,@start,SYSUTCDATETIME()));GENERATE_SERIES is a SQL Server 2022 thing and it needs your database at compatibility level 160 or higher. If it throws an error at you, that’s why.
Tribal Council
Ten million rows in each table. Let’s see who’s still holding a torch.
| Table Name | Load Time | Rows/Sec |
|---|---|---|
| TestInt | 00:04:00 | 41,525 |
| TestBigInt | 00:05:15 | 31,731 |
| TestGuidRandom | 00:08:49 | 18,882 |
| TestGuidSeq | 00:05:22 | 31,003 |
Unsurprisingly, INT wins, BIGINT follows. What is surprising is that the sequential GUID is right there on BIGINT’s shoulder. That’s 31,003 rows/sec versus 31,731. Random GUIDs are the slowest by a mile.
Before you panic: this is a 10-million-row bulk load. You are probably not doing that on a Tuesday. When your app saves one order, nobody’s stopwatch is going to notice the difference. The load column is the least interesting number on this page.
Now let’s look at the clustered indexes, which is where things get spicy.
| Index Name | Page Count | Size MB | Frag % | Page Fullness % |
|---|---|---|---|---|
| PK_TestInt | 151,516 | 1,183 | 0.37 | 98.64 |
| PK_TestBigInt | 156,250 | 1,220 | 0.47 | 98.81 |
| PK_TestGuidRandom | 241,371 | 1,885 | 99.22 | 68.05 |
| PK_TestGuidSeq | 166,667 | 1,302 | 0.67 | 98.57 |
Now we’re getting a picture. INT and BIGINT are basically twins. GUID Sequential is a little chunkier, about 10% more than INT, and honestly I’d call that negligible. GUID Random is a horse of a different color. It’s 60% bigger than INT. That’s 702 extra megabytes for the exact same 10 million rows.
But Isn’t 99% Fragmentation Terrible?
Most of us would look at that 99.22% and yell “fragmentation!” And I get it, that number is enormous. But it’s the wrong number to be yelling about.
There are two kinds of fragmentation and they don’t cost you the same thing.
External fragmentation is that 99.22%, the avg_fragmentation_in_percent column. Your pages are out of order on disk. On modern storage, or on anything that lives in the buffer pool, this mostly doesn’t matter.
Internal fragmentation is the column that’s overlooked, avg_page_space_used_in_percent. It’s free space sitting on your pages. GUID Random is 68% full while everybody else is at 98%, and since SQL Server caches a page at a time and not a row at a time, you’re paying RAM prices for that empty space. That’s where the 702 MB went. Not the ordering. The whitespace.
If you want the long version of why external fragmentation stopped mattering, go read Brent’s post. It’s from 2012 and it has aged like a fine wine.
And if you still think that 99% external number is the villain, look at what happened to the nonclustered indexes we built on TestNumber:
| Index Name | Page Count | Size MB | Frag % | Page Fullness % |
|---|---|---|---|---|
| IX_TestInt_TestNumber | 26,464 | 206.75 | 99.20 | 65.33 |
| IX_TestBigInt_TestNumber | 32,846 | 256.61 | 99.17 | 67.68 |
| IX_TestGuidRandom_TestNumber | 46,706 | 364.89 | 99.25 | 68.75 |
| IX_TestGuidSeq_TestNumber | 47,002 | 367.20 | 99.21 | 68.32 |
Every one of them is 99% externally fragmented and sitting around 65% full. Including the INT. TestNumber is a random value, so of course they are. We shoved random keys into a sorted structure and it did exactly what you’d expect.
The Thing Nobody Told You About GUIDs
Look at that table again. Same 10 million rows, same column, same index definition.
INT: 26,464 pages. GUID Random: 46,706 pages. GUID Sequential: 47,002 pages.
The nonclustered index on the GUID tables is 77% bigger. And notice that NEWSEQUENTIALID didn’t save you here. It’s just as big. Fragmentation didn’t do this. The clustered key did.
Every row in every nonclustered index carries the clustered key as its row locator. Swap a 4-byte INT for a 16-byte GUID and you just added 12 bytes to every row of every nonclustered index on that table. On every index you’ll ever create.
Do The Reads Care?
Let’s find out. These queries force a clustered index scan with WITH (INDEX(1)), because index 1 is always the clustered index. I have to force it, because that nonclustered index on TestNumber actually covers this query, and left to its own devices SQL Server would happily grab the smaller index and dodge the test entirely. Rude.
We’ll also flush the buffer pool before each one so everybody starts cold.
SET STATISTICS IO ON; SET STATISTICS TIME ON;
CHECKPOINT; DBCC DROPCLEANBUFFERS;
SELECT COUNT_BIG(*) c, SUM(CONVERT(bigint,TestNumber)) s FROM TestInt WITH (INDEX(1));
CHECKPOINT; DBCC DROPCLEANBUFFERS;
SELECT COUNT_BIG(*) c, SUM(CONVERT(bigint,TestNumber)) s FROM TestBigInt WITH (INDEX(1));
CHECKPOINT; DBCC DROPCLEANBUFFERS;
SELECT COUNT_BIG(*) c, SUM(CONVERT(bigint,TestNumber)) s FROM TestGuidRandom WITH (INDEX(1));
CHECKPOINT; DBCC DROPCLEANBUFFERS;
SELECT COUNT_BIG(*) c, SUM(CONVERT(bigint,TestNumber)) s FROM TestGuidSeq WITH (INDEX(1));The CHECKPOINT matters. DROPCLEANBUFFERS only drops clean buffers, so dirty pages stay right where they are and your cold-cache test isn’t as cold as you think. And obviously, don’t run this on production unless you enjoy difficult conversations, because it torches the buffer pool for the entire instance.
| Query Table | Logical Reads |
|---|---|
| TestInt | 154,326 |
| TestBigInt | 160,653 |
| TestGuidRandom | 243,222 |
| TestGuidSeq | 173,388 |
INT, BIGINT and GUID Sequential are all in the same neighborhood. But our kooky friend NEWID reads roughly 60% more pages to produce the exact same two numbers. More pages in, more work done, same answer out.
Now It’s Time To Vote
So that’s it, right? Sequential GUIDs are fine, NEWID is a little piggy, stick with INT if you can. Roll credits.
Except I skipped something. How long did those scans actually take?
| Query Table | Time Elapsed |
|---|---|
| TestInt | 00:00:00.811 |
| TestBigInt | 00:00:00.719 |
| TestGuidRandom | 00:00:02.320 |
| TestGuidSeq | 00:00:00.899 |
INT, BIGINT, and sequential GUID all scan 10 million rows in under a second. BIGINT even nosed out INT this run, which tells you exactly how much daylight there is between those three. I am comfortable with any of them as a clustered key. Ship it.
The NEWID table takes over two seconds. Nearly three times the time for the same answer. As a query tuner, that hurts me somewhere deep. But here’s what I’m not going to do. I’m not going to march into a meeting and demand we rip out the primary key of a system that’s already running and paying the bills. The juice is not worth the squeeze.
Immunity Idol
What we can do though is change the clustered key if we really have to. We can create a INT identity column on the table, keep the primary key on the GUID NEWID, and create a clustered index on the new INT identity column.
DROP TABLE IF EXISTS TestGuidNotClustered;
CREATE TABLE TestGuidNotClustered (
Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ,
IntClusteredKey INT NOT NULL IDENTITY (1,1) ,
TestNumber INT NOT NULL ,
TestText VARCHAR(100) NOT NULL ,
CONSTRAINT PK_TestGuidNotClustered PRIMARY KEY NONCLUSTERED (Id)
);
CREATE CLUSTERED INDEX IX_TestGuidNotClustereds_IntClusteredKey ON TestGuidNotClustered (IntClusteredKey);
CREATE INDEX IX_TestGuidNotClustered_TestNumber ON TestGuidNotClustered(TestNumber);That gives us three indexes: a clustered index on IntClusteredKey, a nonclustered index on TestNumber, and a nonclustered index backing the primary key on Id. Yes, we added an index.
| Index Name | Page Count | Size MB | Frag % | Page Fullness % |
|---|---|---|---|---|
| IX_TestGuidNotClustereds_IntClusteredKey | 172,414 | 1346 | 0.37 | 99.58 |
| PK_TestInt | 151,516 | 1,183 | 0.37 | 98.64 |
There it is. External fragmentation is back down to 0.37%, and the pages are 99.58% full, which is actually fuller than the INT table. The internal fragmentation is gone. We’re not renting memory to store empty space anymore.
Let’s look at the query that scans the clustered key with the new clustered index we just created.
| Query Table | Time Elapsed |
|---|---|
| TestGuidNotClustered | 00:00:00.996 |
| TestInt | 00:00:00.811 |
| TestGuidSeq | 00:00:00.899 |
We took a NEWID table from 2.3 seconds down to 0.996 and landed within spitting distance of the sequential GUID. We didn’t touch a single line of application code, we didn’t renumber anything, we didn’t call a migration meeting. We moved an index.
The Jury Has Questions
Now let me argue the other side, because I’ve spent this whole post picking on GUIDs and they deserve a lawyer.
People choose GUIDs for good reasons. The application can generate the key itself, without a round trip to the database. This is great for applications with deep object hierarchies. Records from twenty different systems merge without a collision. Nobody can sit there incrementing /orders/1024 in the address bar to read somebody else’s order. None of those wins show up in a page count, and none of them are available to you with an identity column.
NEWSEQUENTIALID has its own asterisks. It’s only sequential until the server restarts, at which point it may start a fresh range at a lower value and split pages for a while until it catches up. It’s also predictable, which quietly undoes one of the reasons you wanted a GUID in the first place.
Don’t worry I’m never going to practice law.
Sole Survivor
The tribal knowledge isn’t wrong. It’s just louder than it needs to be.
- INT, BIGINT, and NEWSEQUENTIALID all scan 10 million rows in under a second. Pick any of them and sleep fine.
- NEWID as a clustered key costs you 60% more pages, 60% more reads, and 2-3x the scan time.
- A GUID clustered key, sequential or not, makes every nonclustered index on the table about 77% bigger. That’s the bill nobody mentions.
- If you’re stuck with NEWID, you don’t need a rewrite. Cluster on an INT identity, leave the primary key alone, go home on time.
So the next time you’re at tribal council and someone says the GUIDs have to go, you can tell them exactly why torch stays lit.
If you want to go into a DEEP dive into SQL Server indexes where you’ll learn more about clustered indexes I highly recommend to check out Brent Ozar’s Mastering Index Tuning course.
Leave a Reply