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

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!

Michelle

References:

• https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
• http://sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx

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