Empire Data Systems

Undocumented physloc Virtual Column

Daniel AG by Daniel A G

SQL Server - Find Physical Location of Records

SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page. 

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE adventureworks2014 
go 
SELECT *, 
       %% physloc %% as physloc 
FROM   person.addresstype 
ORDER  BY physloc; 

sql-physloc

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT * 
FROM   person.addresstype 
       CROSS apply sys.Fn_physloccracker( %% physloc %% ) 
The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

sql-sys.fun_PhysLocCracker

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC Sp_helptext 
  'sys.fn_PhysLocCracker' 

which display the definition of sys.fn_PhysLocCracker

-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
------------------------------------------------------------------------------- 

CREATE FUNCTION sys.Fn_physloccracker (@physical_locator BINARY (8)) 
returns @dumploc_table TABLE ( 
  [file_id] INT NOT NULL, 
  [page_id] INT NOT NULL, 
  [slot_id] INT NOT NULL ) 
AS 
  BEGIN 
      DECLARE @page_id BINARY (4) 
      DECLARE @file_id BINARY (2) 
      DECLARE @slot_id BINARY (2) 

      -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot   
      --   
      SELECT @page_id = CONVERT (BINARY (4), Reverse ( 
                        Substring (@physical_locator, 1, 4))) 

      SELECT @file_id = CONVERT (BINARY (2), Reverse ( 
                        Substring (@physical_locator, 5, 2))) 

      SELECT @slot_id = CONVERT (BINARY (2), Reverse ( 
                        Substring (@physical_locator, 7, 2))) 

      INSERT INTO @dumploc_table 
      VALUES      (@file_id, 
                   @page_id, 
                   @slot_id) 

      RETURN 
  END 

The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.








Statistics Says It All

16

Years of Experience

3000

Gratified Students

100

Training Batches

9600

Training Hours

Subscribe

Please subscribe our technical blog to get recent updates.

Empire Data Systems

Social Links