How to find the related file and the page numbers of records in SQL Server data table

Sometime, for some administration works, we need to know which file has been used for holding our records and the related page numbers. How can we easily find these information?

Generally we use DBCC IND and DBCC PAGE but there are 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. 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. Combing these two, we can get the information as we want. Here is an example;

SELECT p.BusinessEntityID, p.Title
    , p.FirstName, p.LastName
    , ph.*
FROM [Person].[Person] p
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph