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

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

Rate This
  • Comments 15

(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 INTc2 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
  • 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
1 158 NULL NULL 133575514 1 1 72057594039959552 Row-overflow data 10 NULL
1 157 1 158 133575514 1 1 72057594039959552 Row-overflow data 3 0

(Again, I've stripped off the trailing 4 columns - they're all zero) 

Cool - we have another IAM page and a text page, in a row-overflow IAM chain. So something must have been pushed off-row into page (1:157). Let's have another look at the clustered-index data page, focusing on the 3rd row again:

Slot 2 Offset 0x4a7 Length 143

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x44DCC4A7

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

00000010: 008f8061 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 61616102 00004401 000000a6 †aaaaaaa...D.....

00000080: 2e000040 1f00009d 00000001 000000††††...@...........

UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

c1 = 3

Slot 2 Column 2 Offset 0x13 Length 100

c2 = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

c3 = [BLOB Inline Root] Slot 2 Column 3 Offset 0x77 Length 24

Level = 0 Unused = 68 UpdateSeq = 1

TimeStamp = 782630912

Link 0

Size = 8000 RowId = (1:157:0)

The 2nd varchar column is no longer stored on the page - instead the value have been replaced with an in-row blob root, pointing to an 8000 byte long blob fragment stored in slot 0 of page (1:157). If we dump it with DBCC PAGE we should see its got our value in:

DBCC PAGE (dbccpagetest, 1, 157, 3);

GO

And the output is (slightly curtailed):

PAGE: (1:157)

BUFFER:

BUF @0x02BE8D84

bpage = 0x03FB6000 bhash = 0x00000000 bpageno = (1:157)

bdbid = 9 breferences = 0 bUse1 = 8105

bstat = 0xc0010b blog = 0x1432159b bnext = 0x00000000

PAGE HEADER:

Page @0x03FB6000

m_pageId = (1:157) m_headerVersion = 1 m_type = 3

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000

m_objId (AllocUnitId.idObj) = 117 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594045595648

Metadata: PartitionId = 72057594039959552 Metadata: IndexId = 1

Metadata: ObjectId = 133575514 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 0 m_slotCnt = 1 m_freeCnt = 80

m_freeData = 8110 m_reservedCnt = 0 m_lsn = (21:107:19)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Blob row at: Page (1:157) Slot 0 Length: 8014 Type: 3 (DATA)

Blob Id:782630912

44DCC06E: 63636363 63636363 63636363 63636363 cccccccccccccccc

44DCC07E: 63636363 63636363 63636363 63636363 cccccccccccccccc

44DCC08E: 63636363 63636363 63636363 63636363 cccccccccccccccc

<snipped out for brevity>

44DCDF7E: 63636363 63636363 63636363 63636363 cccccccccccccccc

44DCDF8E: 63636363 63636363 63636363 63636363 cccccccccccccccc

44DCDF9E: 63636363 63636363 63636363 63636363 cccccccccccccccc

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Yep - that's our updated column.

Now, this is a contrived example to force columns off-row, but you can imagine a case where a column is unexpectedly pushed off-row in some rows of a table and suddenly a query slows down. You look at the fragmentation and the query plan and everything looks good but don't realize that you're taking random IOs to return the columns you've asked for.

That's all - now it's time for me to go and pack my diving gear and head off towards the sun (20 1/2 hours of flights over 31 1/2 hours to get to Bali tomorrow - ugh!). Have fun playing with DBCC over the holidays and I'll be back in January.

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • 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

  • It's the index ID of the clustered index.

  • 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

  • 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.

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

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

  • SQL Server 2005 allows rows to include variable length columns which do not actually fit in the maximum

  • 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.

  • I recently received a question about the storage of nonclustered index keys. I am assuming you are aware

  • I recently received a question about the storage of nonclustered index keys. I am assuming you are aware

  • I recently received a question about the storage of nonclustered index keys. I am assuming you are aware

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

    its not working ...

  • Hi

    Sorry its working fine

    good its very usefull to me

  • Yes it is working . dbccpagetest = your database name . Please change it to get it working/

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

  • bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

Page 1 of 1 (15 items)