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