SQL Server : Understanding the IAM Page

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.

Now we have learned the usage of IAM page. Let us try to see what is there inside the IAM page.In the earlier post about fragmentation, we have discussed that, first eight pages of a table/index are single page allocation and will be allocated from mixed extents. From Fig 2 , we know that page now 126 (PagePID column) is an IAM page which track the the in_row allocation. Below is the a section of DBCC page command output.

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.

Single Page allocation section: These are the first 8 pages allocated from the mixed extent.After the 8th page, SQL server allocate uniform extents. So these section is used only in the first IAM page of the chain.Page number 120 and 176 are allocated from mixed extent. This is the same information we have from Fig 2 (Page type column value =1)

Extent Allocation Section: This section will describe the extents allocated to the allocation unit. 

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)

DECLARE @data1 VARCHAR(3000)

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 

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























All single page allocation are done and for the 9th record, SQL server allocated an extent which start from page no 192 to 199.

Note : I have learned a lot about internals from Paul S Randal blog . This is only an attempt to represent the way I understood. 

If you liked this post, do like my page on FaceBook