The choice of the in-row LOB length affects the storage space used for the data pages, LOB pages, and the number of rows that can fit on a data page:
Specifying an in-row LOB length greater than the logical page size is ineffective, as only LOB values that are smaller than a page size are considered for in-row storage. Conversely, specifying a very small in-row LOB value may move very few LOB columns in-row, and not yeild the potential savings in LOB storage space.
A typical in-row LOB length is somewhere between the range of the minimum data length of a LOB column and the logical page size. A large in-row length value may fill an entire data page with just one row, so a practical useful value lies close to the average data length of the off-row LOB column where the length is less than a page size.
The choice of the in-row LOB length can also potentially affect scan performance for queries that return large numbers of rows, and that do not reference LOB columns. If very few rows fit on the data page due to large in-row LOB values, then the number of data pages scanned might be very big, thereby slowing query responses.
Examine the data lengths in your tables to estimate the in-row LOB length so that more than just one or two rows fit on the page. Balance performance impacts against the reduced LOB storage.
Identifying in-row LOB length selection
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
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.
