The IAM architecture of the SQL Server 2008 storage structure

The index allocation Map,iam page tracks the extents used by an allocation unit in a 4 GB interval. An allocation unit is a set of pages that belong to a single partition of a datasheet or index. It consists of one of the following three types of pages: pages that contain regular inline data, pages with large objects (Large object,lob) data, and pages with row-overflow data. In fact, the data page type of SQL Server is similar to that of Oracle segments, where an object contains several segments, and a segment can only belong to one object.

A table with four partitions containing all three types of data (inline, lob, and row-overflow data) will have at least 12 IAM pages. A single IAM page also covers only the 4GB range of a single file, so if the partition spans multiple files, there will be multiple IAM pages, and if the file size exceeds 4GB and the partition uses a data page other than the 4 GB interval, there will be additional IAM data pages.

An IAM data page contains a header (an IAM page header) that contains 8 page pointer slots and a set of bits to map a range of extents to a file that does not necessarily have to be the same file as the IAM page. The page header contains the address of the first area within the IAM mapping scope. The 8-page pointer slot may contain pointers to some of the related object pages that are contained in a mixed-type zone, and only the first IAM page contains the values of those pointers for an object. Once an object occupies more than 8 pages, all of its extents are of a unified type-meaning that an object never needs more than 8 pointers to a page in a mixed-type area. If the row of data in a table has been deleted, the table can actually use less than 8 pointers. Each bit in the bit map represents a region within that range, regardless of whether the area is assigned to an object that owns the IAM. If a bit is open, the area associated with this scope is assigned to an object that has an IAM, and if a bit is closed, the area associated with that scope is not assigned to the object owning the IAM.

The IAM page is assigned to each object when it is needed and is located in a random location in the database. Each IAM page covers a possible range of about 512 000 pages.

Looking at the concept is always more boring, we can build a concrete example.

Before building the example, we first need to create a function that converts the address to a specific page number.

CREATE FUNCTION [Dbo].f_get_page (@page_num BINARY (6))

RETURNS VARCHAR (11)

As

BEGIN

Return (CONVERT (VARCHAR (2), (CONVERT (int,substring (@page_num, 6,1)) *power (2,8)) +

(CONVERT (Int,substring (@page_num, 5, 1))) +':'+

CONVERT (VARCHAR (11),

(CONVERT (Int,substring (@page_num, 4,1)) *power (2,24)) +

(CONVERT (Int,substring (@page_num, 3,1)) *power (2,16)) +

(CONVERT (Int,substring (@page_num, 2,1)) *power (2,8)) +

(CONVERT (Int,substring (@page_num, 1, 1))))

End

--Build a data table called Testiam based on Master.sys.objects

SELECT * into Testiam from master.sys.objects

-Then we get the IAM address of the Testiam object based on the information we know before, and convert the address to the corresponding page according to the F_get_page function.

SELECT Total_pages,used_pages,data_pages,

First_page,root_page,first_iam_page,

Testdb.dbo.f_get_page (first_page) first_page_address,

Testdb.dbo.f_get_page (root_page) root_address,

Testdb.dbo.f_get_page (first_iam_page) iam_address

From sys.system_internals_allocation_units

WHERE container_id in (SELECT partition_id from sys.partitions

WHERE object_id in (SELECT object_id from sys.objects

WHERE name in (' Testiam '))

DBCC page (testdb,1,80,3)