SQL Server manages space allocation for tables and indexes using several metadata pages. While GAM, SGAM, and PFS pages track space at the extent and page level, Index Allocation Map (IAM) pages provide the link between allocation units and physical storage locations.

Starting with SQL Server 2005, allocation units fall into three categories:

• IN_ROW_DATA: Stores regular row data for heaps and B-trees.
• LOB_DATA: Holds large object (LOB) data such as xml, varbinary(max), and varchar(max).
• ROW_OVERFLOW_DATA: Stores variable-length data exceeding the 8,060-byte row size limit (e.g., oversized varchar, nvarchar, varbinary, or sql_variant columns).

An IAM page tracks the allocation of one specific allocation unit for a given table or index partition across GAM intervals. Each GAM interval covers 4 GB of space and has its own IAM chain when needed.

Demo Setup

Consider a sample table with mixed column types to trigger all three allocation unit types:

USE InternalStorageFormat;
GO

CREATE TABLE IAMTable (
    Id INT,
    col1 VARCHAR(3000),
    col2 VARCHAR(3000),
    col3 VARCHAR(3000),
    Lobdata NTEXT
);
GO

Inserting a small row:

INSERT INTO IAMTable 
VALUES (1, 'A', 'B', 'C', N'Test');

Expanding the row using REPLICATE to exceed row-size limits:

INSERT INTO IAMTable 
VALUES (1, REPLICATE('A',3000), REPLICATE('A',3000), REPLICATE('A',3000), N'Test');

Running DBCC IND('InternalStorageFormat','IAMTable',1) shows that:

• Two IAM pages (type 10) appear—one each for in-row and LOB data.
• No IAM page is allocated for row-overflow data until overflow actually occurs.

Once overflow data is generated, a third IAM chain appears.

Structure of an IAM Page

Using DBCC PAGE on page 175 (the first in-row IAM page in our example) yields the following sections:

DBCC TRACEON(3604);
GO
DBCC PAGE('InternalStorageFormat', 1, 175, 3);
GO

Key header fields include:

• sequenceNumber: Indicates the position in the IAM chain (starts at 0).
• start_pg: The first page ID in the GAM interval being mapped (e.g., (1:0) means GAM interval starting at file page 0).
• objectId, indexId, page_count, status: Reserved or unused for internal compatibility.

The page content is organized into two major areas:

1. Single Page Allocation: Lists first 8 pages allocated from mixed extents (e.g., 174, 210). Once 8 individual pages are used, SQL Server switches to extent-based allocation.
2. Extent Allocation: Lists extent starts (8-page intervals) allocated from uniform extents.

After inserting 7 more rows (total 8 overflows), the output shows:

• Red zone (mixedly-allocated pages): Pages 174, 210, 212, 214, 215, 217, 218, 220 (8 total, exhausting the initial mixed extent).
• Blue zone (uniformly-allocated extents): Begins at extent starting with page 224 (i.e., 224–231), reflecting uniform extent allocation.

Run DBCC IND again—results match: 2-page mixed allocation, followed by 184+ extent-aligned pages.

IAM Chain Dynamics

Each allocation unit may require multiple IAM pages if:

• The table has multiple partitions (each partition has its own IAM chain), or
• The data grows across multiple GAM intervals (every ~4 GB of data).

All IAM pages for a given allocation unit chain together via linked-page pointers—the sequence defines the IAM chain. The first IAM page contains mixed-extent info; subsequent ones do not.