http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/11/25/explore-file-physical-structure-iam/

Printed 2014/09/01 09:10AM

Explore file physical structure - IAM

By jamesxu98918, 2012/11/25


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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.