8-KB Pages, Extents, Allocation Maps, IAM, PFS, GAM/SGAM, and File Internals

Learning outcomes

ServiceHub has reached the point where “the table is 40 MB” is no longer a sufficient storage explanation. Operators need to know what that number means physically: SQL Server stores ordinary rowstore data and indexes in 8-KB pages; eight contiguous pages form a 64-KB extent; allocation maps describe which extents and pages are available; and each table/index partition owns one or more allocation units. The goal is not to memorize page trivia. It is to connect supported metadata to concrete questions such as “which file is growing?”, “why did page count change?”, and “is this object using in-row, row-overflow, or LOB storage?”

1. From a logical row to pages and extents

A page is SQL Server's fundamental database storage unit for most rowstore data. A data page is 8 KiB and begins with a 96-byte header that identifies the page and carries engine metadata. A single row normally cannot consume the whole 8 KiB: the row format and slot array consume space, and variable-length data can move to ROW_OVERFLOW_DATA or LOB_DATA allocation units. An extent groups eight physically contiguous pages, so an extent is 64 KiB.

Do not confuse these layers with files. A data file contains many extents; a filegroup is a logical container for one or more data files; a partition of a heap or B+ tree owns allocation units; and an allocation unit owns pages/extents of a particular storage kind. Those distinctions explain why “table size,” “file size,” and “allocated page count” answer different questions.

USE ServiceHubLab;GOSELECT SERVERPROPERTY('ProductVersion') AS engine_build,       SERVERPROPERTY('Edition') AS edition;SELECT compatibility_level,recovery_model_descFROM sys.databases WHERE name=DB_NAME();SELECT file_id,name,type_desc,physical_name,size*8.0/1024 AS size_mb,       growth,is_percent_growthFROM sys.database_filesORDER BY file_id;SELECT fg.data_space_id,fg.name AS filegroup_name,fg.is_defaultFROM sys.filegroups AS fgORDER BY fg.data_space_id;GO

2. PFS, GAM, SGAM, and IAM answer different allocation questions

Page Free Space (PFS) pages track page-allocation state and approximate free-space categories. The Global Allocation Map (GAM) tracks extents that are free versus allocated. The Shared Global Allocation Map (SGAM) identifies mixed extents that still have a free page. An Index Allocation Map (IAM) belongs to an allocation unit and maps the extents that allocation unit uses across a GAM interval. These are engine-maintained system pages, not tables you update.

Mixed extents are historical context, not a reason to turn on old trace flags. Starting with SQL Server 2016, user databases default to uniform-extent allocation because MIXED_PAGE_ALLOCATION defaults to OFF; older advice centered on trace flag 1118 is therefore not a current SQL Server 2025 baseline. System-database history differs, so always inspect the target database rather than copying folklore.

SELECT name,is_mixed_page_allocation_onFROM sys.databasesWHERE name=DB_NAME();GOIF SCHEMA_ID(N'lab08') IS NULL EXEC(N'CREATE SCHEMA lab08 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab08.PageProbe;CREATE TABLE lab08.PageProbe(    probe_id int IDENTITY(1,1) NOT NULL        CONSTRAINT PK_PageProbe PRIMARY KEY,    category char(1) NOT NULL,    payload varchar(700) NOT NULL);;WITH n AS(  SELECT TOP (3000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)INSERT lab08.PageProbe(category,payload)SELECT CHAR(65 + n % 4),REPLICATE('x',650) FROM n;GOSELECT p.index_id,p.rows,au.type_desc,       au.total_pages,au.used_pages,au.data_pagesFROM sys.partitions AS pJOIN sys.allocation_units AS au ON au.container_id=p.hobt_idWHERE p.object_id=OBJECT_ID(N'lab08.PageProbe')ORDER BY p.index_id,au.type_desc;GO

3. Supported operational evidence first

sys.dm_db_index_physical_stats is documented and can report page count, page density, fragmentation, allocation-unit type, and B+ tree depth. sys.dm_db_page_info is also documented for SQL Server 2019+ and returns page-header metadata when you already know the file/page identifier. These are appropriate building blocks for repeatable operational tooling, subject to their permission and scanning-cost requirements.

DECLARE @obj int=OBJECT_ID(N'lab08.PageProbe');SELECT index_id,index_type_desc,alloc_unit_type_desc,       index_level,page_count,avg_page_space_used_in_percent,       avg_fragmentation_in_percent,record_countFROM sys.dm_db_index_physical_stats(DB_ID(),@obj,NULL,NULL,'DETAILED')ORDER BY index_id,index_level DESC,alloc_unit_type_desc;GO

The exact page count and density depend on row format, engine build, compression, and prior modifications, so the lesson does not prescribe one expected numeric result. The expected shape is stable: the clustered primary key appears as a B+ tree and its leaf level is index level 0.

4. Low-level discovery has a supportability boundary

Microsoft's current page/extent guide explicitly marks sys.dm_db_database_page_allocations and sys.system_internals_allocation_units as unsupported. They can be valuable in a disposable learning lab, but they must not become a production monitoring contract. If you use the unsupported page-allocation function to discover one page, immediately switch back to the supported sys.dm_db_page_info for the page header and label the dependency.

-- EDUCATIONAL ONLY: sys.dm_db_database_page_allocations is unsupported.DECLARE @file_id int,@page_id bigint;SELECT TOP (1)       @file_id=allocated_page_file_id,       @page_id=allocated_page_page_idFROM sys.dm_db_database_page_allocations     (DB_ID(),OBJECT_ID(N'lab08.PageProbe'),1,NULL,'DETAILED')WHERE is_allocated=1 AND page_type=1;SELECT @file_id AS discovered_file_id,@page_id AS discovered_page_id;-- Supported SQL Server 2019+ page-header function:SELECT *FROM sys.dm_db_page_info(DB_ID(),@file_id,@page_id,'DETAILED');GO

5. Production judgment and cleanup

Storage internals should answer an operational question, not invite random page inspection. For capacity, use file/filegroup and allocation-unit metadata. For index health, use documented physical/operational DMVs and workload evidence. For a specific latch/wait page, sys.dm_db_page_info can connect the page identifier to object metadata. Permissions matter: SQL Server 2022+ commonly requires VIEW DATABASE PERFORMANCE STATE for these database performance DMVs. No restart, paid edition, multi-node topology, or preview feature is required.

USE ServiceHubLab;DROP TABLE IF EXISTS lab08.PageProbe;-- Keep schema lab08 for later Chapter 08 lessons.GO

Authoritative references

• Page and extent architecture guide — pages, extents and allocation maps
• sys.dm_db_page_info — supported page-header inspection
• sys.dm_db_index_physical_stats — documented physical statistics
• Database files and filegroups — file/filegroup boundaries
• SQL Server 2025 build versions — servicing baseline