SQL Server: Pages and Extents Architecture

--------------------------------------------Pages ------------------------------------------------------

-   The page is the fundamental unit of data storage in SQL Server. 
-   The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
-   In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
-   When the total row size of all fixed and variable columns in a table exceeds the 8 KB limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.
-   Log files do not contain pages; they contain a series of log records.

------------------------------------------- Extent ------------------------------------------------------

-   An extent is a collection of eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
-   Extents help efficiently manage pages. All pages are stored in extents.
-   SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

-   A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations.

Managing Extent Allocations

Global Allocation Map (GAM) : GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 GB of data. The GAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

Shared Global Allocation Map (SGAM) : SGAM pages record which extents are currently being used as mixed extents and also have at least one unused page. Each SGAM covers 64,000 extents, or almost 4 GB of data. The SGAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has a free page. If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

Tracking Free Space

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.