Today lets discuss some of the SQL Server Storage internals like what is GAM, SGAM, IAM etc
Basically in short words:
1.GAM(Global Allocation Map) tracks around a 64,000 extents(extent is 64KB which consists of 8 pages) which is around 4GB of space. GAM has bit indicating if the extent is currently available for allocation or not. GAM bit for a extent is 1 only if it’s not being used at the moment.
2.SGAM(Shared Global Allocation Map) tracks mixed extents with free space. A mixed extent is used by multiple objects like table, index etc. SGAM bit is 1 for a extent only if it has free space in it and it is mixed extent. Even SGAM tracks 64,000 extents same as GAM.
3.IAM(Index Allocation Map) tracks whether a extent is mixed or Dedicated.
An IAM page covers a 4-GB range in a file and is the same coverage as a GAM or SGAM page. If the allocation unit contains extents from more than one file, or more than one 4-GB range of a file, there will be multiple IAM pages linked in an IAM chain. Therefore, each allocation unit has at least one IAM page for each file on which it has extents. There may also be more than one IAM page on a file, if the range of the extents on the file allocated to the allocation unit exceeds the range that a single IAM page can record.
IAM page maps the extents in a 4-GB part of a database file used by an allocation unit. An allocation unit is one of three types:
•IN_ROW_DATA: Holds a partition of a heap or index.
•LOB_DATA:Holds large object (LOB) data types, such as xml, varbinary(max), and varchar(max).
•ROW_OVERFLOW_DATA: Holds variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.
There are four possible combinations of the above pages which are valid for a extent. They are :
| GAM | SGAM | IAM | Comments | |
| 0 | 0 | 0 | Mixed extent with all pages allocated | |
| 0 | 0 | 1 | Dedicated extent (must be allocated to only a single IAM page) | |
| 0 | 1 | 0 | Mixed extent with >= 1 unallocated page | |
| 1 | 0 | 0 | Unallocated extent |
Now what is PFS? It stands for Page Free Space. PFS pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.
After an extent has been allocated to an object, the Database Engine uses the PFS pages to record which pages in the extent are allocated or free. This information is used when the Database Engine has to allocate a new page. The amount of free space in a page is only maintained for heap and Text/Image pages or varchar(max)/varbinary(max)/XML and row-overflow data
For example take a Data file:
A PFS page is the first page after the file header page in a data file (page number 1). This is followed by a GAM page (page number 2), and then an SGAM page (page 3). There is a PFS page approximately 8,000 pages in size after the first PFS page. There is another GAM page 64,000 extents after the first GAM page on page 2, and another SGAM page 64,000 extents after the first SGAM page on page 3. The following illustration shows the sequence of pages used by the Database Engine to allocate and manage extents.
Note:IAM pages are allocated as required for each allocation unit and are located randomly in the file.
The formula for determining the page type is as follows:
GAM: Page ID = 2 or Page ID % 511232
SGAM: Page ID = 3 or (Page ID – 1) % 511232
PFS: Page ID = 1 or Page ID % 8088
If one of these formulas equates to 0, then you determine the page type.
Nice article..
Could i have more details regarding to IAM Pages. If a table is created then how the data stored in datapages including IAM pages?? What would be the oder of IAM page along with other pages such as PFS GAM SGAM? I am totally confused with this architecture… Can you give me Image presentation reg to table data storage architecture?