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

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:

We will require a partition scheme as well:

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:

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:

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:

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.

If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.