Finding the Physical Location of a Row

Max Vernon breaks out the internals toolbag:

Occasionally I’ve needed to determine the physical location of a row stored in SQL Server. The code in this post uses the undocumented feature, %%PHYSLOC%%, which returns a binary representation in hexadecimal of the location of each row returned in a SELECT statement. The system table valued function, fn_PhysLocCracker, is used to decode the binary value returned by %%PHYSLOC%% to provide the file_id, page_id, and slot_id for each row.

Read on for a demo. Unlike most demos of this sort, Max is using a partitioned table, so that’s something new.