SQL Server DBA Tips and Tricks

Page in SQL Server

Page is fundamental unit of data storage in SQL Server.
The disk space allocated to data files in database is logically divided into pages. Disk I/O operations are performed at the page level. Which means SQL server read or writes the whole data page.
All the pages are same physical size i.e. 8 KB.
Some pages includes data, metadata or both.
Each Page begins with the 96-byte header which includes system information about the page. It includes the page number and page type. It also covers the amount of free space on the page. Additionally, it includes the allocation unit ID of the object that owns the page.

Note – Log files do not contain pages, they contain a series of log records.

Types of Pages

1. Data Pages
2. Index Pages
3. Text/Image Pages
4. Global Allocation Map(GAM)
5. Shared Global Allocation Map(SGAM)
6. Page Free Space(PFS)
7. Index Allocation Map(IAM)
8. Bulk Changed Map(BCM)
9. Differential Changed Map(DCM)

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

2. Index Pages
Index pages includes index entries.

3. Text/Image pages
It includes data with large object types. These include text, ntext, and image. Other types are nvarchar(max), varchar(max), varbinary(max), and xml data.

4. Global Allocation Map (GAM)
GAM page includes 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 uniform or mixed extent.
A GAM can hold information of 64000 extents.

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

6. Page Free Space (PFS)
PFS pages includes information about page allocation and free space available on pages.

7. Index Allocation Maps (IAP)
IAP pages includes information about extents used by a table or index per allocation unit.

8. Bulk Changed Map (BCM)
BCM pages contain information about extents. These extents are modified by bulk operations after the last BACKUP LOG statement.

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

Extent in SQL Server

Extent is a collection of eight physically contiguous pages, which means size of extent is 64 KB.
Extent helps to manage pages efficiently.
SQL Server uses GAM and SGAM pages to manage extent allocation.
BCM and DCM pages are used by SQL server to track the modifications in extents.

Types of Extents

1. Uniform Extent
Uniform extents are owned by a single object. Means all the eight pages in the extent are used by same object.

2. Mixed Extent
Mixed extents are owned by different objects. Each of the eight pages can be used by different objects.

Check all related articles