Black Arts Index Maintenance Jeff Moden

Black Arts” Index Maintenance – GUIDs vs Fragmentation - Jeff Moden

Details
This is NOT your typical presentation on the fragmentation problems of Random GUIDs. No… Instead we’re going to DESTROY THE MYTH OF RANDOM GUID FRAGMENTATION.

In one of the most ironic/heterodoxical turns of knowledge you’re ever likely to experience, we’ll see how THE USE OF RANDOM GUIDS CAN ACTUALLY PREVENT FRAGMENTATION! In the end, you’ll witness the results of some simple testing that clearly demonstrate that you can easily insert literally MILLIONs of rows into a Random GUID clustered index with almost no page splits (not even supposed “good” ones) and LESS THAN 1% Logical fragmentation!

We’ll identify the real problem and the seriously effective yet incredibly simple two-part fix for it. In the process, we’ll prove that RANDOM GUIDS ACTUALLY BEHAVE IN A MANNER LIKE MOST PEOPLE EXPECT A GOOD INDEX TO BEHAVE, especially in but not limited to high performance OLTP environments as well as the benefits of doing so.

We’ll also learn how to use a new tool that I created (included in the ZIP file) to ACTUALLY SEE WHAT AN INDEX LOOKS LIKE at the page level for all pages in a single graph.

Then, we’ll use that tool to LAY WASTE TO WHAT PEOPLE ARE CURRENTLY CALLING “BEST PRACTICE” INDEX MAINTENANCE. We’ll literally see what REORGANIZE does to an index at the page level and why it’s one of the very worst things you can do to your Random GUID and many other types of indexes even if you’re limited to the Standard Edition of SQL Server. We also prove that REORGANIZE CAUSES ORDERS-OF-MAGNITUDE WORSE TRANSACTION LOG FILE USAGE THAN REBUILD especially for Random GUIDs.

As interesting and totally necessary sidebars, we'll also see how the use of ever-increasing index keys could be (and frequently is) a major source of many of your fragmentation problems and we’ll also see that NEWSEQUENTIALID IS PROBABLY NOT THE ANSWER THAT YOU’RE LOOKING FOR.

Speaker
Jeff Moden
Jeff Moden is an “Accidental DBA” with more than two decades of SQL Server experience and is the fellow who coined the term “RBAR”. He’s written 39 mostly five-star articles for SQLServerCentral.com and, with more than 53,000 posts, is one of the leading contributors on that site. Jeff is renowned for being able to teach advanced and complex concepts and techniques in such a manner that even beginners can understand without boring the experts. He’s also a 9-year SQL Server MVP veteran, won the Red Gate exceptional DBA award in 2011, and has spoken at the PASS Summit, many SQL Saturdays, and many local PASS chapter meetings on many different SQL Server and T-SQL subjects.

His mantra is “Performance is in the code”.