A Tour of SQL Server Page Types: Data, GAM, SGAM, PFS, IAM
Most of this storage series has been spent inside data pages, pulling rows apart byte by byte. But a data file is not only data pages. Scattered through it, at fixed and predictable spots, are pages whose entire job is to track other pages: which extents are allocated, which have free space, how full each page is, and which pages belong to which table. This post takes a tour of those allocation and tracking pages, reads a few of them on a real database, and explains what the engine uses each one for.
This continues the byte-level storage series alongside the NULL bitmap, row-overflow and LOB, and where each column lives in a record. If DBCC PAGE is new to you, this older post covers the mechanics. Every value below is from a SQL Server 2019 instance, and the script is included so you can reproduce it.
The cast
Every page is 8 KB and starts with a 96-byte header, and that header’s m_type says what the page is.[1] An extent is eight contiguous pages, and the allocation pages exist to manage pages and extents efficiently rather than to hold your rows:
| Page type | m_type | What it tracks |
|---|---|---|
| Data | 1 | Your rows |
| Index | 2 | B-tree index rows |
| GAM | 8 | Which extents are allocated (uniform extents) |
| SGAM | 9 | Mixed extents that still have free pages |
| PFS | 11 | Per-page allocation and how full each page is |
| IAM | 10 | Which extents belong to one allocation unit |
The GAM, SGAM, and PFS pages sit at fixed offsets in every data file: PFS is page 1, GAM is page 2, SGAM is page 3, and they repeat at regular intervals as the file grows. That fixed placement is why the engine can find them without looking anything up.
Which pages did one table get?
Build a small table and ask sys.dm_db_database_page_allocations what it allocated:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE [dbo].[pt_demo] ( [id] int NOT NULL , [pad] char(1000) NOT NULL ); DECLARE @i int = 1; WHILE @i <= 200 BEGIN INSERT INTO [dbo].[pt_demo] ( [id], [pad] ) VALUES ( @i, 'x' ); SET @i += 1; END; SELECT [page_type] = [pa].[page_type_desc] , [count] = COUNT(1) FROM [sys].[dm_db_database_page_allocations](DB_ID(), OBJECT_ID(N'dbo.pt_demo', N'U'), NULL, NULL, N'DETAILED') AS [pa] WHERE [pa].[is_allocated] = 1 GROUP BY [pa].[page_type_desc]; |
|
1 2 3 4 |
page_type count --------- ----- DATA_PAGE 36 IAM_PAGE 4 |
Two hundred thousand-byte rows filled 36 data pages, and the table also owns IAM pages. The IAM is how the engine knows which extents in the file hold this table’s data, since a heap’s pages are not chained in order. Every allocation unit (the in-row, row-overflow, and LOB units from the previous post) gets its own IAM chain.
Reading the PFS page
Page 1 of the file is the PFS, Page Free Space. It stores one byte per page describing that page’s allocation state and how full it is. Dumping it (DBCC PAGE (db, 1, 1, 3)) produces a readable inventory:
|
1 2 3 4 5 6 |
(1:0) - (1:3) = ALLOCATED 100_PCT_FULL (1:4) - (1:5) = NOT ALLOCATED 0_PCT_FULL (1:6) - (1:7) = ALLOCATED 100_PCT_FULL (1:8) - = ALLOCATED 0_PCT_FULL Mixed Ext (1:9) - = ALLOCATED 100_PCT_FULL Mixed Ext (1:10) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext |
Each line is a page or run of pages, its allocation state, and a fullness bucket: 0, 50, 80, 95, or 100_PCT_FULL. That fullness figure is what SQL Server consults when it needs somewhere to put a new row in a heap or a LOB, so it does not have to scan pages hunting for space. The Mixed Ext and IAM Page flags mark pages that live in mixed extents and the IAM pages themselves. The PFS page tracks about 8,000 pages, then another PFS page picks up where it left off.
Reading GAM and SGAM
The GAM (page 2) and SGAM (page 3) manage allocation at the extent level. The GAM has one bit per extent: set means the extent is free, clear means it is allocated as a uniform extent. The SGAM tracks mixed extents, the extents whose eight pages can belong to different objects, that still have a free page to hand out. Between them the engine can find a free extent or a free page in a mixed extent by reading a couple of bitmap pages rather than walking the file. Dumping GAM or SGAM with DBCC PAGE prints the allocation state of each extent as a range, for example (1:0)-(1:3) = ALLOCATED.
What this means in practice
- Allocation is bitmap-driven, not a scan. When you insert a row and SQL Server needs a page, it does not search; it reads the GAM, SGAM, and PFS to locate free space directly. This is why allocation stays fast as a file grows.
- These pages can be a contention point. Because GAM, SGAM, and especially PFS pages are fixed, hot spots on them can serialize allocation under heavy concurrent inserts, the classic
tempdballocation contention, addressed by multiple data files and, on modern versions, by default optimizations. - The IAM is why a heap can be scanned at all. With no clustered index chaining pages together, the IAM chain is the map of which extents hold the table. A
DBCC PAGEtour makes it obvious that “the table” is really a set of extents an IAM points at. - You rarely touch these directly, but they explain a lot. Space that will not release, allocation contention, and the mechanics of how
DBCC CHECKDBand allocation checks work all trace back to these pages. Knowing they exist turns several mysteries into plumbing.
The row-level posts in this series show where your data sits; the allocation pages show how SQL Server keeps track of all of it without ever scanning the file to find room. It is bitmaps and fixed offsets all the way down.
Which of these pages have you run into in the wild, PFS contention in tempdb, or an IAM chain in a DBCC message? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- Page and Extent Architecture Guide – Microsoft Learn. The 8-KB page and 96-byte header, the page types (data, index, GAM, SGAM, PFS, IAM, and others), extents as eight contiguous pages, and the roles of the allocation and tracking pages. ↩