SQL Server Page Architecture

The page is the basic building block of storage in SQL server. Data is stored in datafiles (.mdf and .ndf).  Storages are built by group of pages, logically speaking data is stored in pages.

Each page is of 8 KB (8192 bytes) in size, unlike the page/block size in filesystems, SQL server cannot change the size of the pages. Pages has Header, Footer/Row Offset and Data columns as shown in the below picture.

Page Header: This is 96 bytes and stores the information about page number, page type, the amount of free space and allocation unit ID.

Data Rows: This stores the actual data with storage capacity of 8060 bytes.

Row Offset: This is 36 bytes, stores the information about the data rows and address to next pages.

Extent: – The consecutive group of 8 Pages is called extent, technically 64 KB (8*8KB). There are two types of extents.

  1. Uniform Extents: – If the complete extent is occupied by the data of a single object then it is called as uniform extent.
  2. Mixed Extents: – If the extent is occupied by data of different object or the extent is not completely filled are called as Mixed Extents.
SQL Server Page Architecture

There Are Different Types of Pages in SQL Server

  1. Data Page: – These stores the actual data except the data related to some data types which data is more than 8 KB.

  2. Text/Image Page: – These store the LOB (large object) data, technically data which is more than 8 KB in size. It stores the data of datatypes (text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml). Data that has Row-Overflow, Data Exceeding 8 KB, if the data is more than the size of the page(8KB) the data column will be automatically allocated to the new page by SQL server and will set IN_ROW_DATA allocation counter to 1.

  3. Index Page: – These pages store all the index data. It has multiple types of pages.
    • Page free space page’s – These stores the information about the pages allocated and free pages availability.

    • IAM (Index Allocation Mapping) – These stores the information about the pages of tables that are allocated to index.

    • GAM (Global Allocation Mapping) – These stores the information about the extents (Free/ Allocated) by bit parity. If the bit parity = 1 it means extent is free and If bit parity = 0 it means extent is allocated to another object. These stores the information about 64000 extents Technically 4 GB.

    • SGAM (Shared Global Allocation Mapping) – These pages also store the information about extents which are mixed in bit parity. If Bit Parity = 1 it means the extent is a mixed extent and may have at least one unallocated page available for use. If Bit Parity = 0 then it means the extent is either dedicated or is a mixed extent with no unallocated pages (essentially the same situation given that the SGAM is used to find mixed extents with unallocated pages). These also stores the information about 64000 extents (information about the data which is 4 GB).

    • BCM (Bulk Changed Mapping): – This stores the information about the extents that are modified due to Bulk-Operations after last LOG backup on the database.

    • DCM (Differential Changed Mapping) – These pages note the information about all the extents that are modified after last full backup.

Leave a Reply