Identifying In-Row LOB Length Selection

Identify in-row LOB length selection to estimate the different amounts of LOB storage savings.

1. Identify the minimum, maximum and average data row size for the table with LOBs being stored off-row:

   1> select i.minlen, t.datarowsize, i.maxlen
   2> from sysindexes i, systabstats t
   3> where i.id = object_id('DYNPSOURCE')
   4>   and i.indid in (0, 1)
   5>   and i.id = t.id
   6> go

    minlen datarowsize                 maxlen
   ------  --------------------------- ------
    9                       105.000000    201

2. Compute the mininum, average, and maximum data lengths for the off-row column of interest:

   1> select datalength(FIELDINFO) as fieldinfo_len into 
          #dynpsource_FIELDINFO
   2> from DYNPSOURCE
   3> where datalength(FIELDINFO) < @@maxpagesize
   4> go

   (65771 rows affected)

   1> select minlen = min(fieldinfo_len), avglen = avg(fieldinfo_len), 
          maxlen = max(fieldinfo_len)
   2> from #dynpsource_FIELDINFO
   3> go

    minlen     avglen      maxlen
   ----------- ----------- -----------
           536        7608       16080

   Out of a total of about 190,000 rows in the DYPNSOURCE table, approximately 65000 of them had the off-row LOB column DYPNSOURCE such that its data length was well within the logical page size of 16K.

   By choosing an in-row LOB length that lies between the minlen or avglen in the above output, different numbers of off-row LOBs can be brought in-row, thereby providing different amounts of LOB storage savings.