Differences between GAM, SGAM, Pam, Iam, DCM, and bcm in SQL Server

Gam, SGAM, Pam, Iam, DCM, and BCM are some special allocation ing tables used in SQL Server to manage space allocation. Understanding their differences and roles plays an important role in understanding the architecture of the SQL Server physical database.

SQL Server zone management (GAM, SGAM)

Global allocation ing table (GAM ):Unified Disk Area. The Gam page records allocated areas. Each gam contains 64,000 zones, which is equivalent to nearly 4 GB of data. Gam uses a single bit to indicate the status of each zone in the covered range. If the bit is 1, the zone is available. If the bit is 0, the zone has been allocated.

Shared global allocation ing table (SGAM ):The disk is owned by multiple objects. The SGAM page records the partitions that are currently used as a hybrid zone and have at least one unused page. Each SGAM contains 64,000 zones, which is equivalent to nearly 4 GB of data. SGAM uses a single digit to indicate the status of each zone in the covered range. If the bit is 1, the zone is used as a hybrid zone and there are available pages. If the bit is 0, the zone is not used as the hybrid zone, or all its pages are used even though it is used as the hybrid zone.

Management of available space of SQL Server (PFS)

PFS (page free space ):It is also called page free space. This page is used to track the utilization of each specific page in a file, in the unit of pages. The second page (page 1) in a file is the PFS page, each byte on the page records the distribution, page type, Iam page, deletion record, and space utilization information of the corresponding page; PFS can manage and track the usage of 8088 pages, that is, close to 64 MB space, and will appear every 8088 pages in the future.

Management of space used by SQL Server objects (PFS)

"Index allocation ing (IAM)" Page:Map the areas in the 4 GB section of the database file used by the allocation unit. It is mainly used to identify the partitions used by the SQL Server Object. For example, table A's non-clustered index B occupies 7895 4 partitions. In this way, when making changes to this object, you can quickly find the four areas and change the file.

When the SQL Server database engine must Insert a new row into the current page and there is no available space on the current page, it will use the iam and PFS pages to find the page to which the row will be allocated, or (for heap or text/image pages) find pages with enough space to accommodate this row. The database engine uses the iam page to find the partition allocated to the allocation unit. For each zone, the database engine searches for the PFS page to see if there are available pages. Each Iam and PFS page contains a large number of data pages, so there are only a few Iam and PFS pages in a database. This means that Iam and PFS pages are usually located in the memory SQL Server Buffer Pool, so they can be found quickly. For indexes, the insert point of a new row is set by the index key. In this case, the above search process does not appear.

SQL Server manages modified zones (DCM and BCM)

Difference change ing table (DCM ):

In this way, you can track the areas that have been changed since the last execution of the backup database statement. If the extended disk space is 1, the extended disk space has been modified since the last execution of the backup database statement. If the bit is 0, the extended disk is not modified.

Differential backup only reads the DCM page to determine the modified partition. This greatly reduces the number of pages that must be scanned for differential backup. The time required to run differential backup is proportional to the number of zones modified after the last backup database statement is executed, rather than the size of the entire database.

Large Capacity change ing table (BCM ):

Track the region where the backup log statement is modified by the large-capacity log record operation since the last execution. If the bit of an extended Disk Area is 1, it indicates that the extended Disk Area has been modified by the large-capacity copy operation with log records since the last backup log statement was executed. If the bit is 0, the extended disk is not modified by the large-capacity replication operation with log records.

Although the BCM page is displayed in all databases, it is related to the BCM page only when the database uses the large-capacity log recovery mode. In this recovery mode, when the backup log is executed, the backup process scans BCM for the modified zone. Then, include those zones in the log backup. If the database is recovered from the database backup and a series of transaction log backups, the large-capacity log record operations can be restored. In databases that use the simple recovery mode, the BCM page is irrelevant because large-capacity logging operations are not logged. In a database that uses the full recovery mode, the BCM page is also irrelevant, because the recovery mode regards large-capacity logging operations as operations with full logs.

The interval between the DCM page and the BCM page is the same as that between the GAM page and the SGAM page, both of which are 64,000 zones. In a physical file, the DCM and BCM pages are located behind the GAM and SGAM pages.