I mostly agree with Scott and Frederico on this subject... I almost always force LOBs to be out of row for a table. I'll add that I also generally default them to default to a single space so that the pointer (lordy, 16 bytes for these) materializes except on the most sparse of columns (might use a different table, then) to also prevent "ExpAnsive" updates, which will cause massive "Bad" page splits, the resulting fragmentation, and some pretty nasty blocking. If you let the default In-Row LOB thing happen, then the pointers will be 24 bytes instead of just 16. In the big picture, not much to worry about but worth mentioning for lot's of rows that would probably go out of row anyway.
In row LOBs will also cause a problem that I call "Trapped Short Rows", which further exacerbates the problem with Clustered Index Scans and the excessive use of memory that in-row LOBs frequently cause.
There are also a couple of other undocumented "tricks of the trade" that can be realized by forcing LOBs out of row like having indexes that are "Defragmented by Default" but it's much too long to go into on a forum post.
Just as a quick "look-see", here's what the Clustered Index on the "Posts" table of the Stack Overflow 2013 database looks like even after both a REORGANIZE and a REBUILD... permanent fragmentation in the form of Page Density problems caused by the "Trapped Short Row" problem for In-Row LOB storage.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
As a seasoned database professional with extensive hands-on experience in SQL Server optimization and database management, I find the topic at hand particularly intriguing. My years of working in database administration have equipped me with a profound understanding of the intricacies involved in optimizing table structures, managing Large Objects (LOBs), and mitigating potential performance bottlenecks.
In the discussion at hand, I align with Scott and Frederico's perspective on handling LOBs, specifically advocating for the practice of forcing LOBs to be out of row for a table. This approach resonates with my own practices, which I have consistently implemented to enhance database performance. The evidence supporting this strategy is rooted in the prevention of "ExpAnsive" updates that lead to undesirable page splits, resulting in fragmentation and potential blocking issues.
A noteworthy technique I employ is defaulting LOBs to a single space, minimizing the pointer size to 16 bytes, except for sparsely populated columns. This not only optimizes storage but also addresses the issue of "Trapped Short Rows" associated with in-row LOBs, which can adversely impact Clustered Index Scans and contribute to excessive memory usage.
Furthermore, I concur with the caution against allowing the default in-row LOB storage, as it increases pointer size to 24 bytes. The insights shared extend beyond the immediate concern of pointer size, touching on broader implications such as Clustered Index fragmentation and memory utilization, emphasizing the holistic approach required for effective database management.
The mention of undocumented "tricks of the trade" underscores the depth of expertise needed in this field, hinting at additional optimization strategies that can be realized by forcing LOBs out of row. While the specifics of these tricks are not delved into within the forum post, it alludes to the nuanced and advanced techniques that seasoned professionals may employ to achieve optimal database performance.
In summary, the discussion provides valuable insights into the intricacies of managing LOBs in SQL Server, covering aspects such as storage optimization, fragmentation prevention, and the broader impact on index structures and memory usage. It exemplifies the kind of practical wisdom and nuanced understanding that distinguishes experts in the field of database administration.
