Roadmap › SQL Server Internals — How the Database Engine Really Works › Module: Architecture and Storage
Lessons in this course (11)
2. Pages, extents, files and allocation maps
Intermediate★ Must knowLesson 2 of 11
- A database has at least one data file (
.mdf, plus optional.ndfin filegroups) and one log file (.ldf). - Data files are divided into pages of 8 KB (8,192 bytes: 96-byte header, row data, row offset array at the end). Maximum in-row data ≈ 8,060 bytes per row.
- 8 contiguous pages = an extent (64 KB). Mixed extents (shared by objects) vs uniform extents (one object; default for new allocations since SQL Server 2016).
| Page type | Holds |
|---|---|
| Data | Rows of heaps and clustered index leaf level |
| Index | Non-leaf index levels and nonclustered index rows |
| Text/Image (LOB), row-overflow | NVARCHAR(MAX), VARBINARY(MAX), rows that exceed 8,060 bytes |
| GAM / SGAM | Global Allocation Map — which extents are free / mixed extents with free pages (each covers ~4 GB) |
| PFS | Page Free Space — how full each page is (covers ~64 MB) |
| IAM | Index Allocation Map — which extents belong to a given table/index partition |
| DCM / BCM | Changed extents since last full backup / bulk-logged operations |
| Boot page, file header | Database and file metadata |
Row structure (FixedVar format): status bits, fixed-length columns, null bitmap, variable-length column count and offset array, variable-length data. That's why fixed-length types (INT, CHAR(10)) are laid out first and VARCHAR columns need offsets.
sql
-- See how many pages a table uses and how full they are
SELECT index_id, index_type_desc, page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'DETAILED');