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
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 🙂