title: What are implications of SET-ting ALLOW_SNAPSHOT_ISOLATION ON?
body: <p>Should I run</p>
  <pre><code>ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF
  </code></pre>
  <p>if snapshot transaction (TX) isolation (iso) is not temporarily used?<br />
  In other words,</p>
  <ul>
  <li>why should it be enabled, in first place?</li>
  <li>Why isn't it enabled by default?</li>
  </ul>
  <p>What is the cost of having it enabled (but temporarily not used) in SQL Server?</p>
  <hr />
  <p><strong>--Update:</strong><br />
  enabling of snapshot TX iso level on database does not change READ COMMITTED tx iso to be default.<br />
  You may check it by running:</p>
  <pre><code>use someDbName;
  --( 1 )
  alter database someDbName set allow_snapshot_isolation ON;
  dbcc useroptions;
  </code></pre>
  <p>the last row shows that tx iso level of current session is (read committed).</p>
  <p>So, enabling snapshot tx iso level without changing to it does not use it, etc
  In order to use it one should issue</p>
  <pre><code>--( 2 )
  SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  </code></pre>
  <p><strong>Update2:</strong><br />
  I repeat the scripts from [1] but with SNAPSHOT enabled (but not switched on) but without enabling READ_COMMITTED_SNAPSHOT</p>
  <pre><code>--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
  </code></pre>
  <p>There no results/rows from from executing</p>
  <pre><code>select * from sys.dm_tran_version_store
  </code></pre>
  <p>after executing INSERT, DELETE or UPDATE</p>
  <p>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?<br />
  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))?</p>
  <p>[1]<br />
  Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)<br />
  <a href="https://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example" rel="nofollow noreferrer">Link</a></p>