SQL Server Storage Engine: Data Pages, Extents and Heaps (2026)

The 8 KB Page: SQL Server's Fundamental I/O Unit

SQL Server always reads and writes data in units of 8,192 bytes (8 KB). Even if you read one column from one row, SQL Server reads the entire 8 KB page containing that row into the buffer pool. This design makes random single-row lookups inexpensive (one 8 KB read) while range scans read contiguous pages sequentially.

A page has a fixed layout:

Offset   Size    Content
------   ------  -----------------------------------------
0        96 B    Page header
96       8,060 B Row data area (rows grow top-down)
8,060+   2 B×N   Slot array (grows bottom-up, 2 bytes per row)

The page header contains:

• pageId — file number + page number within the file
• type — data, index, text, IAM, PFS, GAM, SGAM, etc.
• lsn — log sequence number of the last modification
• freeData — offset of the next free byte in the row area
• slotCnt — number of slots (rows) on the page
• tornBits — torn-page detection checksum (when page checksums enabled)

The slot array at the bottom maps logical slot numbers to physical byte offsets of rows. This indirection means SQL Server can physically rearrange rows on the page (to reclaim space after deletes) without updating any pointers — only the slot array entries change.

Page Types

Row Format on a Data Page

Each fixed-length data row has four parts:

[ Row header (4 B) ][ Fixed-length columns ][ Null bitmap ][ Variable-length columns ]

• Row header (4 bytes) — row type flags, number of columns, optional forwarding pointer flag
• Fixed-length columns — stored consecutively in column definition order. INT = 4 B, BIGINT = 8 B, DATETIME2(7) = 8 B, CHAR(n) = n bytes, etc.
• Null bitmap — 2-byte count of total columns followed by one bit per column indicating NULL. Present even if the table has no nullable columns.
• Variable-length section — 2-byte count of variable columns, then 2-byte end-offset per variable column, then the actual variable-length data concatenated.

Example: a table with Id INT, Name VARCHAR(100), Active BIT storing (1, 'Alice', 1):

Header: 4 bytes
Id:     4 bytes (fixed)
Active: 1 byte  (fixed; BIT is stored as 1 byte in a row, not 1 bit)
Null bitmap: 2 + ceil(3/8) = 3 bytes
Var count:  2 bytes
Var offset[0]: 2 bytes  (end of Name data)
Name data: 5 bytes ('Alice')
Total: 4+4+1+3+2+2+5 = 21 bytes

Row Overflow: VARCHAR, NVARCHAR and MAX Columns

When a row's variable-length data would push the total in-row size beyond 8,060 bytes, SQL Server automatically moves the largest variable-length column values to row-overflow pages. The in-row storage for that column shrinks to a 24-byte pointer (file, page, slot) to the overflow page.

For VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, and IMAGE columns:

• Values ≤ 8,000 bytes can be stored in-row if sp_tableoption 'large value types out of row' is OFF (default)
• Values > 8,000 bytes are always stored out-of-row on LOB pages
• Out-of-row storage uses a B-tree of LOB pages for very large values

-- Check if large value columns are stored in-row or out-of-row
SELECT name, large_value_types_out_of_row
FROM sys.tables
WHERE name = 'Documents';

-- Force large values out-of-row (saves in-row space for other columns)
EXEC sp_tableoption 'dbo.Documents', 'large value types out of row', 1;

Extents: Uniform vs Mixed

SQL Server allocates disk space in extents — groups of 8 contiguous 8 KB pages (64 KB). There are two types:

Uniform extents — all 8 pages belong to the same object. Used once a table or index grows beyond 8 pages.

Mixed extents — pages from up to 8 different objects share the extent. SQL Server uses mixed extents for the first 8 pages of any new object to avoid wasting space on tables that stay small.

-- Distribution of page types and extent types for a table
SELECT
    index_id,
    index_type_desc,
    alloc_unit_type_desc,
    page_count,
    record_count,
    avg_record_size_in_bytes,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'DETAILED')
ORDER BY index_id, alloc_unit_type_desc;

Allocation Maps: GAM, SGAM, PFS, IAM

Four special page types track space at the file level:

When SQL Server allocates a new page for a heap row insert, it:

