sql-server - SET-ting ALLOW_SNAPSHOT_ISOLATION ON 的含义是什么？

Question

我应该运行吗

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF

如果暂时不用快照事务(TX)隔离(iso)？
换句话说，

• 首先为什么要启用它？
• 为什么默认情况下不启用它？

在 SQL Server 中启用(但暂时未使用)它的成本是多少？

---

--更新:
在数据库上启用快照 TX iso 级别不会将 READ COMMITTED tx iso 更改为默认值。
您可以通过运行来检查它:

use someDbName;
--( 1 )
alter database someDbName set allow_snapshot_isolation ON;
dbcc useroptions;

最后一行表示当前session的tx iso级别为(read committed)。

因此，启用快照 tx iso 级别而不更改它不会使用它，等等为了使用它，应该发布

--( 2 )
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

更新2:
我重复了 [1] 中的脚本，但启用了 SNAPSHOT(但未打开)但没有启用 READ_COMMITTED_SNAPSHOT

--with enabling allow_snapshot_isolation
alter database snapshottest set allow_snapshot_isolation ON

-- but without enabling read_committed_snapshot
--alter database snapshottest set read_committed_snapshot ON
-- OR with OFF
alter database snapshottest set read_committed_snapshot OFF 
go

执行没有结果/行

select * from sys.dm_tran_version_store

执行 INSERT、DELETE 或 UPDATE 之后

您能否向我提供脚本，说明通过 ( 1 ) 启用 SNAPSHOT tx iso 级别但未通过 ( 2 ) 打开会在 tempdb 中产生任何版本和/或将数据大小增加为每行 14 个字节？
真的，我不明白如果它由 ( 1 ) 启用但未使用(不是由 ( 2) 设置)，那么版本控制有什么意义？

[1]
在SQL Server中管理TempDB:TempDB基础知识(版本存储:简单示例)
Link

Answer 1

一旦在数据库中启用了行版本控制(也称为快照)，所有写入都必须进行版本控制。写入发生在什么隔离级别下并不重要，因为隔离级别总是仅 影响读取。一旦启用数据库行版本控制，任何插入/更新/删除操作将:

• 增加每行 14 个字节的数据大小
• 可能在版本存储 (tempdb) 中创建更新前的数据镜像

同样，使用什么隔离级别是完全不相关的。请注意，如果满足以下任一条件，也会发生行版本控制:

• 表有触发器
• 已在连接上启用 MARS
• 在线索引操作正在表上运行

所有这些都在 Row Versioning Resource Usage 中进行了解释:

Each database row may use up to 14 bytes at the end of the row for row versioning information. The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

• READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.
• The table has a trigger.
• Multiple Active Results Sets (MARS) is being used.
• Online index build operations are currently running on the table.

...

Row versions must be stored for as long as an active transaction needs to access it. ... if it meets any of the following conditions:

• It uses row versioning-based isolation.
• It uses triggers, MARS, or online index build operations.
• It generates row versions.

更新

:setvar dbname testsnapshot

use master;

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

use [$(dbname)];
go


-- create a table before row versioning is enabled
--
create table t1 (i int not null);
go
insert into t1(i) values (1);
go

-- this check will show that the records do not contain a version number
--
select avg_record_size_in_bytes 
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 11 (lacks version info that is at least 14 bytes)


-- enable row versioning and and create an identical table
--
alter database [$(dbname)] set allow_snapshot_isolation on;
go

create table t2 (i int not null);
go

set transaction isolation level read committed;
go

insert into t2(i) values (1);
go

-- This check shows that the rows in t2 have version number
--
select avg_record_size_in_bytes
     from sys.dm_db_index_physical_stats (db_id(), object_id('t2'), NULL, NULL, 'DETAILED')
-- record size: 25 (11+14)

-- this update will show that the version store has records
-- even though the isolation level is read commited
--
begin transaction;
update t1 
    set i += 1; 
select * from sys.dm_tran_version_store;
commit;
go

-- And if we check again the row size of t1, its rows now have a version number
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 25