11

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.

  • 1
    How are the GUIDs generated? NEWID or NEWSEQUENTIALID? – swasheck Oct 31 '13 at 16:31
  • 6
    Clustered guid and insert performance should only be in a sentence if the word immediately preceding "performance" is minimize – billinkc Oct 31 '13 at 16:31
  • 2
    Take those developers out for lunch and explain to them that if they use NEWID() again as a primary key you will blame poor performance on them. They will very quickly ask you what to do to prevent that. At which point, you say use IDENTITY(1,1) instead. (perhaps a slight oversimplification but 9 times out of 10 that will work). – Max Vernon Oct 31 '13 at 16:35
  • 3
    Reason for our hatred of guid is that they are wide (16 bytes) and when not created with newsequentialid are random. Clustered keys are best when they are narrow and increasing. A GUID is the opposite: fat and random. Imagine a bookshelf nearly full of books. In comes the OED and because of the of randomness of guids, it inserts in the middle of the shelf. To keep things ordered, the right half of the books have to get punted into a new location which is a time intensive task. That's what the GUID is doing to your database and killing performance. – billinkc Oct 31 '13 at 16:37
  • 7
    The way to fix the problem of using uniqueidentifiers is to go back to the drawing board and not use uniqueidentifiers. They aren't terrible if the system is small, but if you have at least a few million+ row tables (or any table larger than that), you're flat out going to get crushed using uniqueidentifiers for keys. – Jon Seigel Oct 31 '13 at 17:27
19

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:

12

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:

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.

4

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: http://google-opensource.blogspot.in/2011/04/introducing-cityhash.html - make sure you get all the compile steps right, or the FNV1a variant of http://tools.ietf.org/html/draft-eastlake-fnv-03 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.

  • 2
    I suggest you edit your answer as an answer to the OP's question and not (as it is now) as an answer to Thomas' answer. You can still highlight the differences between Thomas (, MikeFal's) and your suggestion. – ypercubeᵀᴹ Jan 10 '14 at 14:49
  • 2
    Please address your answer to the question. If you don't we'll remove it for you. – JNK Jan 10 '14 at 15:36
  • 2
    Thanks for the comments Mark. When you edit your answer (which I think provides some very good context) I would change one thing: IDENTITY doesnt require an additional round trip to the server if you are careful with the INSERT. You can always return SCOPE_IDENTITY() in the batch that calls the INSERT.. – Thomas Kejser Jan 10 '14 at 20:28
  • 1
    Regarding "it's horribly slow as it requires a round trip to the DB to create a key" - you can grab as many as you need in one round trip. – A-K Feb 1 '14 at 21:26
  • Regarding "you can grab as many as you need in one round trip" - You can't do this with IDENTITY columns or any other method where you're basically using DEFAULT on the database level. – Avi Cherry Mar 17 '15 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.