How do you find which page an index row is on – Corruption scenario

There’s an undocumented column that you can select %%PHYSLOC%% that gives the page:record:slot of the selected record. It gives the information back in hex, so there’s a companion function named sys.fn_PhysLocCracker that formats the output nicely.

For example, lets use Stackoverflow database.

Let’s say there’s a corruption issue where it says there’s a missing row for DisplayName = ‘anonymous’ and Reputation = 7777 in the DisplayName_Reputation nonclustered index (which is index ID 4) of the dbo.Users table. If you want to prove that the row is not missing, here is what you can do:

SELECT DisplayName,
       Reputation,
       physloc.*
FROM dbo.Users
WITH (INDEX = 4)
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS [physloc]
WHERE DisplayName = 'anonymous'
AND Reputation = 7777;

You are selecting the index keys for the nonclustered index and using an index hint to force that index to be used.

And if you can get any output, the row exists. In this case, you get:

DisplayName	Reputation	file_id	page_id	slot_id
Anonymous	7777	3	11284812	133

Pretty awesome.

You can also use this to find rows that need to use DBCC WRITEPAGE to around corruptions to allow data recovery from trashed databases – exhaustive, but possible.