SQL server

SQL allocation pages.

The extents in SQL server are managed by special pages in the data file. These files are know as allocation pages. The types of allocation pages are: IAM, GAM, SGAM, PFS, BCM, DCM.

IAM: Index allocation Map. Used for tracking [clustered indexes or heaps]  and/or non-clustered (249 allowed) indexes, and/or text storage. Each IAM tracks 4 GB of data (GAM interval) in a single file. Linked list IAM chains are used to track objects that are contained in different data files, or entities that are divided between the 4 GB boundary. Read More…

GAM: Global allocation Map. Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

SGAM: Shared global allocation Map. Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

PFS: Page free space. Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages. GAM, SGAM, PFS info taken from the post by Robert L. Davis on SQLserverCentral. Detailed info can be found in Paul Randal’s post.

BCM: Bulk changed map. Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit. When in the bulk-logged recover model, the BCM page holds information on which extents in the GAM interval (4GB) have changed since the last full backup.

DCM: Differential changed map. Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

It is this logic that is one of the checks used in the DBCC CHECKDB statement. The rows in a page are not necessarily arranged in their logical order.  The row order is managed by a slot array at the bottom of the page. See the image from BOL. This makes it easier for deletions and inserting into the deleted slots.

For an index of links to similar topics, click here.

I am using this post to consolidate a list of all the information about allocations and pages. Hence the mixed look.

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s