Storage Internal (7) IAM

I have been lazy those days. After finishing few articles for the topic other than Storage Internal, I would like to go back to this topic little bit more. As a continuation of last post, Storage Internal (6) Other Pages, this article is about IAM – Index Allocation Map. As the name of the page, it's is an allocation map for an "index". In SQL Server, the term index including almost everything – indexes, tables with clustered index, tables in the heap and statistics, they are all called indexes. In SQL Server 2000, when you list all the records in sysindexes, you will notice that when indid is 0, it's a HEAP table, if indid is 1, it's a clustered index, and otherwise it will be either a non-clustered index or statistics. In SQL Server 2005 and 2008, Meta data of indexes and statistics are logically separated to two DMVs, sys.indexes and sys.stats; however, both two views are essentially accessing an internal tabled named sys.idxstats. Let's go back to our topic instead of going too far into system dynamic management view.

IAM, page type 10, is similar to GAM and SGAM; it is a bit map to indicate which extent gets allocated for an index (or table) within a GAM/SGAM interval. An object will have at least one IAM page. A group of IAM will be chained together by m_prevPage and m_nextPage. A group of IAM pages for one object may be in the same/different GAM/SGAM interval. When the bit in IAM is one, the extent is allocated to whatever grouping of allocations the IAM page belongs to.

As IAM page is per object, it is part of the object's meta data. The address of IAM page is saved in sys.system_internals_allocation_units view. Based on this article, I created a view AllocationMetadata to see the page

create view AllocationMetadata
as
 select
   sp.object_id as ObjectID,
   OBJECT_NAME (sp.object_id) as ObjectName, 
   sp.index_id as IndexID,
   i.name as IndexName,
   i.type_desc as IndexType,
   sa.allocation_unit_id AS AllocationUnitID,
   sa.type_desc as AllocationUnitType,
   '(' + CONVERT (VARCHAR (6), CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) + SUBSTRING (sa.first_page, 5, 1))) + ':' + CONVERT (VARCHAR (20),CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) + SUBSTRING (sa.first_page, 3, 1) + SUBSTRING (sa.first_page, 2, 1) + SUBSTRING (sa.first_page, 1, 1))) +')' as FirstPage,
   '(' + CONVERT (VARCHAR (6), CONVERT (INT, SUBSTRING (sa.root_page, 6, 1) + SUBSTRING (sa.root_page, 5, 1))) + ':' + CONVERT (VARCHAR (20), CONVERT (INT, SUBSTRING (sa.root_page, 4, 1) + SUBSTRING (sa.root_page, 3, 1) + SUBSTRING (sa.root_page, 2, 1) + SUBSTRING (sa.root_page, 1, 1))) + ')' AS RootPage,
   '(' + CONVERT (VARCHAR (6), CONVERT (INT, SUBSTRING (sa.first_iam_page, 6, 1) + SUBSTRING (sa.first_iam_page, 5, 1))) + ':' + CONVERT (VARCHAR (20), CONVERT (INT, SUBSTRING (sa.first_iam_page, 4, 1) + SUBSTRING (sa.first_iam_page, 3, 1) + SUBSTRING (sa.first_iam_page, 2, 1) + SUBSTRING (sa.first_iam_page, 1, 1))) +')' AS FirstIAMPage
 from sys.system_internals_allocation_units sa
  inner join sys.partitions sp on sa.container_id = sp.partition_id
  left outer join sys.indexes i on i.object_id = sp.object_id and i.index_id = sp.index_id

When you list all the rows from this view, you will find some tables' root page is 0:0. For user object, it means there is no row in that object. Let's do a quick glance to IAM page.

create table test (id int identity(1,1), description char(20) default('abc'))
go
insert into test default values
go 8000
select FirstIAMPage from AllocationMetadata where ObjectName = 'test'

FirstIAMPage
-----------------------------
(1:158)

(1 row(s) affected)

dbcc traceon(3604)
dbcc page('test', 1, 158)
go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:158)


BUFFER:


BUF @0x0000000084FE4A00

bpage = 0x0000000084C54000           bhash = 0x0000000000000000           bpageno = (1:158)
bdbid = 9                            breferences = 0                      bUse1 = 5363
bstat = 0x1c0010b                    blog = 0x159bbb79                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x0000000084C54000

m_pageId = (1:158)                   m_headerVersion = 1                  m_type = 10
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 98     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594044350464                                 
Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 0
Metadata: ObjectId = 1017874793      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 90                         m_slotCnt = 2                        m_freeCnt = 6
m_freeData = 8182                    m_reservedCnt = 0                    m_lsn = (73:188:7)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 996806614               

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL                  DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now we know the allocation of extents are tracked in 3 places, GAM, SGAM and IAM. Let's see the combination of the bits statuses.

GAM	SGAM	IAM	Comments
0	0	0	Mixed extent with all pages allocated
0	0	1	Uniform extent, full, allocated to only a single IAM
0	1	0	Mixed extent with at least one unallocated page
1	0	0	Unallocated extent
1	0	1	Invalid status
1	1	0	Invalid status
1	1	1	Invalid status

If any statuses in a database file are invalid status(es), they can be detected and fixed by running DBCC CHECKALLOC.