More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns

(Final blog post of the year for me. Its been a bit of a wild ride the last 6 months - 7 TechEds on 3 continents, 46 blog posts and some major life changes - but now things have calmed down and I should be back to more regular posting in 2007. Tomorrow I fly out to Wakatobi in Indonesia to go diving with my partner until January, which is some long overdue R&R. I'll post a link to some photos in January - she's already been out there for two weeks on a live-aboard and has over 3000 photos... In the meantime, I have all my loose-ends tied up at work so time to squeeze in a post. Thanks for all your comments and emails this year - I hope you all have a great holiday and may your pagers be silent throughout!)

At TechEd in November, in one of the sessions I did with Kimberly we demo'd both DBCC IND and DBCC PAGE in the context of following links to columns that have been pushed off-row when the row size exceeds 8060 bytes. The point of the demo is to show that even if an index is perfectly defragmented, the performance of a query that does a range scan may suffer if off-row columns are part of the result set because getting to those off-row columns involves random IOs.

What I'd like to do in this post is run through the demo we did, and at the same time introduce you to the undocumented DBCC IND command. This is like DBCC PAGE - it's used extensively internally but isn't documented or supported - use at your own risk. You already know about using DBCC PAGE to investigate page contents from my previous posts (here and here) so I'm not going to go into details.

To start off, we create a table with a schema that can cause rows to be greater than 8060 bytes. In previous versions, creating a schema like this has always been possible, but actually getting rows larger then 8060 was not. Checkout my previous post on IAM chains and allocation units in SQL Server 2005 for more details of large rows (and a few other cool new features in 2005). I'll assume that you've got a database called dbccpagetest that you're using.

CREATE

TABLE rowoverflowtest (c1 INT, c2 VARCHAR (8000), c3 VARCHAR (8000));

GO

CREATE

CLUSTERED INDEX row_cl ON rowoverflowtest (c1);

GO

Now we need to populate the table so we have something to look at.

INSERT

INTO rowoverflowtest VALUES (1, REPLICATE ('a', 100), REPLICATE ('b', 100));

INSERT

INTO rowoverflowtest VALUES (2, REPLICATE ('a', 100), REPLICATE ('b', 100));

INSERT

INTO rowoverflowtest VALUES (3, REPLICATE ('a', 100), REPLICATE ('b', 100));

INSERT

INTO rowoverflowtest VALUES (4, REPLICATE ('a', 100), REPLICATE ('b', 100));

INSERT

INTO rowoverflowtest VALUES (5, REPLICATE ('a', 100), REPLICATE ('b', 100));

GO

And now the new command - use DBCC IND to find out which page IDs to look at with DBCC PAGE.

DBCC

IND ('dbccpagetest', 'rowoverflowtest', 1);

GO

The output is (prettified in Excel):

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel
1 156 NULL NULL 133575514 1 1 72057594039959552 In-row data 10 NULL
1 155 1 156 133575514 1 1 72057594039959552 In-row data 1 0

(I've stripped off the 4 trailing columns, NextPageFID, NextPagePID, PrevPageFID, PrevPagePID so it all fits in the window. They're all zero.) 

The columns mean:

  • PageFID - the file ID of the page
  • PagePID - the page number in the file
  • IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)
  • IAMPID - the page number in the file of the IAM page that maps this page
  • ObjectID - the ID of the object this page is part of
  • IndexID - the ID of the index this page is part of
  • PartitionNumber - the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of
  • PartitionID - the internal ID of the partition this page is part of
  • iam_chain_type - see IAM chains and allocation units in SQL Server 2005
  • PageType - the page type. Some common ones are:
    • 1 - data page
    • 2 - index page
    • 3 and 4 - text pages
    • 8 - GAM page
    • 9 - SGAM page
    • 10 - IAM page
    • 11 - PFS page
  • IndexLevel - what level the page is at in the index (if at all). Remember that index levels go from 0 at the leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 - where there's a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))
  • NextPageFID and NextPagePID - the page ID of the next page in the doubly-linked list of pages at this level of the index
  • PrevPageFID and PrevPagePID - the page ID of the previous page in the doubly-linked list of pages at this level of the index

So you can see we've got a single page clustered index with an IAM page. Note that the page IDs returned may differ on your server. Let's look at the data page, focusing on the record for c1 = 3.

DBCC

TRACEON (3604);

GO

DBCC

PAGE (dbccpagetest, 1, 155, 3);

GO

Don't forget that we need to turn on T3604 first to get the output back to the console. The dump for the 3rd row is (remembering that slots are number from zero, so slot 2 is row 3):

Slot 2 Offset 0x216 Length 219

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x44DCC216

00000000: 30000800 03000000 04000003 00130077 †0..............w

00000010: 00db0061 61616161 61616161 61616161 †...aaaaaaaaaaaaa

00000020: 61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa

00000030: 61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa

00000040: 61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa

00000050: 61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa

00000060: 61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa

00000070: 61616161 61616162 62626262 62626262 †aaaaaaabbbbbbbbb

00000080: 62626262 62626262 62626262 62626262 †bbbbbbbbbbbbbbbb

00000090: 62626262 62626262 62626262 62626262 †bbbbbbbbbbbbbbbb

000000A0: 62626262 62626262 62626262 62626262 †bbbbbbbbbbbbbbbb

000000B0: 62626262 62626262 62626262 62626262 †bbbbbbbbbbbbbbbb

000000C0: 62626262 62626262 62626262 62626262 †bbbbbbbbbbbbbbbb

000000D0: 62626262 62626262 626262†††††††††††††bbbbbbbbbbb

UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

c1 = 3

Slot 2 Column 2 Offset 0x13 Length 100

c2 = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Slot 2 Column 3 Offset 0x77 Length 100

c3 = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

You can see that all the data is stored in-row. Now we update one of the columns so that the row size exceeds 8060 bytes.

UPDATE

rowoverflowtest SET c3 = REPLICATE ('c', 8000) WHERE c1 = 3;

GO

And run DBCC IND again to see if anything changed. The output is:

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel
1 156 NULL NULL 133575514 1 1 72057594039959552 In-row data 10 NULL
1 155 1 156 133575514 1 1 72057594039959552 In-row data 1 0