PFS, GAM, SGAM Pages in SQL Server: Detailed Explanation

Introduction

In SQL Server, understanding the internal structures that control data storage is crucial for database administrators and developers who aim to optimize performance, ensure data integrity, and troubleshoot database issues. Among these internal structures, the PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map) pages play significant roles in the management of space on data pages. These pages are part of SQL Server’s storage architecture that handles the allocation and management of data pages within a database.

Each of these page types has a specialized function related to how SQL Server tracks and allocates space within data files. While users interact with logical data structures such as tables, indexes, and queries, the database engine depends on these internal pages to ensure data is stored and retrieved efficiently. A deep understanding of PFS, GAM, and SGAM pages will help SQL Server professionals optimize storage performance, recover data, and resolve potential issues with space management.

This comprehensive guide will provide an in-depth look at the PFS, GAM, and SGAM pages, covering their structure, functions, and impact on performance and space management in SQL Server.

Understanding PFS (Page Free Space) Pages

The PFS page is used by SQL Server to track the allocation of space within a data page. Data pages are the basic unit of storage in SQL Server, and PFS pages provide critical information about how data pages are used and whether they are full, partially filled, or empty. The PFS page tracks the allocation of data pages within a filegroup and provides important information for the database engine when determining where new data should be stored.

Key Functions of PFS Pages

Tracking Free Space: The primary function of the PFS page is to track the availability of free space on data pages. Each data page is represented by a corresponding bit in the PFS page, with the bit indicating whether the page is fully allocated, partially allocated, or free.
Free Space Management: SQL Server relies on PFS pages to quickly locate available space when inserting new rows or performing index operations. When a page is partially filled, the PFS page helps SQL Server determine how much space is available for use on that page.
Page Allocation: When a new data page is allocated, SQL Server updates the corresponding bit in the PFS page to indicate that the page is now in use. This allows the database engine to manage page allocation efficiently.
Efficient Page Scanning: PFS pages enable SQL Server to perform efficient space scanning by maintaining a bit-level tracking system for each data page. This allows the database engine to quickly find free or partially filled pages, thus minimizing unnecessary page reads and writes.

Structure of a PFS Page

A PFS page is 8 KB in size and consists of several distinct sections:

Header: Contains basic information about the page, including a pointer to the file where the page resides.
Allocation Information: Each page in the filegroup is represented by a bit, indicating its usage status (free, allocated, or partially allocated).
Free Space Bitmap: A bitmap for tracking the free space on each data page, which helps SQL Server decide where to store new data.

Types of PFS Page States

Allocated (In-Use): This indicates that a data page has been allocated and is currently being used to store data. The bit corresponding to this page in the PFS page is set to 1.
Free: A data page that has been allocated but is not being used (i.e., it’s empty). The bit corresponding to this page in the PFS page is set to 0.
Partial: This indicates that a page has been partially filled with data. The bit in the PFS page reflects that the page contains some but not all available space.

Impact of PFS Pages on Performance

PFS pages significantly impact SQL Server’s performance in terms of storage and efficiency. When the database engine needs to insert a new row or extend an index, it can rely on PFS pages to efficiently locate an appropriate data page with sufficient free space. In addition, PFS pages allow SQL Server to minimize the cost of space allocation, which can lead to better query performance and faster data writes.

GAM (Global Allocation Map) Pages

The GAM (Global Allocation Map) pages serve a broader purpose compared to PFS pages. While PFS pages track the allocation of space on individual data pages, GAM pages track the allocation of extents across the entire database. An extent is a unit of storage consisting of 8 data pages (64 KB). This means that GAM pages help manage the allocation of space at the extent level rather than at the individual page level.

Key Functions of GAM Pages

Tracking Extent Allocation: GAM pages keep track of which extents are allocated and available. Since SQL Server allocates space in extents, GAM pages help the system understand whether a given extent has been assigned to a table or index.
Global Tracking of Allocated Space: GAM pages provide a global view of extent allocation across the database. They maintain an overview of which extents are in use and which are available for allocation.
Efficient Allocation of Space: By tracking extents globally, GAM pages allow SQL Server to allocate extents efficiently. When a new extent is needed, SQL Server can consult the GAM page to quickly find an available extent, reducing allocation time.
Avoiding Fragmentation: By using GAM pages, SQL Server can minimize fragmentation by ensuring that extents are allocated and deallocated in a consistent manner. GAM pages help the system track which extents are being used by a table or index, avoiding scattered allocations.

