SQL Server database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUIDs?

Question

We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out GUIDs as PKs altogether, which we cannot do (at least not at this time)).

• We could change the GUID generation algorithm to e.g. the one that NHibernate uses, as detailed in this post, or
• we could, for the tables that are under the heaviest use, change to a different clustered index, e.g. an IDENTITY column, and keep the "random" GUIDs as PKs.

Is it possible to give any general recommendations in such a scenario?

The application in question has 500+ tables, the largest one presently at about 1,5 million rows, a few tables around 500 000 rows, and the rest significantly lower (most of them well below 10K).

Furthermore, the application is installed at several customer sites already, so we have to take any possible negative effects for existing customer into consideration.

Thanks!

Answer 1

My opinion is clear: use an INT IDENTITY for your clustering key. That's by far the best, most optimal clustering key, because its:

• small
• stable (should never change)
• unique
• ever increasing

Sequential GUID's are definitely a lot better than regular random GUIDs, but there's still four times larger than an INT (16 vs 4 byte) and this will be a factor if you have lots of rows in your table, and lots of non-clustered indices on that table, too. The clustering key is being added to each and every non-clustered index, so that significantly increases the negative effect of having 16 vs 4 bytes in size. More bytes means more pages on disk and in SQL Server RAM and thus more disk I/O and more work for SQL Server.

You can definitely keep the GUID as the primary key, where appropriate - but in that case, I'd strongly recommend adding a separate INT IDENTITY to that table and make that INT the clustering key. I've done that myself with a number of large tables, and the results are astonishing - the table fragmentation is down from 99 and more percent down to a few percent, and performance is much better.

Check out Kimberly Tripp's excellent series on why GUID's are bad as clustering keys in SQL Server here:

• GUIDs as PRIMARY KEYs and/or the clustering key
• The Clustered Index Debate Continues...
• Ever-increasing clustering key - the Clustered Index Debate..........again!

Marc

Answer 2

If you are able to change your guid generation to a sequential guid generation easily then that is probably your quick win option. The sequential guid will stop the fragmentation on the table whilst remaining as your clustered index. The major downside with a sequential guid though is that they then become guessable which is often not desired and the reason guids are used in the first place.

If you go down the Identity route for your clustered primary key and then just an index on your guid column then you will still get a lot of fragmentation on your guid index. However the fact that the table will no longer get fragmented will be a massive gain.

Finally though, I know you said you can't do this for now, but, if you don't NEED to use guids as an index at all then you remove all of these problems.