An Insight To IAM (Index Allocation Map) Pages in SQL

The blog post sheds light on the IAM pages used in SQL. SQL is the most preferred database manager and storage unit. The Index Allocation Map pages aka IAM pages are special kind of pages that are deployed to keep track of the allocation unit.Before moving on further with IAM pages let us just get a brief of what actually is an allocation unit.

SQL Server organizes data like tables, index, large object and other data in pages. Also the pages that store tables are not just assigned to tables itself. An allocation unit basically stores all these pages. As we know that SQL is not immune to database corruption, similarly the problems in these IAM pages also lead to SQL database corruption. So for the solution SQL Repair need to be considered.

What Is An IAM Page?

As mentioned earlier, IAM pages are used to assemble all the pages stored in a single allocation unit. The number of IAM pages in an allocation unit depends on the size of allocation unit. But there is atleast one IAM page in every allocation unit. The IAM Page has type 10. An IAM page normally accesses 4 GB space in a single file. These chunks of 4 GB space are referred as GAM intervals. 

Detailed Analysis Of IAM Page

Every IAM page has two sections-Page Header and Bit Map.

·         Header

The size of an IAM header is 96 bytes. Index allocation map header containing a base page address follows this page header. This header acts as a pointer to the first page of the index allocation map. Each IAM header consists of eight slots for mixed content page allocations.

·         Bit-Map

The other part of an IAM page is the 8000 byte bit map. The first bit of this bit map covers the area containing the page which is pointed by the first page pointer. It is not necessary for the IAM page to be located in the same interval as GAM intervals. 

Header Fields Of IAM Page

·         sequenceNumber

It’s the position of IAM page in an IAM chain. This increases by 1 whenever a new page is added.

·         status

This remains unused in the header.

·         objectId

It is the object ID of which IAM page is a part.

·         indexId

It is the index ID of which IAM page is a part.

·         page_count

It is the page IDs used by single page allocation array.

·         start_pg

It is the GAM interval mapped by the page.

·         Single Page Allocation array

These are the pages allocated from mixed extents.

Summary 
IAM Pages are used by SQL Server to keep track of allocation unit. Each IAM pages consists of two sections- Header and Bit map.