SQL Server – How the First IAM Page is Found

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                    Action 0 (HOBT_PAGE_COUNT) on rowset 72057594039173120. Leaf page delta: 0, Reserved page delta: 1, Used page delta: 1
LOP_MODIFY_ROW          dbo.IAM3    
LOP_CREATE_ALLOCCHAIN   NULL    
LOP_LOCK_XACT           NULL    
LOP_COMMIT_XACT         NULL    

---

EDIT

Thanks for replying, but my question was how does the SQL Server decide the page 0001:00000135 for IAM. It should have looked somewhere in the GAM or SGAM or even PFS for free space check but it doesn't. I created a new database and a new table multiple times. And found out that the first IAM page is always the same and also the same data page

IAM / Mixed page allocation

Below quote that explains tempdb contention when using mixed pages should have the same application for user databases and it's IAM page allocation.

When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This causes intense contention on the SGAM page when several mixed-page allocations are underway. This can cause the problems that are documented in the "Symptoms" section.

Source

Correct me if im wrong but the mechanism it looks like it uses to allocate IAM pages is to check the SGAM page to get a mixed extent with free pages (SGAM bit = 1) and then uses the PFS page to find the first unallocated mixed extent page to allocate the IAM page.

(If there are mixed extents with free pages, if not, it also has to search the GAM page).

This statement is built upon the understanding that allocating a new IAM page should be the same as allocating new mixed extent data pages.

Managing space used by objects

For each extent, the SQL Server Database Engine searches the PFS pages to see if there is a page that can be used

And Paul S. Randal Why PFS pages cannot be repaired

The answer is that the is-this-page-allocated-or-not information is not duplicated anywhere else in the database, and it’s impossible to reconstruct it in all cases.

Managing Extent Allocations

To find a mixed extent with free pages, the SQL Server Database Engine searches the SGAM for a 1 bit.

To allocate a mixed extent, the SQL Server Database Engine searches the GAM for a 1 bit, sets it to 0, and then also sets the corresponding bit in the SGAM to 1.