The details and differences between GAM, SGAM, PAM, IAM, DCM, and BCM in SQL Server

GAM, SGAM, PAM, IAM, DCM, and BCM are some of the special allocation mapping tables used in SQL Server to manage spatial allocations. Understanding their differences and roles is very important for understanding the SQL Server Physical database architecture.

Management of SQL Server area (gam,sgam)

Global Allocation Mapping Table (GAM): Unified extents, GAM pages record allocated extents. Each GAM contains 64,000 extents, which is equivalent to nearly 4 GB of data. GAM uses a bit to represent the state of each area within the covered range. If the bit is 1, the zone is available, and if the bit is 0, the extents are assigned.

Shared Global Allocation Mapping table (SGAM): The extents are shared by multiple objects, and the SGAM page records the extents that are currently used as mixed extents and have at least one unused page. Each SGAM contains 64,000 extents, which is equivalent to nearly 4 GB of data. SGAM uses a bit to represent the state of each area within the covered interval. If the bit is 1, the area is being used as a blending area and there are pages available. If the bit is 0, the zone is not used as a blending zone, or although it is used as a blending area but all of its pages are in use.

SQL Server free space Management (PFS)

PFS (page free space): Also known as page freedom, the page is used to track the utilization of each particular page in a file, in pages. The second page in a file (page 1) is the PFS page, where each byte of the page records the allocation of the page, the type of page, whether the IAM page, whether it contains delete records, and the spatial utilization information; PFS is able to manage and track the usage of 8,088 pages, which is nearly 64M in space, and will appear again every 8,088 pages later.

SQL Server objects Use Space management (PFS)

Index allocation Mapping (IAM) page: The extents in the 4 GB portion of the database file that will be mapped to the allocation unit used. It is used primarily to indicate which extents are used by SQL Server objects. For example, a table of nonclustered index B occupies 7895 4 extents. This allows you to quickly find the 4 extents and change the file when you make changes to this object.

When the SQL Server database engine must insert a new row in the current page, and there is no free space in the current page, it uses the IAM and PFS pages to find the page to which the row is to be assigned, or (for a heap or text/image page) to find a page that has enough space to hold the row. The database engine uses an IAM page to find the extents assigned to the allocation unit. For each zone, the database engine will search the PFS page to see if there are any available pages. Each IAM and PFS page covers a large number of data pages, so there are very few IAM and PFS pages in a database. This means that IAM and PFS pages are typically located in an in-memory SQL Server buffer pool, so they can be found very quickly. For an index, the insertion point of the new row is set by the index key. In this scenario, the above search process does not occur.

SQL Server Management of Modified zones (DCM, BCM)

Differential Change mapping Table (DCM):

This allows you to track the extents that have changed since the last BACKUP DATABASE statement was executed. If the extents are bits 1, the extents have been modified since the last BACKUP DATABASE statement was executed. If the bit is 0, the extents are not modified.

A differential backup reads only the DCM pages to determine which extents have been modified. This greatly reduces the number of pages that a differential backup must scan. The time required to run a differential backup is proportional to the number of extents modified since the last backup DATABASE statement, rather than to the size of the entire databases.


Bulk Change mapping Table (BCM):

Tracks the extents modified by bulk-logged operations since the last BACKUP LOG statement was executed. If the bit of an extent is 1, it indicates that the extents have been modified by a logging bulk copy operation since the last BACKUP log statement was executed. If the bit is 0, the extents are not modified by the bulk copy operation with logging.

Although BCM pages are displayed in all databases, the BCM pages are only relevant when the database uses the bulk-logged recovery model. In this recovery model, when you execute backup LOG, the backup process scans the BCM to find the area that has been modified. Then, include those areas in the log backup. Bulk-Logged operations can be resumed if the database is recovered from a database backup and a series of transaction log backups. In databases that use the simple recovery model, BCM pages are irrelevant because bulk-logged operations are not logged. In databases that use the full recovery model, BCM pages are also irrelevant because the recovery model treats bulk-logged operations as having full logging operations.

The interval between the DCM page and the BCM page is the same as the interval for the GAM and SGAM pages, which are 64,000 extents. In physical files, the DCM and BCM pages are located after the GAM and SGAM pages.

Transferred from: http://www.cnblogs.com/xunziji/archive/2011/02/22/1961465.html

The details and differences between GAM, SGAM, PAM, IAM, DCM, and BCM in SQL Server