(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.
Now we need to populate the table so we have something to look at.
And now the new command - use DBCC IND to find out which page IDs to look at with DBCC PAGE.
The output is (prettified in Excel):
(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:
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.
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.
And run DBCC IND again to see if anything changed. The output is:
(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
Memory Dump @0x44DCC4A7
00000010: 008f8061 61616161 61616161 61616161 †...aaaaaaaaaaaaa
00000070: 61616161 61616102 00004401 000000a6 †aaaaaaa...D.....
00000080: 2e000040 1f00009d 00000001 000000††††...@...........
c3 = [BLOB Inline Root] Slot 2 Column 3 Offset 0x77 Length 24
Level = 0 Unused = 68 UpdateSeq = 1
TimeStamp = 782630912
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:
And the output is (slightly curtailed):
bpage = 0x03FB6000 bhash = 0x00000000 bpageno = (1:157)
bdbid = 9 breferences = 0 bUse1 = 8105
bstat = 0xc0010b blog = 0x1432159b bnext = 0x00000000
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
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)
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.
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?
It's the index ID of the clustered index.
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)
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 :-)
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
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
its not working ...
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