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)




No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts