Pages

Thursday 7 March 2013

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)


IAM page
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)


IAM page
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

14 comments:

  1. Sanjay Karmakar20 March 2013 at 11:34

    Nice Explanation. Add two more things in this series- 1) Covering Index (DBCC PAGE will give better insight on leaf and index level differences) and 2) How important is the key column position in index for a query. Above all, its a great series.
    ~Sanjay Karmakar

    ReplyDelete
    Replies
    1. Thanks you Sanjay for reading this article. I hope you are talking about Index series.I have avoided that two points to keep the article short. Two more post are coming up about the design consideration of index. In that I will include all these points

      Delete
  2. Thank you for the good post!

    I've one quick question here... First 8 pages of any DB object wii be stored in mixed extents. When the object size increases from 8 pages to 9 pages, IAM Page will assign a uniform index for the first 8 pages!

    Question here is, where will this 9th page of the same object goes to? Will IAM page assign it to a mixed extent or to an uniform extent?

    ReplyDelete
    Replies
    1. My bad, my question was wrong...!

      First 8 pages will be allocated in mixed extents. For the 9th record a uniform extent is allocated!

      Now if my DB Object have only 9 pages, and it won't exceed then 7 pages of this uniform extent are going to be unfilled ever and ever?

      Scenario 2:
      First 8 pages will be assigned in mixed extents. 9th to 16th pages will be assigned to an uniform extent.

      Now for the 17th page, again an uniform extent is allocated?

      Delete
    2. For the First Question, Yes the remaining space will be unused for ever. It will be marked as allocated but unused.
      For second question : Yes sql server will keep allocating only uniform extent once the size of the objects crosses the 8 page limit

      Delete
    3. Thank you for your response...!

      Is there any specific reason why SQL Server will allocated mixed extent for first 8 pages and uniform extents for next pages?

      Delete
  3. Explained in simple terms. Tx.

    ReplyDelete
  4. Can I force SQL server to use only uniform extent for a table?

    ReplyDelete
    Replies
    1. Hi Prem,

      No you can't force SQL Server to use only Uniform Extend's. It might be an SQL Server Architecture that when it start getting Data it will allocate pages from mixed extend and thereafter Uniform pages.
      Is that correct Nelson??

      Thanks,
      Vikas B Sahu
      vikasbsahu.blogspot.in

      Delete
    2. prior to SQL Server 2016:
      "Implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. Note that this trace flag affects every database on the instance of SQL Server."
      https://support.microsoft.com/en-us/kb/2154845

      SQL Server 2016:
      Trace flag 1118 for user databases is replaced by a new ALTER DATABASE setting – MIXED_PAGE_ALLOCATION.
      Default value of the MIXED_PAGE_ALLOCATION is OFF meaning allocations in the database will use uniform extents.
      The setting is opposite in behavior of the trace flag (i.e. TF 1118 OFF and MIXED_PAGE_ALLOCATION ON provide the same behavior and vice-versa).
      Syntax: ALTER DATABASE SET MIXED_PAGE_ALLOCATION { ON | OFF }
      https://msdn.microsoft.com/en-US/library/bb522682.aspx

      Delete
  5. who would read this ? please be specific and dont jihad everywhere

    ReplyDelete