Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
1  
Why are you using BIGINT to represent a DATETIME? Why do you think the CX needs to be unique? –  Aaron Bertrand 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
6  
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

3 Answers 3

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:

http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/ (Hat tip Zane )

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx

share|improve this answer
    
No problem I was planning on making a similar post when I got home. Glad you beat me to it as you are more articulate than I am. –  Zane Jan 9 at 22:00

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 no 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.

share|improve this answer
5  
@ThomasKejser are you talking just about scaling an extremely large volume of writes, and disregarding anything else you might later do with the data? Most scenarios (at small to moderate volumes at least), and particularly on traditional storage (we're not all on Fusion-IO), benefit most on balance from a sequential clustered index, and I think you're one of the few who comes across edge cases where this no longer holds. In general I disagree that all OLTP systems are better off with heaps, and I think that's dangerous advice to throw around unless it is extremely qualified. IMHO. –  Aaron Bertrand Jan 9 at 21:29
6  
Scaling to 1M+ rows/sec is great, but this assumes many other things, like conserving disk and buffer pool space is irrelevant, and reading the data back is never done. Random inserts will fragment the data in the table. This advice applies to the clients you deal with, but probably not to most people's systems. –  Jon Seigel Jan 9 at 21:29
4  
@ThomasKejser most of my clients aren't even on commodity SSD. And a lot of them do perform scans, archive/delete data based on timestamp, use partitioning, etc. I agree that your ideas have merits, but I would use caution on generalizing and saying they are appropriate for all OLTP scenarios. Not all OLTP scenarios are the ones you would design - a lot of folks are stuck with what they're stuck with. What I fear is that people will see your advice, and start dropping all their clustered indexes. I'm sure that will improve their performance... –  Aaron Bertrand Jan 9 at 21:55
3  
@AaronBertrand: The advise on using clusters and IDENTITY is just as dangerous. Once you have a database design in place that hits the insert wall, it is very difficult to get out of it. I actually think it is a bit condescending to think that people will blindly take the advise - I trust humans to think for themselves when presented with the evidence (which I think is clearly in favour of non sequential keys) –  Thomas Kejser Jan 9 at 22:20
4  
My point is that most people aren't inserting at such a scale where they're ever going to hit that wall - and they are on systems where using GUIDs has such impacts on I/O and memory usage that it does matter. So, and again on balance and in general, heaps with NC GUID PKs are more dangerous to the average user. And I won't even get into the range of your audience on a site like this - while it is of much higher quality than SO, there are always going to be lemmings who will just do whatever "smart person A" said without considering whether it is actually appropriate for their scenario. –  Aaron Bertrand Jan 9 at 22:26

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.

share|improve this answer
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 at 14:49
2  
Please address your answer to the question. If you don't we'll remove it for you. –  JNK Jan 10 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 at 20:28
    
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. –  AlexKuznetsov Feb 1 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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