As BOL says an Index Allocation Map (IAM) page maps the extents in a
4-gigabyte (GB) part of a database file used by an allocation unit. An
allocation unit is one of three types:
- IN_ROW_DATA : Holds a partition of a heap or index.
- LOB_DATA : Holds large object (LOB) data types, such as xml, varbinary(max), and varchar(max).
- ROW_OVERFLOW_DATA : Holds variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.
Let’s see all this through an example.
USE master;
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name ='IAM')
DROP DATABASE IAM;
GO
-- Create a database
CREATE DATABASE IAM;
-- Create a table Test
in it
Use IAM
GO
CREATE TABLE Test(c1 int identity(1,1), c2 varchar(8000));
-- Populate this table with some data
DECLARE @i int= 1;
WHILE (@i < 16)
BEGIN
INSERT INTO Test VALUES (REPLICATE('a',8000));
SET @i = @i + 1;
END;
-- Check the pages allocated to this object. Clearly you can see the page 89 is an IAM page as the page type says 10. This IAM page is to manage the IN_ROW_DATA allocation unit. Since this IAM can cover upto 4GB of data size for this allocation unit there is only 1 IAM page required currently.
DBCC IND(IAM, Test, 1)
-- We will now check what is there in the IAM page for this object Test
DBCC TRACEON(3604)
DBCC PAGE(IAM, 1, 89, 1)
-- From the header below you can see the page type is 10 which means IAM page
Page @0x000000009C5A8000
m_pageId =(1:89) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId(AllocUnitId.idObj)= 29 m_indexId(AllocUnitId.idInd)= 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage =(0:0) m_nextPage =(0:0)
pminlen = 90 m_slotCnt = 2 m_freeCnt = 6
m_freeData = 8182 m_reservedCnt = 0 m_lsn =(32:223:7)
m_xactReserved = 0 m_xdesId =(0:0) m_ghostRecCnt = 0
m_tornBits = 0
-- The memory
dump of the IAM page clearly shows that the extent has been allocated to
it. This is only a partial output as the complete o/p
would have taken too much space.
Slot 1, Offset 0xbe,Length 7992, DumpStyle BYTE
Record Type= PRIMARY_RECORD Record Attributes = Record Size = 7992
Memory Dump @0x000000000F1CA0BE
0000000000000000: 0000381f 00008000 00000000 00000000 ..8.............
0000000000000010: 00000000 00000000 00000000 00000000 ................
0000000000000020: 00000000 00000000 00000000 00000000 ................
0000000000000030: 00000000 00000000 00000000 00000000 ................
0000000000000040: 00000000 00000000 00000000 00000000 ................
0000000000000050: 00000000 00000000 00000000 00000000 ................
-- Lets decode the initial few bytes. Specially the 3rd and 4rth byte which is 381f. 381f = 83f1 (reversed bytes) = 1f38 (read right to left) = 7992 (converted to decimal). As can be seen clearly this is the record size of the single record in the IAM. Lets now expand the 5th, 6th, 7th byte which is 000080. The first byte is 00 which means 00000000 (bits). Each bit in this byte covers 1 extent which means 8 pages. If we expand all 3 bytes (000080) it would result in the following bit pattern
-- 00000000
-- 00000000
-- 10000000
-- First byte, all 8 bits 0, which means no allocation for the first 64 pages. Similarly second byte all 8 bits are 0 (64 pages) and finally the third byte first 7 bits are 0 (56 pages) 64 + 64 + 56 = 184 (0 to 183 pages are free (no uniform extent allocated, all mixed extents). Extent starting at 184th page is allocated as represented by the last bit which is set to 1.
-- Let's populate 1 more extent.
DECLARE @i int= 1;
WHILE (@i <= 8)
BEGIN
INSERT INTO Test VALUES (REPLICATE('a',8000));
SET @i = @i + 1;
END;
-- We can clearly see that page 192 to 198 have been allocated from new extent
DBCC IND(IAM, Test, 1)
-- The extract from the IAM page has changed slighlty as can be seen below
DBCC TRACEON(3604)
DBCC PAGE(IAM, 1, 89, 1)
Memory Dump @0x000000000F6FA0BE
0000000000000000: 0000381f 00008001 00000000 00000000 ..8.............
0000000000000010: 00000000 00000000 00000000 00000000 ................
0000000000000020: 00000000 00000000 00000000 00000000 ................
-- 5th, 6th and 7th bytes are the same. However the 8th byte has now changed to 01. 00000001 : This means that the extent that starts with the page 192 is allocated for Test as (64 (1st byte) + 64 (2nd byte) + 64 (3rd byte)) = 192 (0 - 191). We can confirm this by looking at the IAM snapshot above. Pages 192 to 198 are allocated to Test. I will now show how adding one more column to this table will lead to row overflow data movement. This will happen as we already have each row very close the max available size of 8060.
ALTER TABLE Test ADD c3 varchar(500);
--
Updating this column with some data. We now check if the row over flow has
happened. Indeed it has. The
important thing is now there is one more IAM page 119 which is used to manage
the allocation unit
ROW_OVERFLOW_DATA. I leave it to you to investigate what is there in IAM page
119
UPDATE Test
SET c3 =REPLICATE('a',500)
-- You can notice the
IAM page for ROW_OVERFLOW_DATA and its data pages with page type 3
DBCC IND(IAM, Test, 1)
-- Finally we add one
more column as varchar(max) which is going to create the LOB_DATA allocation
unit
ALTER TABLE Test ADD c4 varchar(max);
UPDATE Test
SET c4 =REPLICATE('b',8070)
-- You can notice the IAM page for LOB_DATA and its data pages with page type 3. The IAM page is 175
DBCC IND(IAM, Test, 1)
