"Black Arts" Index Maintenance #1 – GUIDs v.s. Fragmentation by Jeff Moden

Jeff Moden explains why random GUIDs are misunderstood in database indexing and how to manage index maintenance to avoid fragmentation.

The slides and resources for this talk are available at csmore.info/on/moden They’re not the problem… We are! by Jeff Moden This is the first in a series of presentations about the “Black Arts” of Index Maintenance. It’s appropriate that this first presentation is about Random GUIDs because they’re one of the most misunderstood features in T-SQL there is. They’re incredibly useful and are actually the epitome of how everyone envisions an index should act like, especially for Clustered Indexes. I know what you’re thinking… “Wait… WHAT???!!!” You heard right… GUIDs have the perfect index behavior that we all expect. So, what about all the massive fragmentation they cause? It’s not them… it’s us. We keep using tools that actually set up Random GUID indexes for massive page splits and the resulting fragmentation. We drank the Kool-Aid® because someone a long time ago decided that what was written in the Microsoft documentation was somehow a “Best Practice” and it’s actually a WORST PRACTICE! In this extended duration (about 100 minutes) presentation, you’ll be re-introduced to what people claim are problems with Random GUIDs and how they’re generally not a problem. Then you’ll be re-introduced to some of the incredible advantages of using Random GUIDs. And, finally, you learn the incredibly simple right way to maintain them (just lowering the Fill Factor is not enough). We’ll also compare Random GUID indexes to classic ever-increasing IDENTITY-based indexes and you’ll also discover that NEWSEQUENTIAL_ID has all of the problems of IDENTITY columns, has none of the incredible advantages (you heard right) that Random GUIDs have, and have only one saving grace that’s actually caused by the built-in security risk they have. Even if you’re not interested in all of that, you’ll be taught how to make Random GUID indexes go for weeks and even months with (get this) almost no page splits and less than 1% fragmentation. You’ll also learn why you should stop following the supposed “Best Practice” Index Maintenance recommendations. Bring your water-cooled helmet because this session is going to burn your brain big-time. Speaker Bio: Jeff Moden is a mostly self-taught accidental DBA and Database Developer. He calls SQLServerCentral his “home” (he has more than 53 thousand posts and, as he says, “Some are even useful”), has 38 mostly 5-star articles to his credit, has spoken at PASS Summit (RiP), many SQL Saturdays and local chapter meetings, coined the term “RBAR”, helped make the “Tally or Numbers” Table a much more common term, and loves to teach. His mantra is “Performance is in the code”.