0

I'm trying to understand why a GUID clustered index produces high fragmentation / page splits?

user3104183
  • 408
  • 1
  • 9
  • 26
  • 1
    It doesn't, necessarily. Whether it does depends on *how* you're generating the `GUID` values. If you're using `NEWSEQUENTIALID()` or it's moral equivalents (e.g. `UuidCreateSequential`) then your assertion is incorrect. – Damien_The_Unbeliever Jan 08 '14 at 07:30

1 Answers1

3

The data type is never the root cause for fragmentation. Fragmentation arises when you add additional data onto pages which do not have space free. This leads to a page split.

There are 2 common reasons for this:

  1. Inserts at random places at random places in the B-tree
  2. Updates increasing the size of a row (at random places in the B-tree)

Guids are usually case (1), except if generated in a sequential way (NEWSEQUENTIALID).

usr
  • 168,620
  • 35
  • 240
  • 369