Pages and Extent in SQL Server

 Hey guys, In this blog I am going to explain you about Pages and Extent in SQL Server and also different types of pages in SQL Server

Pages - A page is a unit of data storage in SQL. The size of a page is 8Kb. The data rows are put on the page serially after the header.

Types of Pages

1.              Data Pages

2.            Index Pages

3.             Global Allocation Map (GAM) Pages

4.            Shared Global Allocation Map (SGAM) Pages

5.             Differential Changed Map (DCM)

Data Pages- Data pages contain data rows with all the data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.

 

Index Pages- Index pages contain index entries.

 

GAM Pages- The GAM page contains information related to Extent allocation.
GAM has a bit for every extent. If the bit is 1, the corresponding extent is free. If the bit is 0, the corresponding extent is in use as a uniform or mixed extent.
A GAM can hold information of 64000 extents.

 

Shared Global Allocation Map (SGAM) Pages- The SGAM pages record which extents are currently being used as mixed extent and also have at least one unused page.
If the bit is 1, the corresponding extent is used as mixed extent and also have at least one page free to allocate. If the bit is 0, the corresponding extent is either not used as mixed extent or it is a mixed extent with all its pages being used.
A SGAM page also holds information of 64000 extents.

 

Differential Changed Map (DCM) Pages- DCM pages contain information about the extents that have been changed since the last BACKUP database statement.

 

Extent - These are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte. SQL Server has two types of extents

1.     Uniform extent - These extents are owned by a single object, all eight pages in the extent can only be used by the owning object.

 

2.   Mixed extent - These extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.