Finding pages (or physical location) of records: %%PHYSLOC%% and fn_PhysLocCracker

There are many instances which we need to find the physical location, specifically the page, related to a record. Good example could be finding records in a corrupted page, or finding records’ distribution in pages. In most cases, I have use DBCC IND and DBCC PAGE but there two more great functions that can be used for finding the same.

The first function is %%PHYSLOC%%. This returns the RID (Record Identifier) as a hexadecimal value. The RID consists file number, page number and, record number.

SELECT p.BusinessEntityID, p.Title

    , p.FirstName, p.LastName

    , %%PHYSLOC%%

FROM [Person].[Person] p

The second function is a table-valued function which is fn_PhysLocCracker. It accepts the RID returning from %%PHYSLOC%% and returns three-columned table for file number, page number and, record number. Here is the way for using it;

SELECT p.BusinessEntityID, p.Title

    , p.FirstName, p.LastName

    , ph.*

FROM [Person].[Person] p

CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph