PASS logo

November 14-17

In person. In Seattle

2021 Summit video library

“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”.

Get updates

Sign up to get the latest conference information, announcements and price bump reminders direct to your inbox.

Redgate will only contact you about PASS Data Community Summit and SQL Saturday, unless you separately request emails about Redgate.