SQL Server IAM Page

SQL Server IAM Page

Index Allocation Map (IAM) pages keep track of the extents in a 4 GB section of a database file used by an allocation unit.

Allocation unit

An allocation unit is a set of pages belonging to a single partition in a table or index and comprises pages of one of three storage types:

  1. pages holding regular in-row data
  2. pages holding Large Object (LOB) data
  3. pages holding row-overflow data

Multiple IAM pages

A single IAM page covers only a 4 GB section of a single file.

  1. if the partition spans files
  2. if the file is more than 4 GB in size
  3. if the partition uses pages in more than one 4 GB section

IAM page structure

  1. 96-byte page header
  2. An IAM page header
    1. Contains eight page-pointer slots
  3. Contains bitmap
    1. A set of bits that map a range of extents onto a file
    2. Each bit of the bitmap represents an extent in the range
    3. 1, is allocated; 0, unallocated

Running the defined procedure sp_AllocationMetadata in the AdventureWorks2019, we can see the page number of the first IAM pages.

1
2
EXEC [sp_AllocationMetadata] N'HumanResources.Employee';
GO
sp_AllocationMetadata result

As we can see, the object Employee has 6 partitions. For the partition that has partition ID of 72057594056933376, its first IAM page is 2105.

Then let's take a closer look at the page 2105.

1
2
3
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2019', 1, 2105, 3)
DBCC TRACEOFF(3604)
Mode 3 result

The 8 slots listed under the Single Page Allocations mean they are in the mixed extent.

Following the Extent Alloc Status Slot are pages in uniform extent. The result suggests that page 12640 and page 12672 are allocated

We can check the allocation status by running the query:

1
2
3
4
5
6
7
8
9
SELECT allocated_page_file_id AS [FileID],
allocated_page_page_id AS [PageID],
page_type_desc,extent_page_id/8 AS ExtentID,
is_mixed_page_allocation,
extent_page_id AS [First Page in Extent],
extent_page_id+7 AS [LastPage in Extent],
is_allocated
FROM sys.dm_db_database_page_allocations(db_id(),object_id('HumanResources.Employee'),null,null,'detailed')
ORDER BY allocated_page_page_id
Allocation status result

As shown in the result, page 12640 and 12672 are allocated, and clearly there are (12672-12640)/8=4 extents between them, which is proved by the corresponding extent ID, 1580 and 1584.

Let's validate our observation by dive into the page in mode 1.

1
2
3
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2019', 1, 2105, 1)
DBCC TRACEOFF(3604)
Mode 1 result
  1. 0000381f is fixed.

  2. Slot 0 is for mixed extent; Slot 1 is for the uniform extent

  3. For string 00000000 0010 and 0100 00000000 in Slot 1

    Each bit in the table stands for one extent.

    Note we need to convert little-endian into normal binary.

    HEX 00 00 00 00 00 10
    Binary (Little endian) many 0 0001 0000
    Binary (normal) many 0 0000 1000
    HEX 01 00 00 00 00 00
    Binary (Little endian) 0000 0001 many 0
    Binary (normal) 1000 0000 many 0

    That is, for 10 01 we have 0000 1000 1000 0000 in binary, the difference is 4 extent, from 1580 to 1584.

    Endian example

SQL Server IAM Page
http://example.com/2022/06/26/SQL Server IAM Page/
Author
Jiacheng Xie
Posted on
June 26, 2022
Licensed under