Monday, December 16, 2013

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

Table

No comments: