How the first IAM page is found by SQL Server

Today, just of curiosity, I was checking the logs when the first allocation for a new table in a new database is made.

I can see that GAM was used to find a free extent and then a page from this extent is allocated to be used as a data page. Rows 8-12.

And then it allocates another page which isn't from the extent used before and uses this page as IAM. Row 13-16.

My question is, from where SQL Server finds this page and make it as IAM. Why this page is not allocated from an extent which is accessed by GAM.

I can see that GAM was used to find a free extent and then a page from
this extent is allocated to be used as a data page. Rows 8-12.

This extent allocation seen in your transaction log file is a uniform extent allocation for your allocation unit.


And then it allocates another page which isn't from the extent used
before and uses this page as IAM. Row 13-16.

This page allocation seen in your transaction log file is a page that is part of a mixed extent, as you noted this page is used by your IAM page.


from where SQL Server finds this page and make it as IAM. Why this
page is not allocated from an extent which is accessed by GAM.

Mixed extents

IAM pages are always part of mixed extents.

Proof of IAM pages always being part of mixed extents by Paul S. Randal on:

Inside the Storage Engine: IAM pages, IAM chains, and allocation units


A couple more things to note about IAM pages:


There are themselves single-page allocations from mixed extents and
are not tracked anywhere

And another article by Paul S. Randal:

Inside the Storage Engine: Anatomy of an extent


Mixed extents (SQL Server 2016 onward)


The use of mixed extents is disabled by default, and the only pages
that are mixed pages are IAM pages, and this cannot be disabled.

How are IAM pages allocated

In Pages and Extents Architecture Guide


IAM pages are allocated as required for each allocation unit and are
located randomly in the file. The system view,
sys.system_internals_allocation_units, points to the first IAM page
for an allocation unit. All the IAM pages for that allocation unit are
linked in a chain.

IAM pages are located randomly in the file but are chained together per allocation unit.

Dependent on version and traceflag 1118

With this information at hand and knowing that pre sql server 2016 (without TF 1118) tables could get mixed extents for the first 8 pages allocated, one extent could in fact hold both IAM pages & data pages.

Again proven in the previously mentioned article: Inside the Storage Engine: Anatomy of an extent


This means a mixed extent may hold a variety of page types
too, including IAM, data, index, or text pages.

By default starting with SQL Server 2016 (or with TF 1118 enabled) data pages will be part of uniform extents and IAM pages will be part of a different, mixed extent.

This should explain why your IAM page is mapped differently from your data page and it's uniform extent that was allocated.

Testing mixed extent allocation

Creating some test tables & inserting one row per table on a SQL Server 2012 instance without TF 1118.

CREATE TABLE dbo.IAM1(id int)
INSERT INTO dbo.IAM1(id)
VALUES(1); -- IAM2,IAM3,IAM4, ... tables created

We can then use DBCC IND to map these allocated uniform pages, in the 150 - 180 range on my end.

If we then check the SGAM settings for the database that only has one SGAM page allocated, on page id 3:

DBCC TRACEON (3604);
DBCC PAGE ('database', 1, 3, 3);

We get this information for our page ranges

(1:152)      - (1:168)      = NOT ALLOCATED       --> 2 FULL uniform extents                       
(1:176)      -              =     ALLOCATED                              
(1:184)      - (1:256)      = NOT ALLOCATED

We see that two uniform extents, from 152 to 168 are full.

We could then check the PFS to see if these are IAM pages or not.

DBCC TRACEON (3604);
GO
DBCC PAGE ('database', 1,1, 3);

This is the result

(1:152)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:153)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:154)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:155)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:156)      - (1:157)      =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:158)      - (1:162)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:163)      -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext
(1:164)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

5 IAM pages, not enough to get a full uniform extent of only IAM pages.
With one data page of one of the tables:

(1:163)      -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext

And 10 System data pages.

You will also see these single page allocations when uniform extents are used by reading the transaction log file:

```
Operation AllocUnitName Description
LOP_FORMAT_PAGE dbo.IAM3
LOP_BEGIN_XACT NULL AllocFirstPage;0x0105000000000005150000002204bf2bf6b1f23aeef85d6e8f150100
LOP_MODIFY_ROW dbo.IAM3 Allocated 0001:00000076
LOP_MODIFY_ROW Unknown Alloc Unit Allocated 0001:00000077
LOP_FORMAT_PAGE dbo.IAM3
LOP_HOBT_DELTA NULL

StackExchange Database Administrators Q#259899, answer score: 10