Unveiling SQL Server Row Overflow: How Data Spills Across Pages

When Does Row Overflow Occur?

Row overflow in SQL Server is triggered only for variable‑length columns when the combined size of the row header, all fixed‑length columns, and the variable‑length column metadata exceeds the 8,060‑byte limit. To store data larger than 8,000 bytes you must use LOB types (text, ntext, image) or MAX types. Additionally, a variable column must be longer than 24 bytes because overflow requires a 24‑byte pointer; if it is 24 bytes or less, overflow is unnecessary. Finally, the variable column cannot be part of a clustered index key, as that would severely degrade query performance.

Storage Structure of Row Overflow

To illustrate the overflow layout, a table HeapPage_Overflow is created and populated with test data. The following image shows the raw page content for a row that contains an overflowed column:

The formatted row reveals several key fields:

0x2980 – offset to the overflowed variable column (after reversing bytes, the offset is 41, where the high two bits serve as a marker).

The first variable‑column offset (41) equals 17 bytes of system information plus a 24‑byte overflow pointer.

The 24‑byte overflow pointer has the following structure:

0x02 – type identifier indicating overflow data.

0x0000 – B‑tree level (always 0 for overflow records; 0x0100 for LOB root records).

0x00 – reserved.

0x01000000 – sequence number that increments on each update, used for optimistic concurrency.

0x804A0000 – timestamp used by DBCC CHECKTABLE to verify correct linking of indexes, row‑in‑row data, LOBs, and overflow pages.

0x70170000 – length of the overflowed field (after byte‑swap, 0x00001770 = 6,000 bytes, matching the inserted value).

0x5900000001000000 – an 8‑byte RID that points to the data page storing the 6,000‑byte overflowed column (e.g., RID (1:89:0)).

The overflow data itself resides on a separate page. The page view is shown below:

Key fields in the overflow data page include:

0x7E17 – variable‑column offset (after reversal, 0x177E = 6,014), covering 14 bytes of system data plus the 6,000‑byte column value.

0x0000804A00000000 – Blob ID, identical to the timestamp in the overflow pointer; mismatches cause DBCC CHECKDB errors.

0x0300 – data type identifier (3 = DATA), indicating the record stores user data.

Overall Logical Structure Diagram

The following diagram visualizes how a single logical row is split across the original heap page and the overflow page:

Summary

Row overflow causes a single logical row to span two physical pages, adding an extra I/O operation for each read. In large tables this extra I/O can dramatically increase random I/O and degrade performance. To mitigate the issue, keep variable‑length columns small, use appropriate MAX or LOB types for very large values, or apply vertical partitioning to move large columns to a separate table.

For further details on SQL Server storage structures, refer to the book “SQL Server Performance Tuning in Practice”.