January 18, 2016 by Kenneth Fisher
To the page
It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s own.
USE AdventureWorks2014 GO SELECT %%physloc%%, * FROM Purchasing.ShipMethod GO
But if we use one of the two system functions specifically designed to work with it things improve dramatically.
USE AdventureWorks2014 GO SELECT sys.fn_PhysLocFormatter(%%physloc%%) PhysLocFormatter, PhysLocCracker.*, Purchasing.ShipMethod.* FROM Purchasing.ShipMethod CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) PhysLocCracker GO
And back again
More commonly we have a page location and want to know the table associated with it. I was reading a post by Matan Yungman (b/t) recently. When Should You Use Identity as a Clustered Index Key? And one of the things he pointed out was that sometimes you’re looking at sys.dm_os_waiting_tasks, or sys.dm_exec_requests, etc and you have a page number for a resource being used. So what’s an easy way to get back to the object? Well you can use DBCC PAGE to look at the information in the page but honestly I find that a bit cumbersome. I find it quite a bit easier to use a system view that Jason Strate (b/t) posted about a while ago. sys.dm_db_database_page_allocations You can plug in the database id and include the page and file ids in the WHERE clause.
I should probably stop here and explain how the page numbers are usually formatted.
Sometimes you will see (1:820:3)
- Position 1: 1: This is the file id.
- Position 2: 820: This the page id.
- Position 3: 3: This is the slot id. The location within the page that the row exists.
Other times you see 6:1:820
- Position 1: 6: This is the database id.
- Position 2: 1: This the file id.
- Position 3: 820: This is the page id.
From this information you can easily get the table name and schema.
SELECT object_schema_name(object_id, database_id) Object_Schema, object_name(object_id,database_id) Object_Name FROM sys.dm_db_database_page_allocations(6,null,null,null,null) WHERE allocated_page_file_id = 1 AND allocated_page_page_id = 820
I want to point out that everything in this post is undocumented so you use it at your own risk.