我有一个启用了RCSI的数据库,而且只要我还记得,我就一直在在线重建我的索引(SQL2014Enterprise)。我的理解是,如果我们要脱机地重建索引,我们就会损失额外的14个字节。但是,只要我们继续在线重建或重新组织,我们将保留每行14个字节。对吗?
发布于 2018-12-07 04:36:17
但是,只要我们继续在线重建或重新组织,我们将保留每行14个字节。对吗?
这是真的。REBUILD WITH (ONLINE = ON)和REORGANIZE将保持原来的14个字节。一个正常的REBUILD会清除它。
如果禁用RCSI,您提到的三个操作中的任何一个都将清除14字节版本标记:
REBUILDREBUILD WITH (ONLINE = ON)REORGANIZE对于你将来的参考来说,自己来测试并不难。只需创建一个数据库和表,然后尝试不同的操作,每次检查行大小。
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');结果呢?如预期的那样:

https://dba.stackexchange.com/questions/224359
复制相似问题