Blog Post

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 :

PageFIDPagePIDIAMFIDIAMPIDObjectIDIndexIDPartitionNumberPartitionIDiam_chain_typePageType
1154NULLNULL21050585351172057594038845440In-row data10
1153115421050585351172057594038845440In-row data1
1155115421050585351172057594038845440In-row data2
1156115421050585351172057594038845440In-row data1

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_idpage_idpg_allocext_sizeobject_idindex_idpartition_numberpartition_idiam_chain_typepfs_bytes
11531121050585351172057594038845440In-row data0x6000000000000000
11551121050585351172057594038845440In-row data0x6000000000000000
11561121050585351172057594038845440In-row data0x6000000000000000
11571121050585351172057594038845440In-row data0x6000000000000000
11581121050585351172057594038845440In-row data0x6000000000000000
11591121050585351172057594038845440In-row data0x6000000000000000
11761121050585351172057594038845440In-row data0x6000000000000000
11771121050585351172057594038845440In-row data0x6000000000000000
11848821050585351172057594038845440In-row data0x4040404040404040
11928821050585351172057594038845440In-row data0x4040404040404040
12008821050585351172057594038845440In-row data0x4040404040404040
12087821050585351172057594038845440In-row data0x4040404040404000

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_idpage_idpg_allocext_sizeobject_idindex_idpartition_numberpartition_idiam_chain_typepfs_bytes
11841821050585351172057594038976512In-row data0x4000000000000000
11928821050585351172057594038976512In-row data0x4040404040404040
12008821050585351172057594038976512In-row data0x4040404040404040
12088821050585351172057594038976512In-row data0x4040404040404040
12568821050585351172057594038976512In-row data0x4040404040404040
12641821050585351172057594038976512In-row data0x4000000000000000
12725821050585351172057594038976512In-row data0x4040404040000000

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating