September 7, 2009 at 9:44 pm
Hey all,
Does anyone have any insight into why rebuilding a non-clustered index ONLINE adds *one* extra byte to the row in the index?
Rebuilding the index offline removes the extra byte.
It doesn't matter whether the table is a heap or not, nor whether the index is UNIQUE or not...or any one of many different things I have tried. Suggestions gratefully received - full repro script below. (You will need 2K5 or 2K8 Developer, Enterprise, or Trial Edition to rebuild indexes online).
Paul
-- Create a test database
CREATE DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC];
GO
-- Turn off row-versioning isolation levels - 1
ALTER DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC]
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
GO
-- Turn off row-versioning isolation levels - 2
ALTER DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC]
SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO
-- Switch to the new database
USE [82EAD17A-36AF-4A43-9EF8-164B948D31AC];
GO
-- Create the test table
CREATE TABLE dbo.ExtraByte (col1 INT NOT NULL);
GO
-- Add a test row
INSERT dbo.ExtraByte(col1) VALUES (4321);
GO
-- Create the NC index
CREATE UNIQUE NONCLUSTERED INDEX nc1
ON dbo.ExtraByte (col1 ASC)
WITH (ONLINE = OFF, FILLFACTOR = 100, SORT_IN_TEMPDB = ON);
-- 13 bytes
SELECT initial_max_record_size = max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('[82EAD17A-36AF-4A43-9EF8-164B948D31AC].dbo.ExtraByte'), NULL, NULL, 'DETAILED')
WHERE OBJECT_ID('[82EAD17A-36AF-4A43-9EF8-164B948D31AC].dbo.ExtraByte') IS NOT NULL
AND index_id = 2;
-- Rebuild the NC index ONLINE
ALTER INDEX nc1 ON dbo.ExtraByte REBUILD WITH (ONLINE = ON, FILLFACTOR = 100, SORT_IN_TEMPDB = ON);
-- 14 bytes
SELECT online_rebuild_max_record_size = max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('[82EAD17A-36AF-4A43-9EF8-164B948D31AC].dbo.ExtraByte'), NULL, NULL, 'DETAILED')
WHERE OBJECT_ID('[82EAD17A-36AF-4A43-9EF8-164B948D31AC].dbo.ExtraByte') IS NOT NULL
AND index_id = 2;
-- Rebuild the NC index OFFLINE
ALTER INDEX nc1 ON dbo.ExtraByte REBUILD WITH (ONLINE = OFF, FILLFACTOR = 100, SORT_IN_TEMPDB = ON);
-- 13 bytes
SELECT offline_max_record_size = max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('[82EAD17A-36AF-4A43-9EF8-164B948D31AC].dbo.ExtraByte'), NULL, NULL, 'DETAILED')
WHERE OBJECT_ID('[82EAD17A-36AF-4A43-9EF8-164B948D31AC].dbo.ExtraByte') IS NOT NULL
AND index_id = 2;
/*
DBCC PAGE shows an extra zero byte in the
row when the NC index is rebuilt ONLINE.
This is removed by an OFFLINE rebuild.
Comparison of the two rows from DBCC PAGE:
00000000: 06010000 00††††††††††††††††††††††††††.....
00000000: 06000100 0000††††††††††††††††††††††††......
*/
GO
USE [master];
DROP DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC];
GO
More detail from DBCC IND and PAGE:
-- Example DBCC commands:
-- I got page 90 on file 1 for this on this run
DBCC IND ('82EAD17A-36AF-4A43-9EF8-164B948D31AC', 'dbo.ExtraByte', 2)
-- Show DBCC PAGE OUTPUT
DBCC TRACEON (3604);
-- Show the page header, per-row hex dumps, and the page slot array
-- (Change file 1 and page 90 depending on your results from DBCC IND)
DBCC PAGE('82EAD17A-36AF-4A43-9EF8-164B948D31AC', 1, 90, 1)
/* OFFLINE (13 bytes)
Slot 0, Offset 0x60, Length 13, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 13
Memory Dump @0x64D9C060
00000000: 06e11000 00370000 00010000 00††††††††.á...7.......
OFFSET TABLE:
Row - Offset
0 (0x0) - 96 (0x60)
*/
/* ONLINE (14 bytes)
Slot 0, Offset 0x60, Length 14, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 14
Memory Dump @0x64D9C060
00000000: 0600e110 00003700 00000100 0000††††††..á...7.......
OFFSET TABLE:
Row - Offset
0 (0x0) - 96 (0x60)
*/
September 8, 2009 at 5:44 am
The odd thing is that I expected the index rows to be 12 bytes wide - 8 bytes for the RID pointer back to the data (this is a heap) and 4 bytes to store the INT value. In fact it is 13 bytes before the online index rebuild, and 14 after.
So what are the extra two bytes?
This is the byte pattern in the index row (from DBCC PAGE):
0600 e110 0000 3700 0000 0100 0000
Reversed:
0000 0100 0000 3700 0000 e110 0600
Byte-swapped:
0000 0001 0000 0037 0000 10e1 0006
Left to right decoded:
0000 = Slot number part of the RID (slot 0)
0001 = File ID part of the RID (file 1)
0000 = High word of the Page ID part of the RID (zero)
0037 = Low word of the Page ID part of the RID (0x37 = page 55 decimal)
0000 = High word of the indexed column value (zero)
10e1 = Low word of the indexed column value (0x10e1 = 4321 decimal)
0006 = ?
00 - the mysterious extra byte for the ONLINE index rebuild
06 - always seems to be this value...
Paul
September 8, 2009 at 6:00 am
Paul, does it add a new byte EVERY time? If you rebuild the same index 5 times, does it increase by 5 bytes? Or does the mysterious extra byte ever change value?
Yes, I know, this is asking questions of a question instead of giving an answer.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 6:11 am
Bob Hovious (9/8/2009)
Paul, does it add a new byte EVERY time? If you rebuild the same index 5 times, does it increase by 5 bytes? Or does the mysterious extra byte ever change value?Yes, I know, this is asking questions of a question instead of giving an answer.
Hey Bob,
It adds the byte when you rebuild online (but only once) and takes it away if you rebuild it offline.
So, in the example, it is always 13 or 14 bytes, never 15 or more. That *would* be scary.
This is just something I want to understand.
It's all your fault anyway - it was your thread earlier that got me trying this scenario.... 😛
Paul
September 8, 2009 at 6:40 am
MY thread.. ?!? Just because I don't accept an execution plan for a table with 7 rows of data?
For the luvva small, fluffy bunnies....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 6:52 am
I would guess it has something to do with the basic difference of an online rebuild compared to an offline rebuild: keeping it up to date with ongoing operations.
So it must be a checkpoint kind of indicator.
Does it change after the full index has been rebuilt ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2009 at 6:53 am
:laugh: You knew the one I meant though!
September 8, 2009 at 6:59 am
ALZDBA (9/8/2009)
So it must be a checkpoint kind of indicator.Does it change after the full index has been rebuilt ?
I agree it seems related - after all rebuilding online with row-versioning enabled adds 14 bytes to every row - but 1 byte???
I guess I'm a little stressed that I don't know the width of a non-clustered index too!
If I understand you correctly (does it change after the rebuild) then I can't say at the moment - I haven't tested what happens to the extra byte when the table is modified. All I know at this stage is that an online build adds it and an offline build takes it away. It's 1am here now, and I have work in the morning, so I'll have to leave testing that until tomorrow sometime.
If anyone has any further thoughts in the meantime...please do contribute. I guess I'll have to add it to Connect or something at some stage unless someone can tell me what I am missing here.
Paul
edit: added the row-versioning reference
September 8, 2009 at 7:14 am
Breathe 😀
Out with the bad air ... In with the good ....
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2009 at 4:50 pm
ALZDBA (9/8/2009)
Breathe 😀
Out with the bad air ... In with the good ....go
Thanks. I feel better now.
Though the extra byte is still bugging me.
I will test modifications in a bit - I will eat my hat if it changes anything though 😎
September 9, 2009 at 7:00 am
September 9, 2009 at 7:35 am
So we have several possibilities at least:
(1) The presence of the byte is itself a flag of some sort.
After all it DOES tell you whether an index was an original or a rebuild.
(2) It's a harmless(?) bug.
Given, that it is endangering Emperor Paulpatine's sanity.
(3) It's reserved space for functionality that has not yet been implemented.
Or abandoned.
Any other thoughts?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 9, 2009 at 7:57 am
Bob Hovious (9/9/2009)(1) The presence of the byte is itself a flag of some sort.
After all it DOES tell you whether an index was an original or a rebuild.
It tells you that the index was built ONLINE last yes. An OFFLINE rebuild makes it go away, remember.
If a flag were required, you'd think it would be in the header or metadata - not every row of the index...?
Bob Hovious (9/9/2009)
(2) It's a harmless(?) bug.
Given, that it is endangering Emperor Paulpatine's sanity.
Probably - but I would love to know the details (probably too late for the sanity though).
If an index is built on an INTEGER, with an INTEGER clustering key (total 8 bytes) is a 12.5% overhead (the extra byte) really harmless? Smaller indexes may be available with worse percentages may be available 🙂
Bob Hovious (9/9/2009)(3) It's reserved space for functionality that has not yet been implemented. Or abandoned.
I still don't think it belongs in-row. The permanent extra byte (the 0x06 value) that remains whether the index is built online or offline is intriguing me also. I really thought that index rows were just the data plus the row locator (ignoring included columns and clustered index uniquifiers). Quite happy to have the fixed extra byte (though even Kalen's latest book makes no mention of it - she says the row is as I described too). Not so happy about the rogue ONLINE byte.
I wonder if this might be related to some internal process within the ONLINE index build that expands the 'permanent' extra byte to a smallint through necessity. A small mismatch between the offline and online index building code-paths? I doubt Microsoft will be in a hurry to tell me somehow.
Paul
September 9, 2009 at 8:03 am
I wonder if this might be related to some internal process within the ONLINE index build that expands the 'permanent' extra byte to a smallint through necessity. A small mismatch between the offline and online index building code-paths?
I think you've got it. If I were a betting man, I would put my money here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 9, 2009 at 8:38 am
Bob Hovious (9/9/2009)
If I were a betting man, I would put my money here.
Maybe. I hope I get a proper answer - I'm kinda nervous about posting about index sizes now 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply