Ah... gotta disagree there (on the GUID stuff) especially since I'm in the process of doing some extreme testing with GUIDs for other purposes. One big thing to consider is that NEWSEQUENTIALID will create an INSERT "HotSpot" just like using an IDENTITY column or any other ever-increasing value will. The other thing to consider it that, and I quote from BOL...
After restarting Windows, the GUID can start again from a lower range...
... and it turns out that there's about a 50% chance that it WILL start at a lower number, which will cause the "bad" type of page splits that we all try to avoid (frequently using incorrect rebuild/reorg methods to do so worsening the problem in the process).
In contrast and using the correct FILL FACTOR (which isn't the waste you think it might be with GUIDs), you end up with 16 different INSERT points instead of just one like you will with an IDENTITY.
I still think that GUIDs have a couple of major "suck" factors, such as being a whole lot wider than an INT and those being included in every NCI and that they're a real PITA to read and type and the fact that they actually sort on what appears to be the last 6 bytes (which makes selecting a range of GUIDs a huge pain), but I no longer think of GUIDs as a form of "Death by Datatype" for performance with restpect to inserts to CIs or NCIs. With the correct FILL FACTOR AND THE RIGHT KIND OF INDEX MAINTENANCE WHICH DOES NOT FOLLOW THE RECOMMENDATIONS IN BOL OR OTHER "BEST PRACTICES" (which in secondary purpose of my current study), they actually have a significant performance advantage for huge numbers of singleton inserts compared to typical ever-ascending keys.
I'm also discovering that most peoples' index maintenance plans are seriously flawed and may actually be the cause of more performance problems than not doing any index maintenance at all. This isn't the result of some artificial, contrived, or hear-say recommendations. You've probably heard me say it before... I've not rebuilt indexes on my production box for almost 2 years and performance actually and significantly improved over the first 3 months and hasn't degraded since. Part of my current study is to identify and determine the remedy and guidelines for the very small handful of exceptions (tables that are regularly added to and then partially deleted from, for example). I'm also developing code where people can duplicate the non-insitu experiments that I've been doing to support both not doing index maintenance and how to actually get the most benefit if you feel compelled to do index maintenance. I've also discovered that ALTER INDEX REORGANIZE sucks so bad that it has its own gravitational pull.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)