There’s something about GUIDs

There is a popular practice among developers to use globally unique identifiers (GUIDs) as primary keys.  At first glance, it seems like a great solution.  They are guaranteed to be unique and can be generated anywhere, avoiding a round trip to the database just to get the unique key.  While this sounds great in theory, there are some serious performance issues that can be caused by this in a SQL Server database.   In this article we’ll take an in-depth look at just one of these.

The first and most damaging thing that GUIDs cause is severe index fragmentation.  To understand this, we need to review how clustered indexes work in SQL Server.  The clustered index for a table or view determines the physical order of the rows on a data page.  So we like to make sure we are clustering on a value that is continuously increasing, like an integer generated by the IDENTITY property or via a sequence.  By doing this we ensure that new data always gets added to the end of the table and pages are filled sequentially.  The very definition of a GUID is that it is not sequential.  So every time a new row is inserted, SQL Server has to move things around to make room for it in the correct place.  Not only is this an expensive operation, but a GUID will never be accessed in a range so we lose that benefit of a good clustering key as well.  If a page is full and we need to insert a row into it, we get a Page Split, meaning SQL Server has to allocate a new page, move the rows that spill off the original page to it, and then add our new row to the original page.

To illustrate this, let’s create a simple table with a UNIQUEIDENTIFIER data type as the clustering key, using the NEWID()  function to generate a GUID.   With CHAR data types for the AccountName and AccountDescr columns, these rows will be large so we can create at table with a lot of pages very quickly.  We’re also including an InsertOrder column so we can see how data gets moved around.

Source code

CREATE DATABASE TestDB
 GO
 
USE TestDB
 GO
 
CREATE TABLE Account (
     AccountID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWID()
     ,AccountName CHAR(50)
     ,AccountDescr CHAR(2000)
     ,InsertOrder INT IDENTITY(1,1)
 )
 GO

To view fragmentation as we go through this exercise, we’ll use the following query as we add data to the table.

Source code

SELECT 
     OBJECT_NAME(OBJECT_ID) AS ObjectName
     ,index_id
     ,index_type_desc
     ,page_count
     ,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('Account'),1,NULL,null)

If we execute this query  immediately after creating the table, we get the following results.  The table is empty so this makes sense.

Now let’s add some data.  We’ll start with just a single row.

Source code

INSERT Account DEFAULT VALUES
GO

As we add rows to the table, we’ll use the undocumented function sys.fn_PhysLocFormatter to see exactly where in the database the rows are going.  The function returns the database file, page, and slot on the page where the row exists. In the output below, we can see that our single row is on Database File 1, Page 275, Slot 0.  As we add more rows, we’ll be able to see the shuffling that is going on.

Source code

SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM Account

Executing our DMV query again and we see that with just a single row, we get just 1 page and no fragmentation.

Now let’s add 10 more rows.

Source code

INSERT Account DEFAULT VALUES
GO 10
 
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM Account ORDER BY 5
GO

You can visually see the fragmentation and re-ordering of the rows that has occurred here. The InsertOrder tells us the order the rows came into the table, but things have been shuffled around because of the GUID clustering key.  Looking at the index stats, we now have 6 pages in this table and we are over 83% fragmented.

The last thing we’ll look at here is the volume of page splits.  Let’s insert 1,000,000 rows this time and watch the page split activity while we’re doing it.  We’ll do that by monitoring the Page Splits / sec performance monitor counter while the inserts are running.

Source code

INSERT Account DEFAULT VALUES
GO 1000000

As you can see, we are averaging over 300 page splits per second. Pretty much every insert is a page split in this scenario.

Looking at the index stats, we have 499,975 pages and we are over 99% fragmented.  It’s pretty much impossible to avoid fragmentation in a situation like this.

So what’s the performance problem?

The main place you’ll see this problem manifest itself is in excessive disk I/O.  Because the data is scattered all over the place, doing things like getting multiple rows from a single page won’t usually happen.  So you’re seeing a 1:1 or worse ratio of rows to logical reads.

The other issue is that you are touching almost every page in your index over the course of a day.  We have a customer who contacted us because their differential backups were just as big as the full backup, even on the first day.  They were doing a full backup on Sunday night and differentials on Mon-Saturday.  Monday’s differential was just as big as the full.  They wanted to know why and if there is really a point to doing differential backups.

As soon as I heard their issue, I knew the next question to ask.  Are you using GUID’s for clustering keys?  Sure enough, every table in the database was clustered on a uniqueidentifier colulmn.  The way differential backups work is that they backup all the data pages that have changed since the last full backup.  So what this tells us is that pretty much every page in the database was being written to on a daily basis.  This was for a 1TB database.

How to solve it

I love performance engagements where the problems tend to be “low hanging fruit.”  Configuration issues, missing indexes, query tuning, etc.  At the other end of the spectrum is problems like this because there is not an easy fix.  It’s a core architectural issue that requires data model changes at the very foundation of the system.  I have one customer with this problem who now understands it but is not willing to fix it due to the cost.   We identified the problem almost four years ago and they haven’t been able to fix it yet.  And their performance problems just keep getting worse as their database continues to grow.

In SQL Server 2005, Microsoft came out with the NEWSEQUENTIALID() function to try to alleviate some of the pain if you happen to be generating the GUIDs in the database.  While it sounds like a great idea, there are two main problems.  First, they are not really sequential, at least not over the long term.  More importantly, most people who are using GUIDs are generating them on the client.

This is a great example of a problem that would be easy to avoid at design time when the database is first being created but is a huge undertaking to fix after you’re in production.