Out-of-Row Storage for varchar(max): Keeping Data Pages Small

Out-of-row storage moves varchar(max) values off the main data pages, so queries that skip the text read far fewer pages. The price is that queries that need the text must follow a pointer.

The status report that reads too much

Picture a table with an ID, a status and a big text payload. Someone runs a simple count by status. It should be instant. But the payload sits in the same rows, so SQL Server drags every payload through memory just to count rows.

That is the problem out-of-row storage solves. The table option is called “large value types out of row”. It applies to varchar(max), nvarchar(max), varbinary(max) and xml. Let me build a small table and watch the page counts. The demo creates a database and drops it at the end.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.LobDemo
(
    ItemId  int PRIMARY KEY CLUSTERED,
    StatusId int,
    Payload varchar(max)
);

INSERT dbo.LobDemo (ItemId, StatusId, Payload)
SELECT value, 1, REPLICATE(CAST('x' AS varchar(max)), 3000)
FROM GENERATE_SERIES(1, 100);

SELECT N'before' AS phase, in_row_data_page_count, lob_used_page_count,
       row_overflow_used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.LobDemo') AND index_id = 1;

Each payload is 3,000 characters, small enough to live in the row. So we see 50 in-row pages, no LOB pages and no row-overflow pages. Two rows per page, 100 rows, 50 pages.

Read before you change anything

Here are the two queries I care about: a narrow report that ignores the payload, and a wide one that reads it. STATISTICS IO shows the pages each one touches. Look at the Messages tab.

SET STATISTICS IO ON;

SELECT COUNT_BIG(*) AS status_rows
FROM dbo.LobDemo
WHERE StatusId = 1;

SELECT SUM(CONVERT(bigint, DATALENGTH(Payload))) AS payload_bytes
FROM dbo.LobDemo
WHERE StatusId = 1;

SET STATISTICS IO OFF;

The count returns 100 and the payload query returns 300000 bytes. Both scan the whole table, and both show 52 logical reads, because the narrow query still has to walk past the payloads.

Turn the option on, and notice nothing

Now change the option and measure again. This is the step people skip, and it is why they think the option did not work.

EXEC sys.sp_tableoption N'dbo.LobDemo', N'large value types out of row', N'ON';

SELECT N'option only' AS phase, in_row_data_page_count, lob_used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.LobDemo') AND index_id = 1;

Still 50 in-row pages and zero LOB pages. The option affects values as they are written, not values already stored. Existing rows stay where they are.

Rewrite the values and compare

To move the data, write it again. Setting a column to itself counts as a write. The rebuild then gives back the pages that the old rows left empty.

UPDATE dbo.LobDemo SET Payload = Payload;
ALTER INDEX ALL ON dbo.LobDemo REBUILD;

SELECT N'after rewrite' AS phase, in_row_data_page_count, lob_used_page_count,
       row_overflow_used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.LobDemo') AND index_id = 1;

SET STATISTICS IO ON;

SELECT COUNT_BIG(*) AS status_rows
FROM dbo.LobDemo
WHERE StatusId = 1;

SELECT SUM(CONVERT(bigint, DATALENGTH(Payload))) AS payload_bytes
FROM dbo.LobDemo
WHERE StatusId = 1;

SET STATISTICS IO OFF;

Now one in-row page holds all 100 narrow rows, and 55 LOB pages hold the payloads. Check the Messages tab again. The count drops from 52 logical reads to 3, with zero LOB reads. The payload query still returns 300000, but it now shows 3 logical reads plus 200 LOB logical reads.

Decide with your real query mix

The trade is clear. Narrow queries get much cheaper, and queries that read the text get more expensive: 52 reads became 3 plus 200. If most of your traffic skips the payload, this is a good deal. If most queries read it, you only added hops.

A covering nonclustered index is another way to make the narrow report cheap, and it does not move any data. Compare both on your own workload. Test the rewrite on a copy of your table first. It logs every row, so watch log space and plan the way back too.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Before you move a payload, count how often your queries actually read it.

Out-of-row storage is not compression, it is a different home for the payload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.