2

I'm watching Paul S Randal's index fragmentation video at here. In the video, Paul said that GUID or other random values as high-order key will cause page splits which in turn will cause index fragmentation. The reason is that, random values means the new records will be inserted into random pages, which will probably cause page split on random page. My question: if the new records are not inserted into random pages, page split still can occur, right? If this is true, what's the difference in between the two cases?

BTW, does high-order key mean primary key? I'm not a native English speaker and not sure of it.

Thanks.

Just a learner
  • 26,690
  • 50
  • 155
  • 234

2 Answers2

3

The index data is clustered on the keys used. The "high-order" here is mentionned because indexes can be composite, and the first member is the most significant for the order.

So if this member is random (and a GUID has to be seen as random, even if crypto guys don't like this term for GUIDs) you'll get much more frequent splits than if you insert consecutive values, even if the values are not at the end of the range.

(That's also why using NEWSEQUENTIALID() instead of NEWID() helps a lot for the insert performance when using a GUID for a clustered primary key).

Lucero
  • 59,176
  • 9
  • 122
  • 152
1

If your keys are sequential, then the data will all be appended at the end, meaning new pages will be created as necessary. This will however create a "hotspot" where data is being pushed in a high volume system. The solution to this is to have a clustered index across some kind of categorisation field, then the incrementing primary key.

cjk
  • 45,739
  • 9
  • 81
  • 112
  • Not really. A hot spot means long cache life expectancy, while a categorisation field means writes and splits like a GUID would. "It depends" is more correct... – gbn Feb 11 '11 at 17:38
  • Yes... really. While a "hot spot" certainly does include "long cache life expectancy", it's also the place of all the insert activity, which keeps it in the cache. Because everyone doing an insert is hitting the same page and that effort is completely stopped with it comes time to do a supposed "good" page split, a "hot spot" can lead so some seriously slow inserts. There are tons of articles on the subject including some that give you multiple methods to avoid such a hot spot. – Jeff Moden Mar 29 '22 at 01:05