sqlskills-logo-2015-white.png

SQL Server 2008: New (undocumented) physical row locator function


One of the drawbacks of not being in the SQL team at Microsoft any longer is that I don’t know about all the undocumented features in the next release – I have to hunt around for them like everyone else :-(


So I was poking about in SSMS in 2008 CTP-6 and noticed a function called sys.fn_PhysLocCracker that I’d never heard of. Doing an sp_helptext on it gets the following output:



— 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


Cool – but something else I’ve never heard of %%physloc%% – what’s that? After playing around for a while, I figured out how to make it work.  Just to be confusing, there’s another identical version of the function called sys.fn_PhysLocFormatter – and that’s the only one I could get to work. Here’s an example:



CREATE TABLE TEST (c1 INT IDENTITY, c2 CHAR (4000) DEFAULT ‘a’);
GO
INSERT INTO TEST DEFAULT VALUES;
INSERT INTO TEST DEFAULT VALUES;
INSERT INTO TEST DEFAULT VALUES;
GO


SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], * FROM TEST;
GO


Physical RID       c1
—————–  ———–
(1:411:0)          1
(1:411:1)          2
(1:413:0)          3


It’s a physical-record locator function! Undocumented and unsupported (obviously), but hey, some of the best features are :-) It gives the database file, page within the file, and slot number on the page in the format (file:page:slot). I can think of a *bunch* of uses for this which I’ll be exploring over the next few months.


How cool is that?!?!

15 thoughts on “SQL Server 2008: New (undocumented) physical row locator function”

  2. Very cool, yet the possibility for misuse scares the *** out of me… I -almost- wish you hadn’t blogged it (but on the flip side, it certainly is useful — so thanks!)

    Reply

  3. In your page in "Create Function" you said "create function sys.fn_PhysLocCracker (@physical_locator binary (8))"
    In SQL you said SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], * FROM TEST;

    sys.fn_PhysLocCracker
    sys.fn_PhysLocFormatter

    Is it Cracker or Formatter?

    Reply

    1. Another useful bit of T-SQL Code – lists the File_ID, Page_ID, Slot_ID at the end :

      select *, %%physloc%%
      from AdventureWorks2008.Person.Address
      cross apply fn_PhysLocCracker(%%physloc%%)

      But, you probably had this code already …

      Reply

  7. I could get the cracker to work using it in the following way:

    — get’s the root page of:
    SELECT file_id, page_id
    FROM sys.system_internals_allocation_units iau
    INNER JOIN sys.partitions p ON iau.container_id = p.partition_id
    CROSS APPLY sys.fn_PhysLocCracker(iau.root_page)
    WHERE p.object_id = OBJECT_ID(@objectName)
    AND p.index_id = 1
    AND iau.type = 1

    Reply

  8. Michael Swart already made a similar comment, but once your TEST table is built, you can use sys.fn_PhysLocCracker like this:

    SELECT *
    FROM dbo.TEST
    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

    c1 c2 file_id page_id slot_id
    ———– ——— ———– ———– ———–
    1 a 1 7706 0
    2 a 1 7706 1
    3 a 1 7708 0
    4 a 1 7708 1
    5 a 1 7709 0
    6 a 1 7709 1

    Cheers,
    Chris

    Reply

  9. Hello Paul! Thank to the post.

    I made some tests and noted the %%physloc%% is not a RID, or at least, the pointer to the row in the heap.
    For example, if you run a query like “select col,%%physloc%% from MyHeap” and have a nonclustered index and QP choose use them, then %%physloc%% will return values according to page that belongs to the index.

    Do you know some hack like this, that allows us retrieve the stored RID in leaf level of the noncluster index?

    Thanks for the attention.

    Reply

    1. Correct – it’s the RID of the record used to retrieve the column(s) you ask for. If you want to force the heap, use an index hint of 0, if you want to force a nonclustered index, only ask for columns covered by the nonclustered index, and potentially use an index hint with that index ID.

      Reply

      1. Hi Paul!

        And if you cannot access a heap by some reason (e.g it is corrupted)?

        I think if I could access the value of RID that is stored on leaf level of index, I would be able to retrieve the values
        (for columns in an index) of the pages that were corrupted. Is this correct?
        Do you know something like %%physloc%% that returns the RID of a HEAP that is leaf level of a nonclustered index of a heap?

        Thanks again for you attention.

        Reply

        1. Yes – use the function to get the nonclustered index RID and then do DBCC PAGE on that page to get the heap RID. You’ll need to convert from the hex – four byte page, two byte file, two byte slot – all byte reversed within their fields.

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course