SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)

 

Page is the most basic unit of SQL Server on storage. Each page consists of 8 KB.

The mdf or ndf files that allocate disks from the operating system are logically divided into pages.

IO on disk is performed at page level. SQL Server uses Extents to manage the Page.

Each Extent consists of 8 pages and has a size of 64 KB. There are two types of Extent.

 

1- Uniform Extent

Uniform Extent is the extent type that has only one object.

 

2- Mixed Extent

Mixed Extents is the extent type that have multiple objects.

 

SQL Server does not directly allocate a Uniform Extent to an object to use the domain more efficiently.

First, it creates the Extent as a Mixed Extent.

If the same object grows enough to fill 8 pages, this generated Mixed Extent is then converted to Uniform Extent.

You should read the article titled “Can We Use Our Disks at Maximum Performance?”

 

The page types in SQL Server are as follows:

Data Pages All data types except text, ntext, image, nvarchar (max), varchar (max), varbinary (max) and xml data type are kept on data pages.
Index Pages Indexes are kept on Index pages.
Text/Image Pages Text, ntext, image, nvarchar (max), varchar (max), varbinary (max) and xml data types are kept.

If data size exceeds 8 KB for columns that use varchar, nvarchar, and varbinary data types, this data is kept on Text / Image pages.

Global Allocation Map Pages,

Shared Global Allocation Map Pages

In the “Gam page” s, which extens are allocated and which are not allocated information is kept.

In the “SGam page”s, which extents are used as Mixed Extent information is kept.

Page Free Space Pages “PFS page” have information on how much free space on which page is kept.
Index Allocation Map Pages Which page belongs to which object is kept.
Bulk Changed Map After the last log backup, the information of the Extents that are changed by the bulk operations is kept.
Differential Changed Map After the last backup, the information of the changed Extents is kept.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *