檢視對象實體存儲位置

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 ( {'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.

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:

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.

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

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.

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