Identifying the Physical Location of a Row
Occasionally I’ve needed to determine the physical location of a row stored in SQL Server. The code in this post uses the undocumented feature, %%PHYSLOC%%, which returns a binary representation in hexadecimal of the location of each row returned in a SELECT statement. The system table valued function, fn_PhysLocCracker, is used to decode the binary value returned by %%PHYSLOC%% to provide the file_id, page_id, and slot_id for each row.
Pablo Juan Salinas, 1871
We’ll need a table with some rows, and just to make this interesting, we’ll make a partitioned table. So, first we need a partition function:
|
1 2 3 4 5 6 |
CREATE PARTITION FUNCTION p ( int ) AS RANGE LEFT FOR VALUES (10, 20, 30); |
We will require a partition scheme as well:
|
1 2 3 |
CREATE PARTITION SCHEME ps AS PARTITION p ALL TO ([DEFAULT]); |
Now that we have the partition function and scheme in place, we can create the table, and populate it with a few rows across the partitions:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE dbo.pt ( i int NOT NULL CONSTRAINT pt_pk PRIMARY KEY CLUSTERED ) ON ps(i); INSERT INTO dbo.pt (i) SELECT TOP(30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.syscolumns sc; |
Each row contains a single value, ranging from 1 to 30, spread across thee partitions.
This code will display three rows, showing that three pages are occupied, and which pages belong to each partition:
|
1 2 3 4 5 6 7 8 9 |
SELECT dpa.allocated_page_file_id , dpa.allocated_page_page_id , dpa.partition_id FROM sys.schemas s INNER JOIN sys.objects o ON o.schema_id = s.schema_id CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa WHERE o.name = N'pt' AND s.name = N'dbo' AND dpa.page_type_desc = N'DATA_PAGE'; |
The results look like this:
╔════════════════════════╦════════════════════════╦══════════════╗ ║ allocated_page_file_id ║ allocated_page_page_id ║ partition_id ║ ╠════════════════════════╬════════════════════════╬══════════════╣ ║ 5 ║ 8184 ║ 1 ║ ║ 4 ║ 32 ║ 2 ║ ║ 3 ║ 40 ║ 3 ║ ╚════════════════════════╩════════════════════════╩══════════════╝
This code shows how to use the %%PHYSLOC%% function and the fn_PhysLocCracker function to display the details for rows in the table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
;WITH rowsource AS ( SELECT pt.i , plc.file_id , plc.page_id , plc.slot_id FROM dbo.pt CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc ) SELECT rs.* , dpa.partition_id FROM rowsource rs CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa WHERE dpa.allocated_page_file_id = rs.file_id AND dpa.allocated_page_page_id = rs.[page_id] ORDER BY rs.i , dpa.partition_id; |
The output from that query looks like:
╔════╦═════════╦═════════╦═════════╦══════════════╗ ║ i ║ file_id ║ page_id ║ slot_id ║ partition_id ║ ╠════╬═════════╬═════════╬═════════╬══════════════╣ ║ 1 ║ 5 ║ 8184 ║ 0 ║ 1 ║ ║ 2 ║ 5 ║ 8184 ║ 1 ║ 1 ║ ║ 3 ║ 5 ║ 8184 ║ 2 ║ 1 ║ ║ 4 ║ 5 ║ 8184 ║ 3 ║ 1 ║ ║ 5 ║ 5 ║ 8184 ║ 4 ║ 1 ║ ║ 6 ║ 5 ║ 8184 ║ 5 ║ 1 ║ ║ 7 ║ 5 ║ 8184 ║ 6 ║ 1 ║ ║ 8 ║ 5 ║ 8184 ║ 7 ║ 1 ║ ║ 9 ║ 5 ║ 8184 ║ 8 ║ 1 ║ ║ 10 ║ 5 ║ 8184 ║ 9 ║ 1 ║ ║ 11 ║ 4 ║ 32 ║ 0 ║ 2 ║ ║ 12 ║ 4 ║ 32 ║ 1 ║ 2 ║ ║ 13 ║ 4 ║ 32 ║ 2 ║ 2 ║ ║ 14 ║ 4 ║ 32 ║ 3 ║ 2 ║ ║ 15 ║ 4 ║ 32 ║ 4 ║ 2 ║ ║ 16 ║ 4 ║ 32 ║ 5 ║ 2 ║ ║ 17 ║ 4 ║ 32 ║ 6 ║ 2 ║ ║ 18 ║ 4 ║ 32 ║ 7 ║ 2 ║ ║ 19 ║ 4 ║ 32 ║ 8 ║ 2 ║ ║ 20 ║ 4 ║ 32 ║ 9 ║ 2 ║ ║ 21 ║ 3 ║ 40 ║ 0 ║ 3 ║ ║ 22 ║ 3 ║ 40 ║ 1 ║ 3 ║ ║ 23 ║ 3 ║ 40 ║ 2 ║ 3 ║ ║ 24 ║ 3 ║ 40 ║ 3 ║ 3 ║ ║ 25 ║ 3 ║ 40 ║ 4 ║ 3 ║ ║ 26 ║ 3 ║ 40 ║ 5 ║ 3 ║ ║ 27 ║ 3 ║ 40 ║ 6 ║ 3 ║ ║ 28 ║ 3 ║ 40 ║ 7 ║ 3 ║ ║ 29 ║ 3 ║ 40 ║ 8 ║ 3 ║ ║ 30 ║ 3 ║ 40 ║ 9 ║ 3 ║ ╚════╩═════════╩═════════╩═════════╩══════════════╝
Let me know in the comments below if you have any questions about how to determine the physical location of a row, or if you spot an error I may have made. Also, you could take a look at the rest of our posts on SQL Server Internals.
[…] Hannah Vernon breaks out the internals toolbag: […]