SQL Server: Understanding IAM pages

Original: SQL Server: Understanding IAM pages

In previous articles, we discussed data pages, GAM and Sgam, and PFS pages. Today we will discuss the Index allocation Mapping (index Allocation Map:iam) page.

In SQL Server 2005 and later versions, the allocation unit (allocation units) has the following three types:

• IN_ROW_DATA 

  for storage heap partitions or index partitions, heap and b-tree.

• lob_data

  for storing large object (LOB) data types, such as  xml, varbinary (max)   and   varchar (max).

• row_overflow_data

  nvarchar, varbinary  or  sql_variant Variable-length data stored in the   column.  

As we discuss the GAM and SGAM pages, we notice that a GAM page can track 4GB of space and add one GAM page per 4GB space. An IAM page is used to track the allocation of a specified allocation unit of a table, in the GAM section of a partition, or a page or area. Let's demonstrate it by example.

We create a table that contains a 3-column varchar (3000) data type and 1-column LOB data type, as well as a column of int data types. In this way our table will contain the three categories of allocation units.

 
 1 USE InternalStorageFormat
 2 GO
 3 
 4 CREATE TABLE IAMTable(
 5 Id INT,
 6 col1 VARCHAR(3000),
 7 col2 VARCHAR(3000),
 8 col3 VARCHAR(3000),
 9 Lobdata NTEXT)
10 GO

We insert data into the table

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

The 1 data We insert here does not generate overflow (row overflow). We use the DBCC IND command to list the pages assigned to this table.

1 DBCC IND ('internalstorageformat','iamtable',1)

As you can see clearly, SQL Server allocates 2 IAM pages for our table (page Type 10), which is used to track the allocation of In-row data and LOB data. Because the current record size is not enough to create Row_overflow_data,sql server does not assign us an IAM page to track the allocation of Row_overflow_data.

Let's insert a bar that allows SQL serve to generate Row_overflow_data and view the page assignments with the DBCC IND command.

 
1 INSERT  INTO IAMTable
2 VALUES  ( 1, REPLICATE(‘A‘,3000), REPLICATE(‘A‘,3000), REPLICATE(‘A‘,3000), N‘Test‘ )
3 
4 DBCC IND(‘InternalStorageFormat‘,‘IAMTable‘,1)

Now the table already has all of these 3 types of allocation units. If we have more partitions on this table, there are a series of separate IAM pages for each district. Can give you a clear display.

In short, a heap or B-tree structure has at least one IAM page, with up to 3 times times the partitioning of IAM pages. If the table continues to grow, pages are allocated in different GAM intervals, and more IAM pages are added. These IAM pages are linked, and this list is called an IAM chain.

Now that we know what IAM pages are for, let's look at what it looks like in an IAM page. We know that the first 8 pages of a table or index are assigned to a single page in a mixed area. From the picture just now we know that page 175 is the IAM page used to track the In-row data allocation unit, and we use the DBCC PAGE command to see what the information is in the page.

 
1 DBCC TRACEON(3604)
2 go
3 DBCC PAGE(‘InternalStorageFormat‘,1,175,3)

In the iam:header area, we see the following fields:

• SequenceNumber = 0 This is the position of the IAM page in the IAM chain. Add 1 to each additional 1 IAM pages in the IAM chain.
• Status = 0x0 not used.
• ObjectId = 0 Not used.
• IndexID = 0 Not used.
• Page_count = 0 Not used.
• Start_pg = (1:0) This is the GAM interval for page mappings. The first page ID that holds the GAM interval in the map.

Single page allocation area: This shows the 1th 8 page allocated from the mixed zone. After page 8th, SQL begins to be allocated from the unified zone. So this section is only used in the first IAM page chain. Pages 174 and 210 are allocated from the mixed zone, which is consistent with the output of the DBCC IND (' Internalstorageformat ', ' iamtable ', 1).

Extent Alloc Area: The allocation unit area allocation is shown here.

We execute the following statement, inserting 7 records into the table, so that we have 9 records in the table, and then use the DBCC page to see the information of the IAM page.

 
1 INSERT  INTO IAMTable
2 VALUES  ( 1, REPLICATE(‘A‘,3000), REPLICATE(‘A‘,3000), REPLICATE(‘A‘,3000), N‘Test‘ )
3 go 7
4 
5 DBCC TRACEON(3604)
6 go
7 DBCC PAGE(‘InternalStorageFormat‘,1,175,3)

Can see

Red area : since 174,210, an increase of 212,214,215,217, 218,220, a total of 8 pages, that is, the mixed zone allocation is complete.

Blue Zone : The unified area has been allocated since 224.

Use DBCC IND to view the page assignments in exactly the same situation:

1 DBCC IND ('internalstorageformat','iamtable',  1)

SQL Server: Understanding IAM pages