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
