Explore file physical structure - IAM

1. Create sample db

use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)
GO

2. Create table

use test
GO
BEGIN TRANSACTION
GO
CREATE TABLE dbo.mytest
(
a int NOT NULL,
b varchar(500) NOT NULL,
d varchar(400) NULL,
)  ON [PRIMARY]
GO
ALTER TABLE dbo.mytest ADD CONSTRAINT
PK_mytest PRIMARY KEY CLUSTERED
(
a
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT


3. Insert test data

use test
GO


Declare @int int
set @int=1
while (@int <= 300)
begin
insert into mytest(a,b,d)
values(@int,CONVERT(char(500),@int),CONVERT(char(400),@int))

set @int=@int+1
end


4. check IAM chain

DBCC TRACEON(3604)
GO
dbcc
ind ('test','mytest',1)
GO
 output of the first 4 rows :

PageFID	PagePID	IAMFID	IAMPID	ObjectID	IndexID	PartitionNumber	PartitionID	iam_chain_type	PageType
1	154	NULL	NULL	2105058535	1	1	72057594038845440	In-row data	10
1	153	1	154	2105058535	1	1	72057594038845440	In-row data	1
1	155	1	154	2105058535	1	1	72057594038845440	In-row data	2
1	156	1	154	2105058535	1	1	72057594038845440	In-row data	1

here the IAM page is pagePID 154type =10).

5.  check the page 153

DBCC TRACEON(3604)
GO
dbcc page(test,1,154,3)
GO

IAM: Single Page Allocations @0x00000000109EA08E

Slot 0 = (1:153)                     Slot 1 = (1:155)                     Slot 2 = (1:156)
Slot 3 = (1:157)                     Slot 4 = (1:158)                     Slot 5 = (1:159)
Slot 6 = (1:176)                     Slot 7 = (1:177)                  


IAM: Extent Alloc Status Slot 1 @0x00000000109EA0C2

(1:0)        - (1:176)      = NOT ALLOCATED                            
(1:184)      - (1:208)      =     ALLOCATED                            
(1:216)      - (1:248)      = NOT ALLOCATED    

so the first 8 page are single-page allocation page, after first 8 page, sql use uniform extents.

6. you can verify this by DBCC Extentinfo

dbcc extentinfo ( 'test','mytest',1 )
go

file_id	page_id	pg_alloc	ext_size	object_id	index_id	partition_number	partition_id	iam_chain_type	pfs_bytes
1	153	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	155	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	156	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	157	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	158	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	159	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	176	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	177	1	1	2105058535	1	1	72057594038845440	In-row data	0x6000000000000000
1	184	8	8	2105058535	1	1	72057594038845440	In-row data	0x4040404040404040
1	192	8	8	2105058535	1	1	72057594038845440	In-row data	0x4040404040404040
1	200	8	8	2105058535	1	1	72057594038845440	In-row data	0x4040404040404040
1	208	7	8	2105058535	1	1	72057594038845440	In-row data	0x4040404040404000

so the first 8 pages are single-page allocations, then from page 184, the ext_size is 8(pages), they are uniform extent.

7. -T1118
Trace flag -T1118 can force uniform extent allocation, if you start sql server service with -T1118. then rebuild the index.
alter index PK_mytest on mytest rebuild
go

dbcc extentinfo ( 'test','mytest',1 )
go

file_id	page_id	pg_alloc	ext_size	object_id	index_id	partition_number	partition_id	iam_chain_type	pfs_bytes
1	184	1	8	2105058535	1	1	72057594038976512	In-row data	0x4000000000000000
1	192	8	8	2105058535	1	1	72057594038976512	In-row data	0x4040404040404040
1	200	8	8	2105058535	1	1	72057594038976512	In-row data	0x4040404040404040
1	208	8	8	2105058535	1	1	72057594038976512	In-row data	0x4040404040404040
1	256	8	8	2105058535	1	1	72057594038976512	In-row data	0x4040404040404040
1	264	1	8	2105058535	1	1	72057594038976512	In-row data	0x4000000000000000
1	272	5	8	2105058535	1	1	72057594038976512	In-row data	0x4040404040000000

now, all pages are in uniform extents(ext_size=8)

you can also get the IAM page number by query below:

select sys.fn_PhysLocFormatter(first_iam_page) from sys.system_internals_allocation_units sau
join sys.partitions pt on sau.container_id = pt.partition_id and pt.object_id = object_id('mytest')



reference:
http://sqlsimplified.blogspot.com/2012/01/as-bol-says-index-allocation-map-iam.html
http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-IAM-pages-IAM-chains-and-allocation-units.aspx