“Black Arts” Index Maintenance – LOBs – Defragmented by Default

Jeff Moden

This is NOT your typical session on indexes.

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.

In this session, 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”.