SQL Server IAM Pages – So you know your IAM Page number

Hi Guys

Sorry for discontinuation from tune your queries.I was preparing for a interview and got to know very intresting thing.

As a DBA we all know about SQL Server IAM pages but exactly its page number is little bit difficult because when you run

select first_iam_page,root_page from sys.system_internals_allocation_units where allocation_unit_id=196608

first_iam_page  root_page

0x9D0000000100 0x9E0000000100

i am not putting screenshot bt its a hexadecimal value decimal value is 172623325561088 for first_iam_page.

Obviously this value is huge and i dont want to put it into dbcc page command,so i know somewhere i am wrong, i am not actually able to convert my root page value or first_iam_page value into decimal form but then thanks to kalen delaney book Microsoft SQL Server 2008 internals,its awesome book and Kalen seriously u deserve to be sqlqueen on twitter.In hexadecimal notation each set of two hexadecimal digit represents a byte so first we need to swap all bytes in reverse order

00  01  00 00 00 9D

The first two group represent  file number and rest four group represent page number here 0001 is 1 and 0000009D is equivalent to 157… so my file number is 1 here and page number is 157..so here goes my DBCC page to actually check whether its a IAM page or again i m doing some blunder.



This is a IAM page hence proved,but while observing screenshot i have noticed something and really intresting for sql server geeks to go further in depth …first 8 rows are IAM: Single Page Allocations after that its  IAM: Extent Alloc Status .???

this was just a knowledge sharing ,ill update some more blogs on tuning sql queries till then keep reading 🙂



Akash Gautam

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook


Leave a Reply

Your email address will not be published.