Row Overflow Data: Finding Rows That Spill Past 8,060 Bytes

A table can declare several wide VARCHAR columns even when their combined maximum exceeds the 8,060-byte in-row limit. SQL Server can move variable-length values to row overflow data pages and leave pointers in the row. Reads that need those values can require extra page access, so measure the layout and the workload.

Where Row Overflow Data Comes From

A normal data row has limited in-row space. Variable-length columns can move out of row when actual values make the row too large. Declared maximum width alone does not prove an overflow page is used; a varchar(5000) column holding ten characters fits easily. The actual row values and other columns determine whether SQL Server stores a value on ROW_OVERFLOW_DATA pages.

I check the table design and the rows that are actually large. What query reads the overflowing columns? A narrow key lookup that never projects them can behave differently from a report that returns all three wide values for every row.

Create a Wide Lab Row

Use a disposable heap with three varchar columns. Each receives thousands of characters, making the combined value much larger than the in-row limit. Keep an ID column so the row can be found again. Do not run this against a production table just to demonstrate the mechanism.

DROP TABLE IF EXISTS dbo.RowOverflowDemo;
CREATE TABLE dbo.RowOverflowDemo
(
    ID int NOT NULL,
    PartA varchar(5000) NULL,
    PartB varchar(5000) NULL,
    PartC varchar(5000) NULL
);
INSERT dbo.RowOverflowDemo(ID,PartA,PartB,PartC)
VALUES (1,REPLICATE('A',4000),
          REPLICATE('B',4000),REPLICATE('C',4000));
SELECT ID, DATALENGTH(PartA) AS a_bytes,
       DATALENGTH(PartB) AS b_bytes,
       DATALENGTH(PartC) AS c_bytes
FROM dbo.RowOverflowDemo;

The row has 12,000 payload bytes before overhead, so some values need off-row storage. Which column moves is an engine layout decision, not a fixed rule that the last column always moves. Use allocation evidence rather than guessing from column order.

Count Row Overflow Data Pages in Allocation Units

sys.allocation_units has an allocation unit type for ROW_OVERFLOW_DATA. Join it to partitions through hobt_id for in-row and row-overflow units. The used and total page counts show whether the object has allocated overflow storage. A positive count is table-level evidence, not a list of which row or column moved.

SELECT i.name AS index_name,
       au.type_desc AS allocation_type,
       SUM(au.used_pages) AS used_pages,
       SUM(au.total_pages) AS reserved_pages
FROM sys.partitions AS p
JOIN sys.indexes AS i
  ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units AS au
  ON au.container_id = p.hobt_id
WHERE p.object_id = OBJECT_ID(N'dbo.RowOverflowDemo')
  AND au.type_desc IN (N'IN_ROW_DATA',N'ROW_OVERFLOW_DATA')
GROUP BY i.name, au.type_desc;

A heap has a NULL index name, so label by object and index ID in a production report. Allocation counters can change after deletes, rebuilds, and ghost cleanup. Take the sample close to the performance test, and do not interpret a reserved page as a current row count.

Confirm With Physical Stats

sys.dm_db_index_physical_stats can report page_count by allocation-unit type for the object and index. LIMITED mode is a lighter first pass; DETAILED mode can be expensive on a large table. Filter to the target object and inspect ROW_OVERFLOW_DATA beside IN_ROW_DATA.

SELECT index_id, partition_number,
       alloc_unit_type_desc, page_count,
       avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
     (DB_ID(),OBJECT_ID(N'dbo.RowOverflowDemo'),NULL,NULL,'LIMITED')
WHERE alloc_unit_type_desc IN
      (N'IN_ROW_DATA',N'ROW_OVERFLOW_DATA');

Some metrics are unavailable or less precise in LIMITED mode, so use page_count as a lead and check the view's documented behavior for the target version. Do not run DETAILED across every table during a slowdown. A targeted query is enough to establish that overflow storage exists.

Find Rows and Columns That Drive Width

DATALENGTH reports actual bytes in each value. Rank rows by combined lengths, then inspect the individual columns for the top IDs. The sum is a candidate finder, not a perfect predictor of off-row placement because row headers, null bitmap, variable-column metadata, and engine choices also consume space.

SELECT TOP (20) ID,
       DATALENGTH(PartA) AS a_bytes,
       DATALENGTH(PartB) AS b_bytes,
       DATALENGTH(PartC) AS c_bytes,
       COALESCE(DATALENGTH(PartA),0)
       + COALESCE(DATALENGTH(PartB),0)
       + COALESCE(DATALENGTH(PartC),0) AS total_value_bytes
FROM dbo.RowOverflowDemo
ORDER BY total_value_bytes DESC;

For a real table with many variable columns, generate the expression from sys.columns or profile likely columns in stages. Do not expose sensitive text while measuring widths. A few extreme rows can account for most overflow pages, while an average row looks modest.

Measure the Read Penalty

Run a query that selects only ID, then one that reads all three wide columns under SET STATISTICS IO. Compare logical reads, lob logical reads, the actual plan, and elapsed time. Overflow page reads are counted as lob logical reads; in my lab the wide query added one while the ID-only query added none. The extra access appears when the values are needed. A row-overflow pointer can add work to lookups and scans, but cache state and projection matter.

I test realistic predicates and output sizes. Moving a rarely read description into a separate table can help a hot narrow query; moving every wide column can create joins and complexity. The best fix follows the workload. If a query always needs the full value, changing storage layout does not make those bytes disappear.

Compare Narrow and Wide Projections

A row-overflow page matters when a query follows the pointer to retrieve the off-row value. Run one query that selects only ID and another that selects PartA, PartB, and PartC for the same qualifying row set. Capture STATISTICS IO and TIME, then compare logical reads and elapsed time. For a large test, use enough rows to avoid a one-page curiosity. Keep output rendering outside the timed comparison if the client spends more time drawing long strings than SQL Server spends reading them.

A covering index that includes a wide column can duplicate storage or create new LOB costs. Do not add it solely to remove a lookup without measuring write overhead and index size. The best design can be to keep rarely used text in a separate table keyed by ID, fetched only when the application opens a detail view.

Read Row Overflow Data Evidence Carefully

The combined DATALENGTH query ranks candidate rows, but it does not show which specific value SQL Server moved off-row. Inspect the actual plan and allocation units, then use a targeted page-inspection method in a lab if exact placement matters. Do not run undocumented page-inspection commands on production as a casual diagnostic. For most tuning decisions, table-level overflow evidence plus query-level extra reads is sufficient.

Variable-length data can later shrink after updates while allocation pages remain until cleanup or rebuild. Compare measurements at the same time and under similar data. A high ROW_OVERFLOW_DATA page count today can reflect historical wide values as well as current ones.

Plan a Targeted Fix

If a handful of rows are extreme, validate the input policy and decide whether those values belong in a different column or storage tier. If most rows overflow, reduce unnecessary widths, normalize optional attributes, or split frequently read narrow data from rarely read wide data. Every change has a cost in joins, application code, and migration time. Rehearse with real value lengths and compare before-and-after reads.

Related reading on this blog: 2005 Row Overflow Data Explanation and ANSI PADDING and Storage: SQL in Sixty Seconds 210.

Row overflow is not proved by a wide definition, it is confirmed by allocation and large-row evidence.

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