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
SELECTstatement. The system table valued function,
fn_PhysLocCracker, is used to decode the binary value returned by
%%PHYSLOC%%to provide the
slot_idfor each row.
Read on for a demo. Unlike most demos of this sort, Max is using a partitioned table, so that’s something new.