Indexing a PK GUID in SQL Server 2012

Question

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's.

The system is relatively young and our biggest tables are just over a million rows, but we're taking a look at our indexing and want to be able to scale quickly as it may be needed in the near future.

So, my first inclination was to move the clustered index to the created field which is a bigint representation of a DateTime. However, the only way I can make the CX unique would be to include the GUID column in this CX but order by created first.

Would this make the clustering key too wide and would it boost performance for writes? Reads are important too, but writes are probably a bigger concern at this point.

Answer 1

The primary problems with GUIDs, especially non-sequential ones, are:

• Size of the key (16 bytes vs. 4 bytes for an INT): This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index.
• Index fragmentation: It is virtually impossible to keep a non-sequential GUID column defragmented because of the completely random nature of the key values.

So what does this mean to your situation? It comes down to your design. If your system is simply about writes and you have no concern about data retrieval, then the approach outlined by Thomas K is accurate. However, you have to keep in mind that by pursuing this strategy, you're creating many potential issues for reading that data and storing it. As Jon Seigel points out, you will also be occupying more space and essentially having memory bloat.

The primary question around GUIDs is how necessary they are. Developers like them because they ensure global uniqueness, but it's a rare occasion that this kind of uniqueness is necessary. But consider that if your maximum number of values is less than 2,147,483,647 (the maximum value of a 4 byte signed integer), then you're probably not using the appropriate data type for your key. Even by using BIGINT (8 bytes), your max value is 9,223,372,036,854,775,807. This is typically enough for any non-global database (and many global ones) if you need some auto-incrementing value for a unique key.

Finally, as far as using a heap versus a clustered index, if you are purely writing data a heap would be most efficient because you are minimizing overhead for inserts. However, heaps in SQL Server are extremely inefficient for data retrieval. My experience has been that a clustered index is always desirable if you have the opportunity to declare one. I have seen the addition of a clustered index to a table (4 billion+ records) improve overall select performance by a factor of 6.

Additional information:

• GUIDs as PRIMARY KEYs and/or the clustering key - Kimberly L. Tripp
• Disk space is cheap... - Kimberly L. Tripp (hat tip Zane )
• GUID vs INT Debate – Microsoft SQL Server Tips & Tricks

Answer 2

There is nothing wrong with GUID as keys and clusters in an OLTP system (unless you have a LOT of indexes on the table that suffer from the increased size of the cluster). As a matter of fact, they are much more scalable than IDENTITY columns.

There is a widespread belief that GUID are a great problem in SQL Server - largely, this is quite simply wrong. As a matter of fact, GUID can be significantly more scalable on boxes with more than about 8 cores:

• http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/
• http://www.microsoft.com/en-gb/download/details.aspx?id=26665 (See section on last page/trailing)
• http://dangerousdba.blogspot.co.uk/2011/10/day-sequences-saved-world.html

I am sorry, but your developers are right. Worry about other things before you worry about GUID.

Oh, and finally: why do you want a cluster index in the first place? If your concern is an OLTP system with a lot of small indexes, you are likely better off with a heap.

Let us now consider what fragmentation (Which the GUID will introduce) does to your reads. There are three major problems with fragmentation:

1. Page splits cost disk I/O
2. Half full pages are not as memory efficient as full pages
3. It causes pages to be stored out of order, which makes sequential I/O less likely

Since your concern in the question is about scalability, which we can define as "Adding more hardware makes the system go faster" these are the least of your problems. To address each one in turn

Ad 1) If you want scale, then you can afford to buy I/O. Even a cheap Samsung/Intel 512GB SSD (at a few USD/GB) will get you well over 100K IOPS. You wont be consuming that any time soon on a 2 socket system. And if you should run into that, buy one more and you are set

Ad 2) If you do deletes in your table, you will have half full pages anyway. And even if you don't, memory is cheap and for all but the largest OLTP systems - the hot data should fit there. Looking to pack more data into pages is sub-optimising when you are looking for scale.

Ad 3) A table built out of frequently page split, highly fragmented data does random I/O at exactly the same speed that a sequentially filled tables does

With regards to joining, there are two major join types you are likely to see in an OLTP like workload: Hash and loop. Lets look at each in turn:

Hash join: A hash join assumes that the small table is scanned and the bigger one is typically seeked. Small tables are very likely to be in memory, so I/O is not your concern here. We already touched on the fact that seeks are the same cost in fragmented index as in a non fragmented index

Loop join: The outer table will be seeked. Same cost

You may also have lots of bad table scanning going on - but then GUID is again not your concern, proper indexing is.

Now, you may have some legitimate range scans going on (especially when joining on foreign keys) and in this case, the fragmented data is less "packed" as compared to the non fragmented data. But let us consider what joins you will be likely to see in well indexed a 3NF data are:

1. A join from a table that has a foreign key reference to the primary key of the table it references

2. The other way around

Ad 1) In this case, you are going for a single seek to the primary key - joining n to 1. Fragmentation or not, same cost (one seek)

Ad 2) In this case, you are joining to the same key, but may retrieve more than one row (range seek). The join in this case is 1 to n. However, the foreign table you seek, you are seeking for the SAME key, which is just as likely to be on the same page in a fragmented index as on a non fragmented one.

Consider those foreign keys for a moment. Even if you had "perfectly" sequential laid our primary keys - anything pointing to that key will still be non sequential.

Of course, you may be running on a virtual machine in some SAN in some bank who is cheap on money and high on process. Then all this advise will be lost. But if that is your world, scalability is probably not what you are looking for - you are looking for performance and high speed/cost - which are both different things.

Answer 3

Thomas : some of your points make complete sense and I agree with them all. If you are on SSDs, the balance of what you optimise for does change. Random vs sequential is not the same discussion as spinning disk.

I especially agree that taking a pure DB view is horribly wrong. Making your application slow and unscalable to improve just the DB performance can be quite misguided.

The big issue with IDENTITY (or sequence, or anything generated in the DB) is that it's horribly slow as it requires a round trip to the DB to create a key, and this automatically makes a bottleneck in your DB, it enforces that applications must make a DB call to start using a key. Creating a GUID solves this by using the application to create the key, it's guaranteed to be globally unique (by definition), and the application layers can thus use it to pass the record around BEFORE incurring a DB round-trip.

But I tend to use an alternative to GUIDs My personal preference for a datatype here is a globally unique BIGINT generated by the app. How does one go about doing this? In the most trivial example, you add a small, VERY lightweight function to your app to hash a GUID. Assuming your hash function is fast and relatively quick (see CityHash from Google for one example - make sure you get all the compile steps right, or the FNV1a variant of FNV for simple code) this gets you the benefit of both application generated unique identifiers and a 64 bit key value that CPUs work better with.

There are other ways of generating BIGINTs, and in both these algos there is a chance of hash collisions - read and make conscious decisions.