Structure of a GAM Page

A GAM page is 8 KB in size, similar to a PFS page, but instead of tracking individual pages, it tracks the status of extents. Each bit in the GAM page represents a group of 8 data pages (an extent) within the database.

The structure of a GAM page includes:

Header: Contains information about the page, such as the file number and extent group.
Extent Bitmap: A bitmap where each bit corresponds to a group of 8 pages (an extent). The bit indicates whether the extent is allocated (1) or free (0).

Types of GAM Page States

Allocated Extents: These extents are in use by tables, indexes, or other database objects. The corresponding bit in the GAM page is set to 1.
Free Extents: These extents are not currently in use and can be allocated for new tables or indexes. The corresponding bit in the GAM page is set to 0.

Impact of GAM Pages on Performance

GAM pages are critical for space allocation at the extent level. Efficient management of extents reduces the time needed to allocate space for large objects (e.g., tables and indexes) and helps to avoid extent fragmentation. When SQL Server needs to allocate space for a new table or index, it consults the GAM page to find an available extent, which speeds up the process and leads to better performance.

SGAM (Shared Global Allocation Map) Pages

The SGAM (Shared Global Allocation Map) pages function similarly to GAM pages, but they have a specific role in managing shared extents. Shared extents are used when multiple tables or indexes share the same extent. SGAM pages track these shared extents, helping to manage space when multiple objects are allocated to the same extent.

Key Functions of SGAM Pages

Tracking Shared Extents: SGAM pages track which extents are used by more than one object. In SQL Server, when there is not enough space for a new object to have its own extent, it can share an extent with other objects. SGAM pages help SQL Server manage these shared extents.
Efficient Space Allocation: When multiple tables or indexes need space and there are free shared extents available, SQL Server consults the SGAM page to determine if any extents are available for shared use. This helps prevent fragmentation by grouping multiple objects together in the same extent.
Minimizing Fragmentation in Shared Extents: Shared extents, if not managed correctly, can lead to internal fragmentation in the database. SGAM pages help reduce this issue by keeping track of shared extents and ensuring that objects can be allocated in an organized and efficient manner.

Structure of an SGAM Page

An SGAM page is also 8 KB in size, and its structure mirrors that of the GAM page. Instead of tracking individual extents, SGAM pages track the allocation of shared extents. Each bit in the SGAM page corresponds to a shared extent and indicates whether it is in use.

The structure includes:

Header: Contains metadata about the SGAM page, including the file number and extent group.
Shared Extent Bitmap: A bitmap where each bit corresponds to a shared extent. A bit value of 1 indicates that the shared extent is in use, while a bit value of 0 indicates that the extent is available.

Types of SGAM Page States

Allocated Shared Extents: These are extents that are shared between two or more objects. The corresponding bit in the SGAM page is set to 1.
Free Shared Extents: These extents are available for allocation and can be used by multiple objects. The corresponding bit in the SGAM page is set to 0.

Impact of SGAM Pages on Performance

SGAM pages are essential for managing shared space. Properly managing shared extents can help minimize the fragmentation caused by the use of shared extents and can result in better space utilization. When multiple objects share an extent, SGAM pages allow SQL Server to manage the allocation efficiently, which reduces fragmentation and improves overall database performance.

PFS, GAM, and SGAM pages are integral components of SQL Server’s internal space management system. They track the allocation of space at various levels: PFS pages manage free space at the individual page level, GAM pages manage extents globally, and SGAM pages handle the allocation of shared extents.

Understanding how these pages work together to track and allocate space helps database administrators optimize performance, ensure data integrity, and troubleshoot issues related to space management. By properly configuring and maintaining these pages, SQL Server professionals can minimize fragmentation, improve space utilization, and ensure efficient data storage.

Proper management of PFS, GAM, and SGAM pages is crucial for optimizing SQL Server performance, particularly for large databases that require efficient space allocation.