Why is snapshot isolation level off by default?

Question

In MS SQL Server, in order to run a transaction with SNAPSHOT isolation level, it first needs to be turned on at the database level.

I can only assume that turning this flag ON comes with some downsides the DBA needs to be aware of. But I don't know what they are.

Why is it not ON by default?

PS. Note I'm not talking about the READ_COMMITTED_SNAPSHOT version of the READ COMMITTED isolation level.

Answer 1

The costs are described on the page Choosing Row Versioning-based Isolation Levels.

Some are:

• When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level...

• Row versioning information requires 14 bytes added to the database row.

• Update performance can be slower due to the work involved in maintaining row versions...

There are other costs listed also, but the above are ones which seem to apply, whether or not any transaction within the database is actually using one of the Row Versioning-based levels.

Answer 2

Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version.

What this means is that there is additional computational and storage overhead on a per-transaction basis (bookkeeping), however by adding this additional load more operations can be going on concurrently--IF they do not collide. This form of "optimistic concurrency" is one where a small performance penalty is incurred to perform bookkeeping that lets you catch when something has collided, at which point you have to unwind one or both transactions. However, if no collision occurs, then both transactions went through at the exact same time.

A poor analogy would be assume you have two transactions that would take 5 seconds individually, so to do one after another they take a total of 10 seconds. However, if you were to spend an additional second on each one, you could do both at the same time. Sure each now takes 6 seconds (a 20% penalty) but both complete in 6 seconds total instead of 10.

If you don't have really high levels of concurrency, making each transaction slightly more expensive might be a waste of resources because it would reduce your individual transaction throughput. Therefore it is something your DBA would want to consider when trading off enabling at the database level because it impacts every transaction by changing how they are stored.