1. Reads the PFS page to find a page on a mixed extent with sufficient free space
2. If no suitable mixed extent page exists, reads the SGAM for a mixed extent with a free slot
3. If no mixed extents available, reads the GAM for a free uniform extent, allocates it, updates GAM + SGAM
4. Writes the IAM page for that table to record ownership of the new extent

Heap Tables: Structure and Characteristics

A heap is a table without a clustered index. Rows are stored in no particular order — they land wherever there is free space. The heap's "index_id" in sys.indexes is 0.

-- Check if a table is a heap
SELECT name, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.StagingOrders')
  AND type = 0;  -- 0 = heap

Characteristics of heaps:

• Inserts are fast — no B-tree maintenance; rows go wherever there is space (found via PFS scan)
• Full scans read an IAM chain — SQL Server reads the IAM pages to find all extents, then reads all data pages. This is an unordered scan, potentially with many random I/Os on fragmented heaps.
• No clustered key — rows are located by their 8-byte RID (Row Identifier: file:page:slot). Non-clustered index leaf rows store the RID as the row locator.
• Updates can cause forwarding — see next section

Heaps are appropriate for:

• Staging/bulk-load tables where you INSERT millions of rows then swap to a production table (no index maintenance overhead during load)
• Tables always accessed via a non-clustered index covering all needed columns (no clustered key lookups needed)
• Very small lookup tables where a full scan is always faster than a seek

Forwarding Pointers: The Hidden Cost of Heap Updates

When an UPDATE on a heap row makes the row too large to fit on its original page, SQL Server moves the row to a new page with more free space. But non-clustered indexes already store the original RID as the row locator — updating all those index entries would be expensive.

Instead, SQL Server leaves a forwarding pointer (9 bytes) at the original slot, pointing to the new page. Non-clustered index lookups follow: original RID → forwarding pointer → new page — two page reads instead of one.

-- Check forwarding record count (heap fragmentation indicator)
SELECT
    OBJECT_NAME(s.object_id)   AS table_name,
    s.forwarded_record_count,
    s.page_count,
    s.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
WHERE s.index_id = 0              -- heaps only
  AND s.forwarded_record_count > 0
ORDER BY s.forwarded_record_count DESC;

-- Rebuild the heap to eliminate forwarding pointers
ALTER TABLE dbo.StagingOrders REBUILD;

IAM Chains: How SQL Server Finds a Table's Pages

Each table (or index) has one or more IAM pages — one per 4 GB of data per allocation unit. The IAM pages form a linked chain stored as a bitmap. Each bit represents one extent; a set bit means that extent is owned by this object.

SQL Server uses the IAM chain to perform table scans without using the B-tree. For a heap, a full scan reads:

1. The first IAM page (recorded in sys.system_internals_allocation_units)
2. Each extent flagged in the IAM bitmap
3. Each page within those extents
4. Follow the IAM chain to the next IAM page when the current one's coverage is exhausted

-- Inspect IAM and allocation unit metadata
SELECT
    au.type_desc,
    au.data_pages,
    au.used_pages,
    au.total_pages,
    au.first_iam_page
FROM sys.allocation_units au
JOIN sys.partitions p ON au.container_id = p.partition_id
WHERE p.object_id = OBJECT_ID('dbo.Orders');

Clustered Index vs Heap: When to Use Each

Inspecting Storage with DMVs and DBCC

-- Page-level inspection (undocumented but widely used)
DBCC PAGE (YourDB, 1, 245, 3);  -- database, file, page, output style 3=full detail
-- Enable output: DBCC TRACEON(3604) first

-- Table space usage
EXEC sp_spaceused 'dbo.Orders';

-- Detailed physical stats per index
SELECT
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent,
    page_count,
    avg_page_space_used_in_percent,
    record_count,
    forwarded_record_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'SAMPLED')
ORDER BY index_id;

-- File-level space
SELECT
    name,
    physical_name,
    size * 8 / 1024                                    AS size_mb,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024        AS used_mb,
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS free_mb
FROM sys.database_files;

Read Next

• Transaction Log Internals: WAL and Checkpoints
• Index Internals: B-Tree, Page Splits and Fragmentation
• TempDB Internals: Version Store and Optimization
• Memory Internals: Buffer Pool and Plan Cache
• All SQL Server Articles