What are implications of SET-ting ALLOW_SNAPSHOT_ISOLATION ON?

Question

Should I run

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF

if snapshot transaction (TX) isolation (iso) is not temporarily used?
In other words,

• why should it be enabled, in first place?
• Why isn't it enabled by default?

What is the cost of having it enabled (but temporarily not used) in SQL Server?

---

--Update:
enabling of snapshot TX iso level on database does not change READ COMMITTED tx iso to be default.
You may check it by running:

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

the last row shows that tx iso level of current session is (read committed).

So, enabling snapshot tx iso level without changing to it does not use it, etc In order to use it one should issue

--( 2 )
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Update2:
I repeat the scripts from [1] but with SNAPSHOT enabled (but not switched on) but without enabling 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

There no results/rows from from executing

select * from sys.dm_tran_version_store

after executing INSERT, DELETE or UPDATE

Can you provide me with scripts illustrating that enabled SNAPSHOT tx iso level by ( 1 ) but not switched on by ( 2 ) produces any versions in tempdb and/or increase the size of data with 14 bytes per row?
Really I do not understand what is the point in versioning if it is enabled by ( 1 ) but not used (not set on by ( 2))?

[1]
Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)
Link

Answer 1

As soon as row versioning (aka. snapshot) is enabled in the database all writes have to be versioned. It doesn't matter under what isolation level the write occurred, since isolation levels always affect only reads. As soon the database row versioning is enabled, any insert/update/delete will:

• increase the size of data with 14 bytes per row
• possibly create an image of the data before the update in the version store (tempdb)

Again, it is completely irrelevant what isolation level is used. Note that row versioning occurs also if any of the following is true:

• table has a trigger
• MARS is enabled on the connection
• Online index operation is running on the table

All this is explained in 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.

Update

: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