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.

SQL SERVER ERROR 8910

"Page P_ID in database ID DB_ID is allocated to both object ID O_ID1, index ID I_ID1, and object ID O_ID2, index ID I_ID2."

The above shown error message appears in SQL server and referred as Error 8910.This error is linked with Index Allocation Map (IAM) pages. These pages are used to heap or index tables in SQL database files. IAM pages support two types of entries IAM header and a bitmap. This error is basically encountered when there are inaccurate entries in IAM pages. That mean when in IAM array there are two entries of same page or one page has two allocated indexes. In SQL Server corruption is a common issue and Error 8910 is one of the issues causing corruption of database. User mostly go for instant manual solutions as he/she detect the error such running DBCC CHECHDB command, try to restore from backup or he/she will be going for Hardware diagnose to solve the issue and to recover the corrupted database.

Manual Solutions to Solve 

As discussed in the previous section manual solutions to resolve this error are to go for DBCC CHECKDB command, back-up restoration or hardware diagnose. But in mostly cases these solutions are success but what if these solutions are not able to repair the data. So to know more visit my Error 8910 blog.