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.
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:
SELECT *,%%physloc%% AS physloc FROM dbo.tst AS T ORDER BY physloc;
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.
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:
SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) AS PLF FROM dbo.tst AS T ORDER BY PLF;
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.
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:
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;
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:
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.