To the page and back again

1

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

RowToPage1

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

RowToPage2

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

RowToPage3

I want to point out that everything in this post is undocumented so you use it at your own risk.

One thought on “To the page and back again

  1. […] Kenneth Fisher is looking for the database page tied to a particular row: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: