How to implement Snapshot Isolation correctly and tempdb issues?

Question

The Snapshot Isolation feature helps us to solve the problem where readers lock out writers on high volume sites. It does so by versioning rows using tempdb in SqlServer.

My question is to correctly implement this Snapshot Isolation feature, is it just a matter of executing the following on my SqlServer

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Do I still also have to write code that includes TransactionScope, like

using (new TransactionScope(TransactionScopeOption.Required, 
    new TransactionOptions { IsolationLevel = IsolationLevel.SnapShot}))

Finally, Brent pointed out his concern in this post under section The Hidden Costs of Concurrency, where he mentioned as you version rows in tempdb, tempdb may run out of space, and may have performance issues since it has to lookup versioned rows. So my question is I know this site uses Snapshot Isolation, anyone else uses this feature on large sites and what's your opinion on the performance?

Thx, Ray.

Answer 1

Depending on what data you are trying to select you can always use WITH ( NOLOCK ) to avoid all locks

If you need a committed reads and your transaction volume is such that you are having tempdb issue (not common, but still a major IO overhead) an alternative idea is to use a transactional replicated database to do the reads from

The replicated database will be transactionally accurate but slightly delayed ( < 1 second in most cases)

The replication process will not hold locks on the source database as it is made from completed log entries

You can have the replicated database on a separate server for even more performance if required?

You will need a system to monitor the replication status and handle link failure etc

If this technique will work for you depends on the reasons for the reads. For typical business reports this technique work well

Do NOT use this technique as part of a business transaction!

Answer 2

Hi,

For an awesome guide on how to work with SQL Server Row versioning technology, this white paper is a must read.

SQL Server 2005 Row Versioning-Based Transaction Isolation

Once you have digested the content, please feel free to drop me a line with any further questions you may have.