Pages & Extent in SQL Server

Here is the list of all types of pages and extents in SQL Server. It will definitely help you during your SQL Server DBA interviews.

1. Data Pages:

• Data pages are the basic units of storage in SQL Server.
• They store actual data, such as rows of tables or indexes.
• Each data page has a fixed size of 8 KB and can hold multiple rows of data.

2.????Index Pages:

• Index pages store index key values and pointers to corresponding data rows.
• They facilitate efficient data retrieval based on indexed columns.
• Index pages follow the same 8 KB size structure as data pages.

3.????IAM (Index Allocation Map) Pages:

• IAM pages track the allocation status of individual extents within a database.
• They maintain information about allocated and free extents.
• IAM pages are crucial for SQL Server's storage engine to manage physical data storage.

4.????GAM (Global Allocation Map) Pages:

• GAM pages track allocation status at the level of entire files or filegroups within a database.
• Each GAM page represents one allocation unit, which can be an extent or a collection of extents.
• GAM pages indicate whether an allocation unit is in use or available for allocation.

5.????SGAM (Shared Global Allocation Map) Pages:

• SGAM pages also track allocation status at the file or filegroup level, but at a more granular extent level than GAM pages.
• Each SGAM page represents one extent.
• SGAM pages track whether specific extents are allocated or unallocated, facilitating efficient allocation.

6.????PFS (Page Free Space) Pages:

• PFS pages store information about the amount of free space available on each data and index page.
• They track the percentage of free space on a page, ranging from 0% to 100%.
• PFS pages help SQL Server determine where to allocate new data or index rows.

7.????Text/Image Pages:

• Text/Image pages store large object (LOB) data types, such as text, ntext, and image.
• LOB data, exceeding the maximum size for regular data pages, is stored separately.
• Text/Image pages are used for storing and retrieving large text and binary data.

8.????Allocation Bitmap Pages:

• Allocation bitmap pages track the allocation status of individual pages within an extent.
• Each extent has an allocation bitmap page that indicates which pages are allocated or free.
• The bitmap structure uses bits to represent the allocation status of each page.

9.????Differential Changed Map (DCM) Pages:

• DCM pages are used in differential backups to track modified extents since the last full backup.
• They identify extents that have been modified, optimizing the backup process.

10. Bulk Changed Map (BCM) Pages:

• BCM pages track extents modified during bulk operations in bulk-logged recovery mode.
• They help SQL Server identify extents affected by bulk inserts or updates for efficient recovery.

11. Global Allocation Map (GAM) Interval Pages:

• GAM interval pages store additional GAM information for large databases or files.
• They improve performance and reduce contention when tracking allocation status.

12. Bulk Change Map (BCM) Interval Pages:

• BCM interval pages store additional BCM information for large databases or files.
• They optimize tracking of modifications during bulk operations.

13. Mixed Extents:

• A mixed extent is an extent that contains pages allocated to different objects.
• In a mixed extent, pages are shared between multiple tables or indexes.
• Each mixed extent contains a combination of data pages, index pages, or pages allocated to different objects.

14. Uniform Extents:

• A uniform extent is an extent that is fully allocated to a single object, either a table or an index.
• Uniform extents are more efficient for data access and storage because there is no sharing of pages between different objects.