So lately I've been geeking out on SQL internals. My most recent find involves a couple of undocumented toys, DBCC Pageand 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 interpretationThe 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
