次の方法で共有

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

Comments

  • Anonymous
    January 19, 2007
    DBCC IND ('dbccpagetest', 'rowoverflowtest', 1); Hey Paul. This is awesome. But I dont understand what the parameter '1' is for... Is it the same parameter that dbcc page has? Thank you

  • Anonymous
    January 23, 2007
    It's the index ID of the clustered index.

  • Anonymous
    March 01, 2007
    Hi Randal, I've been using the DBCC IND and DBCC PAGE for a few days and it's really interesting to understand how the things work in the background. I've tried to use the command from a client application but: dbcc page (mydb,1,164,3) returns either a table that I can use or a message that I can't. Is there a way to catch this message with ADO.NET? (even an annoying one) Thanks, Hassan

  • Anonymous
    March 02, 2007
    Hi Hassan, Your best bet is to use the table output from DBCC PAGE - cracking the text message version of the output is a real pain (which is why the table version is there :-) Thanks Paul.

  • Anonymous
    March 29, 2007
    For part two of my short series on data file shrinking, I want to look at how elements of your schemas

  • Anonymous
    June 28, 2007
    You’ve probably heard the term banded around but do you know what it means and what it means to the performance

  • Anonymous
    October 09, 2007
    SQL Server 2005 allows rows to include variable length columns which do not actually fit in the maximum

  • Anonymous
    October 23, 2007
    Paul, I've been following your posts for sometime now and must appreciate they are simply awesome! One of my clients recently asked me to analyze deadlocks. In the process I saw DBCC Page showing indid 0 for data pages that were part of a clustered index (in fact the table had just 1 index which was clustered). I thought may be indid for leaf-level pages of clustered indexes are shown as 0 to identify them as actual data pages and not index pages and hence also would have a page type 1 attributed. Q1. Am I correct in my understanding above? If not, what else could be the the reason? Q2. (on a seperate note) Do non-leaf level pages have clustering key or RID information as well, just like leaf level pages? If the answer is no, how does the read-ahead mechanism internally work. In fact it would be best if you could give some examples as well.

  • Anonymous
    March 16, 2008
    I recently received a question about the storage of nonclustered index keys. I am assuming you are aware

  • Anonymous
    March 16, 2008
    I recently received a question about the storage of nonclustered index keys. I am assuming you are aware

  • Anonymous
    April 16, 2008
    I recently received a question about the storage of nonclustered index keys. I am assuming you are aware

  • Anonymous
    July 15, 2009
    DBCC IND ('dbccpagetest', 'rowoverflowtest', 1); its not working ...

  • Anonymous
    July 15, 2009
    Hi Sorry its working fine good its very usefull to me

  • Anonymous
    December 09, 2010
    Yes it is working . dbccpagetest = your database name . Please change it to get it working/ DBCC IND ('dbccpagetest', 'rowoverflowtest', 1); , Please

  • Anonymous
    May 07, 2014
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

  • Anonymous
    April 04, 2016
    pls tell me anyone !what's the full form of dbcc....