ONLINE Index Rebuild Adds One Byte To The Index Row

ONLINE Index Rebuild Adds One Byte To The Index Row by Paul White NZ

Closed

as By Design Help for as By Design

Type:	Bug
ID:	488761
Opened:	9/8/2009 8:03:22 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When rebuilding an existing non-clustered index ONLINE, the operation adds one extra byte per index row. Rebuilding the index OFFLINE removes the extra byte.

This is not the behaviour that adds 14 bytes of row-versioning overhead with an ONLINE rebuild (the present behaviour occurs when row-versioning isolation levels are disabled in the database).

The max_record_size for a nonclustered index on a single column INT on a heap is reported as 13 or 14 bytes depending on whether the last build or rebuild was OFFLINE (13 bytes) or ONLINE (14 bytes). I was expecting 12 bytes for the row (8 byte RID + 4 byte INT).

Details (expand)

English

SQL Server 2008 - Enterprise Edition

SQL Engine

Windows Server 2008

US English

CREATE DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC];
GO
ALTER DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC]
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC]
SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO
USE [82EAD17A-36AF-4A43-9EF8-164B948D31AC];
GO
CREATE TABLE dbo.ExtraByte (col1 INT NOT NULL);
GO
INSERT dbo.ExtraByte(col1) VALUES (4321);
GO
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;
GO
USE [master];
DROP DATABASE [82EAD17A-36AF-4A43-9EF8-164B948D31AC];
GO

ONLINE index build gives an index record size of 14 bytes in this example:

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)

13 bytes as for the OFFLINE build.

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)

X64

0 attachments

Thanks for contacting us. You will notice that with ONLINE, we are adding an extra byte at position 2 in the record. This extra byte is used internally for ONLINE build. This only happens for non clustered index.

Thanks
Sunil