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

So now we have the page each row is located on. Of course this may not be the only page that a row has data in, but it is the page with the in row data.

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.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: indexes, microsoft sql server, T-SQL