Black Arts Index Maintenance #3: LOBs -- Defragmented by Default

This is NOT your typical presentation on Index Maintenance or LOBs.

With the advent of SQL Server 2005, Microsoft made it easier than ever to store and use LOBs by introducing the MAX datatypes. Never before has it been so easy to manage huge amounts of non-relational data and we can do it all without having to use pointers like we did with the old TEXT, NTEXT, and IMAGE datatypes.

They also made a not-so-well advertised change that seriously affects the way that LOBs are handled and stored by default. Most people don’t even know this change occurred and, for those that do, most are unaware of the ramifications and rather extreme collateral damage this seemingly innocuous change has caused.

1. Non-Lob (which is most of our queries) Clustered Index Queries run twice as slow and require two orders of magnitude more memory to do so.
2. Rampant “bad” page splits, which can lead to serious blocking.
3. Permanent and perpetual fragmentation of Clustered Indexes.
4. Increased data storage requirements.
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.

Fixing any one of those problems would be a big help.

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

Jeff Moden is an “Accidental DBA” with more than two decades of SQL Server experience. 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”.