I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember. My understanding is that if we were to rebuild the indexes offline, we would lose those additional 14 bytes. However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row. Is that correct?

  • 1
    Note that this is kind of like rebuilding your indexes with a <100% fill factor to reduce page-splits in post-rebuild DML. – David Browne - Microsoft Dec 7 at 16:15
up vote 8 down vote accepted

However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row. Is that correct?

This is true. REBUILD WITH (ONLINE = ON) and REORGANIZE will leave the 14 bytes as-is. A normal REBUILD will clear it.

If you disable RCSI, the 14 byte version tag will be cleared by any of the three operations you mentioned:

  • REBUILD
  • REBUILD WITH (ONLINE = ON)
  • REORGANIZE

For your future reference, it's not too hard to test this yourself. Just create a database and table, and then try the different operations, checking the row size each time.

USE [master];
GO
CREATE DATABASE AreSeaEssEye;
GO

USE [AreSeaEssEye];
GO

CREATE TABLE dbo.Foo (c1 INT NOT NULL);
INSERT INTO dbo.Foo (c1) VALUES (1);

ALTER TABLE dbo.Foo ADD CONSTRAINT PK_Foo PRIMARY KEY (c1);
-- Baseline (11)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER DATABASE AreSeaEssEye SET ALLOW_SNAPSHOT_ISOLATION ON;
UPDATE dbo.Foo SET c1 = 2;
-- After enabling RCSI and updating (25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REBUILD WITH (ONLINE = ON);
-- After online rebuild (still 25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REORGANIZE;
-- After reorg (still 25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REBUILD;
-- After normal rebuild (back to 11)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

And the results? As expected:

screenshot of RCSI row version changes

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.