Pages are the smallest unit of I/O in SQL Server, with a fixed size of 8 KB. All data, including rows, indexes, and metadata, is stored on pages. SQL Server organizes storage using a variety of page types, each serving a specific role in the database engine.

Understanding these page types is helpful for database administrators and developers who want to optimize performance, troubleshoot issues, or simply deepen their knowledge of SQL Server internals. There are four main categories of page types: Data and Index, Allocation, Restore, and Metadata.

SQL Server Page Types

Data and Index Pages:

These are the core pages where user data and index information are stored.

Data (Type ID 1): Stores the actual data rows for tables, except for large object (LOB) data types like text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml when the “text in row” option is enabled.

Index (Type ID 2): Contains index entries, which help SQL Server quickly locate data rows based on indexed columns.

Text/Image (Type ID 3 or 4): Used for storing large object data types and variable-length columns when the data row exceeds 8 KB. These pages can span multiple pages to accommodate large data.

Allocation Pages:

Allocation pages manage how space is allocated and tracked within the database files.

GAM (Type ID 8): Global Allocation Map pages tracks which extents have been allocated.

SGAM (Type ID 9): Shared Global Allocation Map pages tracks mixed extents that have been allocated that have at least one free page available.

IAM (Type 10): Index Allocation Map pages keep track of which extents have been allocated to a particular table or index per allocation unit.

PFS (Type 11): Page Free Space pages record how much free space is available on each page and track page allocation status.

Restore Pages

These pages are essential for backup and restore operations, helping SQL Server track changes for efficient recovery.

Bulk Changed Map (Type 17): Tracks extents modified by bulk operations since the last BACKUP LOG statement, per allocation unit.

Differential Changed Map (Type 16): Tracks extents that have changed since the last BACKUP DATABASE statement, per allocation unit. This allows differential backups to be efficient by only backing up changed extents.

Metadata Pages

Metadata pages store critical information about the database and its files.

Boot (Type 13): Contains information about the database itself. Each database has only one Boot page.

File Header (Type 15): Stores information about the file. This is always the first page (page 0) in every file.

Conclusion

Understanding these SQL Server page types helps you appreciate how SQL Server manages data, tracks allocations, and supports backup and recovery. Whether you’re tuning performance, troubleshooting, or planning your storage architecture, knowing what each page type does is a foundational skill for any SQL Server professional. To learn more; check out the Pages and Extents Architecture Guide.