So we have a new DMF to allow us to view details for the page something I do very often during trainings using the DBCC Page Utility. I figured this DMF would be pretty useful since I can be queried and joined with sys.dm_db_database_page_allocations to provide some
interesting insight on the underlying pages mapped directly with table level allocation details. But after trying it out I must say I am a bit disappointed since it doesn’t really provide actual page internals. It just provide the page header details. To understand this let’s look at how I use the function.
When explaining indexes, you can see a video of it here I start off by looking for pages that have been allocated to the table I am analyzing using the query below.
SELECT database_id
,object_id
,index_id
,allocation_unit_type_desc
,allocated_page_page_id
,is_iam_page
,is_allocated
,page_free_space_percent
,page_type_desc
FROM sys.dm_db_database_page_allocations(5, NULL, NULL, NULL, 'Detailed')
The next step is to identity the IAM page, a data page and an index page and show participants what each of these pages look like internally. E.g. in the above screenshot the IAM PAGE is 228, the index page is 160 and the data page is 320.
SO let’s look at the IAM page first
DBCC page(5, 1, 228, 3)
Which gives me an idea of which pages have been allocated to this table as we can see from above, but here is the same IAM page queried using the DMF dm_db_page_info
So without paged level details this DM just shows me basic level information about my IAM page. But let’s move on to the Data Page, here is the output for data page 321 using DMF
And using DBCC utility, notice how I can actually see the contents of the page and the values for the columns in the table( towards the bottom)
Which is very useful when explaining things like encryption or variable datatypes. But my biggest complaint is when it comes to index pages. In the below screenshot I am showing the output from the DMF
Opposed to the DBCC utility
The DBCC command provides me with much more in depth information about the contents of the index and how the B tree looks based on the values of the key columns. Which is something I use extensively to explain how queries traverse the B tree to answer predicates. In the end just because of this one aspect it looks like I can’t really use the DMF dm_db_page_info for my trainings.
So where Can I use the DMF? At the moment I don’t see any particular use case where I might need to use this on a regular basis as part of troubleshooting SQL Server or for training’s, If something comes up I will post it here.
Please Consider Subscribing
