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! 🙂

Digging Around in SQL Internals – View Page Data

So lately I’ve been geeking out on SQL internals. My most recent find involves a couple of undocumented toys, DBCC Page and sys.system_internals_allocation_units.

DBCC Page will actually display the contents of a page. I found a blog post by Paul Randal, back when he still worked for Microsoft, describing the DBCC Page command. Here’s a summary:

dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.

The printopt parameter has the following meanings:

* 0 – print just the page header
* 1 – page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn’t have one, like allocation bitmaps)
* 2 – page header plus whole page hex dump
* 3 – page header plus detailed per-row interpretation

The per-row interpretation work for all page types, including allocation bitmaps.

We’ll come back to actually viewing a page in just a minute. Now that we know we can view page contents, how do we find out which pages contain the data we want to look at? This is where sys.system_internals_allocation_units can help. The sys.system_internals_allocation_units DMV is just like sys.allocation_units, except it has a few additional columns of interest: [first_page], [root_page], and [first_iam_page]. The query below will return the filenum and pagenum values, for use in the DBCC Page command, for a specific table.

Select Object_Name(p.object_id) As 'tableName'
    , i.name As 'indexName'
    , p.partition_number
    , au.type_desc
    , Convert (varchar(6),
      Convert (int, SubString (au.first_page, 6, 1) +
         SubString (au.first_page, 5, 1))) +
   ':' + Convert (varchar(20),
      Convert (int, SubString (au.first_page, 4, 1) +
         SubString (au.first_page, 3, 1) +
         SubString (au.first_page, 2, 1) +
         SubString (au.first_page, 1, 1))) As 'firstPage'
    , Convert (varchar(6),
      Convert (int, SubString (au.root_page, 6, 1) +
         SubString (au.root_page, 5, 1))) +
   ':' + Convert (varchar(20),
      Convert (int, SubString (au.root_page, 4, 1) +
         SubString (au.root_page, 3, 1) +
         SubString (au.root_page, 2, 1) +
         SubString (au.root_page, 1, 1))) As 'rootPage'
    , Convert (varchar(6),
      Convert (int, SubString (au.first_iam_page, 6, 1) +
         SubString (au.first_iam_page, 5, 1))) +
   ':' + Convert (varchar(20),
      Convert (int, SubString (au.first_iam_page, 4, 1) +
         SubString (au.first_iam_page, 3, 1) +
         SubString (au.first_iam_page, 2, 1) +
         SubString (au.first_iam_page, 1, 1))) As 'firstIAM_page'
From sys.indexes As i
Join sys.partitions As p
    On i.object_id = p.object_id
    And i.index_id = p.index_id
Join sys.system_internals_allocation_units As au
    On p.hobt_id = au.container_id
Where Object_Name(p.object_id) = 'ProductReview'
Order By tableName;

Note: the conversion code was borrowed from one of Kimberley Tripp’s posts, see the References section for the link.

Running the preceding query in the AdventureWorks database will produce the following:

tableName  indexName                           partition_number type_desc    firstPage  rootPage  firstIAM_page
---------- ----------------------------------- ---------------- ------------ ---------- --------- --------------
ProductRev PK_ProductReview_ProductReviewID    1                IN_ROW_DATA  1:770      1:773     1:771
ProductRev IX_ProductReview_ProductID_Name     1                IN_ROW_DATA  1:911      1:914     1:912

Great, now we have a starting place! Let’s now take DBCC Page out for a spin and see what it can do. I’m going to use the [firstPage] value for the IX_ProductReview_ProductID_Name index. Remember, the value preceding the colon (:) is your file number, and the value after it is your page number.

DBCC TraceOn (3604);
DBCC Page (AdventureWorks, 1, 911, 3);
DBCC TraceOff (3604);

You should get back something like the following. (Note: for brevity’s sake, I’m only displaying part of the results)

Allocation Status
GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            
FileId PageId      Row    Level  ProductID (key) ReviewerName (key)                                 ProductReviewID (key) Comments
------ ----------- ------ ------ --------------- -------------------------------------------------- --------------------- -------------------------------------------------------------------------
1      911         0      0      709             John Smith                                         1                     I can't believe I'm singing the praises of a pair of socks, but...(79025483b74e)

Let’s take a moment to consider why we’re seeing this. You’ll notice there’s 3 columns with (key) next to the name: [ProductID], [ReviewerName], and [ProductReviewID]. There’s one column without (key): [Comments]. This is exactly what I was expecting to see. Why? If you take a look at the details of IX_ProductReview_ProductID_Name, you’ll notice it’s a non-clustered index on only [ProductID, ReviewerName], with an included column, [Comments]. You’ll also notice the clustered index on Production.ProductReview is [ProductReviewID]. Remember, the composition of a non-clustered index includes the clustered index value as a sort of row pointer.

Not sure how to view the details of an index, like included columns? Check out my Index Interrogation Script.

I was interested in the ordering of the data. I wasn’t sure whether I’d find [ProductReviewID] first or last. But after looking at several of these examples, I’ve always found the clustered index to be nestled behind the non-clustered index columns and before the included columns (as exampled above).

That’s all I have for now. Try it out, play with the different printopt values, look at different types of pages, and have fun. Oh, and if anyone has a link to a blog post or a book that explains each of the items in the page header, I’d greatly appreciate it. 🙂

Happy Digging!



Source: http://sqlfool.com/2009/02/view_page_data/