This is NOT your typical session on indexes.

tldr: If you use any LOBs (MAX datatypes, XML, or even big VARCHARS()) in your database, they're probably killing performance and wasting huge amounts of buffer memory and it's totally unnecessary. We're going to fix all that and make some of your indexes "Defragmented by Default" in the process using a couple of very simple "tricks".

Moere detailed...

If you have LOBs in your tables, there's a really good chance that they're seriously slowing down your queries, using possibly hundreds of times more buffer memory than you need to, causing massive page splits and the resulting fragmentation, and permanently reducing the page density of a huge portion of your clustered indexes to less than 50% and to as little as 1% because of permanently "Trapped Short Rows" that you can't do anything about unless you know "the trick". No amount of defragging will fix it either.

When Microsoft introduced the MAX and XML data-types, they also made a mostly unknown change as to how the LOBs are handled. The hidden collateral damage caused by the change is huge…

1. Non-LOB queries (most of our queries) run twice as slow and require two orders of magnitude more memory.
2. Rampant “bad” page splits, which leads to serious blocking.
3. Permanent physical fragmentation of Clustered Indexes.
4. Seriously Increased memory and disk usage.
5. Increased and totally unnecessary Index Maintenance.
6. Increased log file activity, which also affects query performance, backup storage requirements, and increased backup and restore times.

SQL Server MVP Veteran Jeff Moden shows us how and why the change causes all of these problems and then demonstrates how two simple changes to our tables fix it all. He also demonstrates how the same techniques can be used to make some non-LOB tables “Defragmented by Default”.