SQL Fool Adventures in SQL Tuning – a blog for the rest of us

Undocumented Function in SQL 2008

If you've been following my blog for a little while, you'll know that I'm a fan of SQL Server internals. There's a lot that can be learned or better understood by rolling up your sleeves and getting into the nitty-gritty of data pages (i.e. see my post on Overhead in Non-Unique Clustered Indexes). So imagine how happy I was when my co-worker Jeff shared an undocumented function with me today that retrieves the file number, page number, and slot number of a single record. Very cool! Well, at least to me. So now let's see how you can use it.

The fn_physLocCracker function can be called in the following way:

Select Top 100 plc.*, soh.SalesOrderID
From Sales.SalesOrderHeader As soh
Cross Apply sys.fn_physLocCracker (%%physloc%%) As plc;

Results (just a sample):

file_id     page_id     slot_id     SalesOrderID
----------- ----------- ----------- ------------
1           14032       0           43659
1           14032       1           43660
1           14032       2           43661
1           14032       3           43662
1           14032       4           43663

If you look at the sp_helptext for sys.fn_physLocCracker, %%physloc%% is apparently a virtual column that contains information on where the record is stored. In fact, you can even append %%physloc%% to your column list if you want to see how the information is stored. But for our purposes, we now have a file number, page number, and slot number. What do we do with it?

Well, you can use the investigation proc I wrote to retrieve the actual data page:

Execute dba_viewPageData_sp 
      @databaseName = 'AdventureWorks'
    , @fileNumber = 1
    , @pageNumber = 14032;

Results (just a sample):

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
 
SalesOrderID = 43659                 
 
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
 
RevisionNumber = 1                   
 
Slot 0 Column 3 Offset 0x9 Length 8 Length (physical) 8
 
OrderDate = 2001-07-01 00:00:00.000  
 
Slot 0 Column 4 Offset 0x11 Length 8 Length (physical) 8
 
DueDate = 2001-07-13 00:00:00.000

Neat, huh? So why would you use it to look up the data page and file number when you can just pass the table name and index name to my proc and retrieve data pages? Well, my investigation proc will retrieve data pages for any index type -- the fn_physLocCracker function will only retrieve data for the clustered index -- but it will not retrieve the data page for a specific record. So just something to be aware of.

That's all for now. Back to the #24HoursOfPASS!