In my earlier post, we have discussed about Data,GAM,SGAM and PFS pages. In this post, let us try to understand about the IAM (Index Allocation Map) page.

In SQL server 2005 and later, there are three types of allocation units.

1. IN_ROW_DATA (btree and heap) allocation unit
2. LOB_DATA allocation unit
3. ROW_OVERFLOW_DATA allocation unit

While discussing about GAM/SGAM pages, we have noticed that, a GAM page can track  4GB worth space and they are repeated in the 4GB intervel. An IAM page track the pages/extents allocation in GAM interval of a partition for specific allocation unit of a table. Let us try to make it more clear .

Let us create a table with three column having data type of varchar(3000) and one column with data type LOB. This will ensure three type allocation to this table.

USE mydb
GO
CREATE TABLE IAMTable(
   Id INT,
   data1 VARCHAR(3000),
   data2 VARCHAR(3000),
   data3 VARCHAR(3000),
   Lobdata NTEXT)
GO

Now let us insert a record into this table 

INSERT INTO IAMTable VALUES (1,'A','B','C',N'Test')




Here we are inserting a record which will not generate row overflow. We will use the DBCC IND command to list the pages allocated to this table.

DBCC IND('mydb','IAMTable',1)

Fig 1

From the output it is clear that , SQL server allocated two IAM pages (page type 10) to this table to track the allocation of  IN_ROW_DATA and  LOB_DATA . As the size of existing record is not enough to create ROW_OVERFLOW_DATA, SQL server did not allocated a IAM page to track row_overflow allocation.

Let us try to insert a record which will force the SQL server to generate the row_overflow data.

DECLARE @data1 VARCHAR(3000)

SET @data1  = REPLICATE('A',3000)
INSERT INTO IAMTable VALUES (1,@data1,@data1,@data1,N'Test')
GO
DBCC IND('mydb','IAMTable',1)



Fig 2

Now the table has IAM pages for all three allocation units. If we have more partition on this table, there will be separate set of IAM pages for each partition. Below picture will give you a pictorial representation.


In short a heap/B tree structure can have minimum of one IAM page and maximum of (No. of partition X 3)  IAM pages. If the tables grows  further and pages allocated from different GAM interval, more IAM pages will be added.These IAM pages need to be linked together and this list is called IAM chain.

DBCC traceon(3604)
GO
DBCC page('mydb',1,126,3)

In the IAM header section , we can see following field.

• SequenceNumber : This is the position of the IAM page in the IAM chain. This increases by one for each page added to the IAM chain.
• Status: Unused
• Objectid : Unused
• Indexid : Unused
• Page_Count : Unused
• Start_pg:This is the GAM interval that the page maps.It store the first page id in the mapped GAM interval.

This part has referenced from  : http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units/

Let us execute the below script 7 time . After that there will be 9 records in the table (Two records are inserted as part of earlier step)

SET @data1  = REPLICATE('A',3000)
INSERT INTO IAMTable VALUES (1,@data1,@data1,@data1,N'Test')

Now let us see how the IAM page looks like