How to implement Snapshot Isolation correctly and tempdb issues?
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
It is "just a matter of executing the following", as stated in https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx, "If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows." Your second ALTER DATABASE command sets the READ_COMMITTED_SNAPSHOT ON so code does not need to specify that TransactionScope.
There are two sides to a performance coin, whenever one seeks an opinion about performance is "sufficient" versus "insufficient": Either "supply" is underwhelming or "demand" is overwhelming.... For this post, "supply" could refer to the performance and space used by tempdb, while the "demand" could concern the rate at which writes to tempdb occur. On the supply side, a variety of HW (from a single spindle 5400 RPM disk to arrays of SSDs) can be used. On the demand side, this isn't a SQL Server concern (although failing to properly normalize a database design can be a factor) as much as its a client code concern.
My SQL Servers see clients concurrently demanding approximately 50 writes/minute and 2000 batches/minute, where the writes are usually on the OTLP/short side. I have 1 TB of databases and a 30 GB tempdb, per SQL Server. All databases are in general normalized to 3rd normal form. All databases are running on SSDs. I have no concerns about the tempdb disk's IO throughput capacity being exceeded. As a result, I have had no concerns about enabling snapshot isolation on my systems. But, I have seen other systems where enabling snapshot isolation was attempted, but quickly abandoned.
Your system's experience can vary from any other respondent's system, by orders of magnitude. You should seek to profile/reliably replay your system's writes, along with replaying other uses of tempdb (including sorts), in order to come up with your own conclusions for your own system (for a variety of HW with sufficient space for your system's resulting tempdb size). Load testing should not be an afterthought :). You should also benchmark your tempdb disk's IO throughput capacity - see https://technet.microsoft.com/library/Cc966412, and be prepared to spend more money if its IO throughput capacity ends up being insufficient.