Why not use Snapshot isolation for everything read-only?

Question

Suppose that:

• I already have Snapshot isolation enabled
• I do not have Read Committed Snapshot Isolation enabled
• I have been strict with my READCOMMITTEDLOCK hints wherever I truly need them

then is there any reason at all to not use the Snapshot isolation level for all read-only queries? It seems that all of the costs from Snapshot isolation are paid at the moment that you enable the database-level setting rather than when you run queries under it.

Answer 1

You'd be mostly safe doing this, but not entirely.

is there any reason at all to not use the Snapshot isolation level

It is possible for a read-only transaction to encounter an error:

CREATE TABLE dbo.AccessMe
(
    x integer NULL
);
CREATE TABLE dbo.TruncateMe
(
    x integer NULL
);
-- Connection 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT COUNT_BIG(*) FROM dbo.AccessMe;
-- Connection 2
TRUNCATE TABLE dbo.TruncateMe;
-- Connection 1
SELECT COUNT_BIG(*) FROM dbo.TruncateMe;

The final SELECT fails with:

_{Example taken from my article The Snapshot Isolation Level.}

Any DDL change to the target table (by another session) will produce a similar error. This example is interesting because TRUNCATE TABLE is not always regarded as being DDL.

Creating a temporary table e.g. with SELECT INTO then attempting to add an index fails for the same reason. You may not regard this as a read-only operation.

The same error results if someone executes sys.sp_recompile against an object accessed by the snapshot transaction.

This particular limitation will be addressed when SQL Server supports metadata versioning.

Statistics creation or updating is not a problem.

Other limitations and restrictions for snapshot isolation are documented, for example you cannot access objects in another database if snapshot isolation is not also enabled there.