Is it possible to see LOB (large object) logical reads from STATISTICS IO output on a table with no LOB columns? I was asked this question today by someone who had spent a good fraction of their afternoon trying to work out why this was occurring – even going so far as to re-run DBCC CHECKDB to see if any corruption had taken place.
The table in question wasn’t particularly pretty – it had grown somewhat organically over time, with new columns being added every so often as
the need arose. Nevertheless, it remained a simple structure with no LOB columns – no TEXT or IMAGE, no XML, no MAX types – nothing aside from ordinary INT, MONEY, VARCHAR, and DATETIME types. To add to
the air of mystery, not every query that ran against
the table would report LOB logical reads – just sometimes – but when it did,
the query often took much longer to execute. Ok, enough of
the pre-amble. I can’t reproduce
the exact structure here, but
the following script creates a table that will serve to demonstrate
the effect: IF OBJECT_ID(N'dbo.Test', N'U')
IS NOT NULL
DROP TABLE dbo.Test
GO
CREATE TABLE
dbo.Test
(
row_id NUMERIC IDENTITY NOT NULL,
col01 NVARCHAR(450) NOT NULL,
col02 NVARCHAR(450) NOT NULL,
col03 NVARCHAR(450) NOT NULL,
col04 NVARCHAR(450) NOT NULL,
col05 NVARCHAR(450) NOT NULL,
col06 NVARCHAR(450) NOT NULL,
col07 NVARCHAR(450) NOT NULL,
col08 NVARCHAR(450) NOT NULL,
col09 NVARCHAR(450) NOT NULL,
col10 NVARCHAR(450) NOT NULL,
CONSTRAINT [PK dbo.Test row_id]
PRIMARY KEY CLUSTERED (row_id)
)
;
The next script loads
the ten variable-length character columns with one-character strings in
the first row, two-character strings in
the second row, and so on down to
the 450th row:
WITH Numbers
AS (
-- Generates numbers 1 - 450 inclusive
SELECT TOP (450)
n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3
ORDER BY
n ASC
)
INSERT dbo.Test
WITH (TABLOCKX)
SELECT REPLICATE(N'A', N.n), REPLICATE(N'B', N.n),
REPLICATE(N'C', N.n), REPLICATE(N'D', N.n),
REPLICATE(N'E', N.n), REPLICATE(N'F', N.n),
REPLICATE(N'G', N.n), REPLICATE(N'H', N.n),
REPLICATE(N'I', N.n), REPLICATE(N'J', N.n)
FROM Numbers AS N
ORDER BY
N.n ASC
;
Once those two scripts have run,
the table contains 450 rows and 10 columns of data like this:
Most of
the time, when we query data from this table, we don’t see any LOB logical reads, for example:
-- Find
the maximum length of
the data in
-- column 5 for a range of rows
SELECT result = MAX(DATALENGTH(T.col05))
FROM dbo.Test AS T
WHERE row_id BETWEEN 50 AND 100
;
But with a different query…
-- Read all
the data in column 1
SELECT result = MAX(DATALENGTH(T.col01))
FROM dbo.Test AS T
;
…suddenly we have 49 LOB logical reads, as well as
the ‘normal’ logical reads we would expect.
The Explanation
If we had tried to create this table in SQL Server 2000, we would have received a warning message to say that future INSERT or UPDATE operations on
the table might fail if
the resulting row exceeded
the in-row storage limit of 8060 bytes. If we needed to store more data than would fit in an 8060 byte row (including internal overhead) we had to use a LOB column – TEXT, NTEXT, or IMAGE. These special data types store
the large data values in a separate structure, with just a small pointer left in
the original row.
Row Overflow
SQL Server 2005 introduced a feature called row overflow, which allows
one or more variable-length columns in a row to move to off-row storage if
the data in a particular row would otherwise exceed 8060 bytes. You no longer receive a warning when creating (or altering) a table that might need more than 8060 bytes of in-row storage; if SQL Server finds that it can no longer fit a variable-length column in a particular row, it will silently move
one or more of these columns off
the row into a separate allocation unit.
Only variable-length columns can be moved in this way (for example
the (N)VARCHAR, VARBINARY, and SQL_VARIANT types). Fixed-length columns (like INTEGER and DATETIME for example) never move into ‘row overflow’ storage.
The decision to move a column off-row is done on a row-by-row basis – so data in a particular column might be stored in-row for some table records, and off-row for others.
In general, if SQL Server finds that it needs to move a column into row-overflow storage, it moves
the largest variable-length column record for that row. Note that in
the case of an UPDATE statement that results in
the 8060 byte limit being exceeded, it might not be
the column that grew that is moved!
Sneaky LOBs
Anyway, that’s all very interesting but I don’t want to get too carried away with
the intricacies of row-overflow storage internals.
The point is that it is now possible to define a table with non-LOB columns that will silently exceed
the old row-size limit and result in ordinary variable-length columns being moved to off-row storage. Adding new columns to a table, expanding an existing column definition, or simply storing more data in a column than you used to – all these things can result in
one or more variable-length columns being moved off
the row.
Note that row-overflow storage is logically quite different from old-style LOB and new-style MAX data type storage – individual variable-length columns are still limited to 8000 bytes each – you can just have more of them now. Having said that,
the physical mechanisms involved are very similar to full LOB storage – a column moved to row-overflow leaves a 24-byte pointer record in
the row, and
the ‘separate storage’ I have been talking about is structured very similarly to both old-style LOBs and new-style MAX types.
The disadvantages are also
the same: when SQL Server needs a row-overflow column value it needs to follow
the in-row pointer a navigate another chain of pages, just like retrieving a traditional LOB.
And Finally…
In
the example script presented above,
the rows with row_id values from 402 to 450 inclusive all exceed
the total in-row storage limit of 8060 bytes. A SELECT that references a column in
one of those rows that has moved to off-row storage will incur
one or more lob logical reads as
the storage engine locates
the data.
The results on your system might vary slightly depending on your settings, of course; but in my tests only column 1 in rows 402-450 moved off-row. You might like to play around with
the script – updating columns, changing data type lengths, and so on – to see
the effect on lob logical reads and which columns get moved when. You might even see row-overflow columns moving back in-row if they are updated to be smaller (hint: reduce
the size of a column entry by at least 1000 bytes if you hope to see this).
Be aware that SQL Server will not warn you when it moves ‘ordinary’ variable-length columns into overflow storage, and it can have dramatic effects on performance. It makes more sense than ever to choose column data types sensibly. If you make every column a VARCHAR(8000) or NVARCHAR(4000), and someone stores data that results in a row needing more than 8060 bytes, SQL Server might turn some of your column data into pseudo-LOBs – all without saying a word.
Finally, some people make a distinction between ordinary LOBs (those that can hold up to 2GB of data) and
the LOB-like structures created by row-overflow (where columns are still limited to 8000 bytes) by referring to row-overflow LOBs as SLOBs. I find that quite appealing, but
the ‘S’ stands for ‘small’, which makes expanding
the whole acronym a little daft-sounding…small large objects anyone?
© Paul White 2011
email:
[email protected]
twitter: @SQL_Kiwi
