ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF
如果暂时不使用快照事务(TX)隔离(iso)？
换一种说法,
为什么要启用,首先呢？
>为什么默认情况下不启用？
在sql Server中启用(但暂时不使用)的成本是多少？
–update：
启用数据库上的快照TX iso级别不会将READ COMMITTED tx iso更改为默认值.
你可以通过运行：
use someDbName; --( 1 ) alter database someDbName set allow_snapshot_isolation ON; dbcc useroptions;
最后一行显示当前会话的tx iso级别为(已提交).
所以,启用快照tx iso级别而不改变它不使用它等
为了使用它应该发布
--( 2 ) SET TRANSACTION ISOLATION LEVEL SNAPSHOT
UPDATE2：
我重复了[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基础(版本存储：简单示例)
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example.aspx
解决方法
>增加每行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,'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'),'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(),'DETAILED')
-- record size: 25
