How get physical location of row.

Its undocumented command and gives physical location of the row.

This helps while doing advanced trouble shooting.

Its fun to get internals of MSSQL Server doing regular tasks.

First we will look at %%physloc%%

Lets see with example

CREATE TABLE test

  (

     id   INT,

     data VARCHAR(10)

  )

DECLARE @i INT

SET @I = 1

WHILE @i <= 100

  BEGIN

      INSERT INTO test

      VALUES      (@i, Replicate ('a', @i % 10))

      SET @i +=1

  END

SELECT *

FROM   test

Now we will look into physical location of row.

SELECT %%physloc%% ,  *

  FROM test;

This gives hexadecimal value for row location

Its not easy to decode we need function sys.fn_PhysLocFormatter(%%physloc%%) for this

so new query

SELECT sys.fn_PhysLocFormatter( %%physloc%%) , %%physloc%% ,  *

  FROM test;

Here we can see its showing location in more readable format.

Now we want to interpret this values.

We have one more function for this sys.fn_PhysLocCracker(%%physloc%%)

So new query

SELECT sys.fn_PhysLocFormatter( %%physloc%%) , %%physloc%% , b.* , t.*

  FROM test t

                CROSS APPLY

          sys.fn_PhysLocCracker( %%physloc%%)b;

So now we can read it more easily

It shows file_id , page_id and slot_id where row resides in database.

Do we want to get more details ?

Thanks to this function we details of file/page/slot.

Now We can use DBCC Page here.

DBCC PAGE

First we will see arguments for DBCC PAGE

1. Database name or ID

2. File ID

3.Page ID

4.Level

   

    0 header

    1 header and hex dump

    2 header and page dump

    3 header and detailed information

DBCC TRACEON(3604)

DBCC PAGE (master,1,479,3)

DBCC TRACEOFF(3604)