Find actual Row Location by fn_PhysLocFormatter in your database in SQL Server 2008

There are many undocumented and unsupported functions available in Microsoft SQL Server. You won’t find it even in BOL since it is undocumented but many of them are really very useful. I was digging about filegroups, pages and memory allocation in Microsoft SQL Server 2008 and suddenly found one function “sys.fn_PhysLocFormatter”. This function is used to use for finding the real location of the row in SQL Server. It can give you File id, page no and slot no.  Let us see one small example.

create table RowLocater

(

      id bigint,

      FirstName char(2000),

      LastName char(2000),

      Country char(2000)

)

insert into RowLocater

SELECT 1,'Ritesh','Shah','India' Union ALL

SELECT 2,'Dharmesh','Kalaria','USA'

CREATE TABLE RowLocater1

(

      id bigint,

      FirstName char(200),

      LastName char(200),

      Country char(200),

)

insert into RowLocater1

SELECT 1,'Ritesh','Shah','India' Union ALL

SELECT 2,'Dharmesh','Kalaria','USA'

GO

select sys.fn_PhysLocFormatter(%%physloc%%)AS[Physical_Location],
FirstName 
from RowLocater

GO

--result of select is here in my server (BIG TABLE)

--Physical_Location  ID   FirstName                                            

-------------------------------------

--(1:2776:0)          1   Ritesh

--(1:2780:0)          2   Dharmesh

select sys.fn_PhysLocFormatter(%%physloc%%)AS[Physical_Location], 
FirstName  
from RowLocater1

GO

--result of above query in my server (SMALL TABLE)

--Physical_Location  id       FirstName
---------------------------------------------

--(1:2777:0)          1       Ritesh                                               --(1:2777:1)          2       Dharmesh                                                                                                                                                                                                 

One more interesting thing to observe, you might know that one page contain 8KB of data in SQL Server, our first table is big one so no pages can contain more than one row, however small table has small datatype and you can see both records falls under only one page (2777). This page # may be different in your own server but the concept will remain same.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com