Home » Blog » Where are my Rows? – Using the %%physloc%% Virtual Column

Where are my Rows? – Using the %%physloc%% Virtual Column

2014-06-25 - DMVs & CVs, General, Series, SQL Server Internals, Storage Wednesday

Introduction

Back in February, I told you that in SQL Server database files are organized in 8192-byte chunks, called pages, and that every row in every table ends up being stored in a page. Today I want to discuss the question, how to find out which page exactly a particular row is stored on.

The %%physloc%% Virtual Column

SQL Server makes answering that question easy for us. Since SQL 2008 we have access to a virtual column that provides exactly that information: %%physloc%%

This virtual column is undocumented and its use is potentially extremely resource intensive, so use it at your own risk.

The %%physloc%% virtual column returns a record locator value similar to the one you can find in the different lock related trace events. Let us look at an example:

[sql] SELECT *,%%physloc%% AS physloc
FROM dbo.tst AS T
ORDER BY physloc;
[/sql]

This query returns the following output in my environment:

Those hexadecimal values encode not only the file number and page number; they also specify the slot number within the page. However, the encoding is not really human readable.

The sys.fn_PhysLocFormatter function

To help with the readability of these values, SQL Server provides the - also undocumented - function sys.fn_PhysLocFormatter. This function turns the hex value returned by %%physloc into a nice human-readable format. You can use it like this:

[sql] SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) AS PLF
FROM dbo.tst AS T
ORDER BY PLF;
[/sql]

The output of this function has the format (FileNumber:PageNumber:SlotNumer). Below is the output of above query:

Rows 7, 8 and 9 for example are all stored on page (1:285), each one (naturally) in a different slot.

The sys.fn_PhysLocCracker function

But wait, there is more. The sys.fn_PhysLocFormatter function produces, as you have seen, a nicely formatted output. However, this output is not suited to be processed further by T-SQL. That is where the - again undocumented - sys.fn_PhysLocCracker table valued function comes in. Because sys.fn_PhysLocCracker is a table valued function, you have to use the CROSS APPLY statement to call it within a query:

[sql] SELECT * FROM dbo.tst AS T
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
ORDER BY FPLC.file_id, FPLC.page_id, FPLC.slot_id;
[/sql]

sys.fn_PhysLocCracker returns three columns: file_id, page_id and slot_id. Used with the same table you have seen in the previous examples, the output looks like this:

Summary

The undocumented %%physloc%% virtual column allows us to see where in the database file(s) each row of a table is located. Both, the sys.fn_PhysLocFormatter and the sys.fn_PhysLocCracker functions provide convenient ways to turn the encoded hexadecimal value that %%physloc%% returns into a more usable format.