UNIQUEIDENTIFIER & Clustered index

Mani 2014-08-21 11:26:39

Tried to browse if there is any existing demonstration available on the potential problems caused by the mixture of these two.

Would be great if someone could point me where this is exhibited in easy to understand fashion.

Thanks,
Mani.

Mani 2014-08-21 15:42:29
Thanks All, appreciated. I was also looking for samples where this is demonstrated like queries showing how the fragmentation occurs as you INSERT records into tables.
rknight 2014-08-21 16:23:40
That's pretty simple to illustrate. Something like this.

create table t1 (guid uniqueidentifier default newid())
 
insert t1 default values
go 100000

You could watch fragmentation change while running that or even easier, just monitor page splits using PerfMon. They'll be off the charts.

Or come to my lightning talk at SQLPASS this year. 🙂

ed 2014-08-21 11:52:10
The first problem is:

Depending on how you are creating the UNIQUEIDENTIFIER, if you are using NewID or generating the guid then you will get lots of index fragmentation and lots of page splits as the id's will be anywhere in the index depending on what guid you get.

If you are using sequential id's then you won't get that.

What you want is to have a sequential id so you know all writes go to the end of the index and won't cause earlier page splits.

The second problem is that uniqueidentifiers are quite large and if they are the clustered index, all other indexes also include those so it is slower to use all indexes whether you include the uniqueidentifier or not.

ed

SQLSaurus 2014-08-21 12:06:06
Expanding on ed's answer, you should read Kimberly's blog post here: http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
Aaron Bertrand 2014-08-21 13:57:23
The only thing I'll add is that in extreme cases you may want to cluster on a GUID in order to distribute inserts across many pages and avoid a hotspot. Thomas Kejser is a very big proponent of that (but I think he sometimes forgets that the scenario that needs it is pretty rare, like 6-7 figures of inserts per second, and doesn't address how to deal with all the trickle-down effects of clustering that way).
Mani 2014-08-23 20:52:16
Thanks all!!