I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?

eg. Will this affect recovery of the database? Is there anything else we need to do to take advantage of this?

I plan to execute these commands:


I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

I am looking into this because I am sick of locking problems in SQL Server 2005. I am hoping this might reduce the the occasional deadlocks our users see, help overall performance of our application and encourage our developers to do more than one operation per transaction without fear.

5 Answers 5



  1. If you have locking problems then you have a problem with your code: it isn't the database engine
  2. It isn't a magic bullet
  3. You may add more problems


It will also increase load on your tempdb and CPU. Also see:


Most important, snapshot isolations are not safe in many cases by default. Read "Snapshot isolation" (Wikipedia) for more on write-skew anomalies. The next section is "Making Snapshot Isolation Serializable" to get around this.

In general, therefore, snapshot isolation puts some of the problem of maintaining non-trivial constraints onto the user, who may not appreciate either the potential pitfalls or the possible solutions. The upside to this transfer is better performance.

Also see:


I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent writers from blocking other writers. There is no way around that.

In my experience, the additional load on the TEMPDB is negligible and the benefits of row versioning in reducing blocked readers is huge.

For reference, row versioning (snapshot isolation) is the method Oracle has used for decades to achieve isolation without blocking readers and the Oracle DBs I've worked on for nearly 20 years experience far less blocking issues than SQL Server does. Most SQL developers are hesitant to use snapshot isolation though because they've only tested their code against databases that use the default setting.


Couple of additional points to add to the other answers:

SET ALLOW_SNAPSHOT_ISOLATION ON only enables snapshot isolation in a database. To take advantage of it you have to recode and SET TRANSACTION ISOLATION LEVEL SNAPSHOT for the transactions you want it to apply to. The calling code will need to be changed to handle update conflict errors.

After SET READ_COMMITTED_SNAPSHOT ON, statements at read committed use row-versioning. Note, this is statement level row-versioning for reads only. For updates, the "real" row is retrieved and update locks applied. See the Summary of Behaviour section in Understanding Row-Versioning based Isolation Levels

Either route, without exhaustive testing you're likely to introduce a completely new set of problems to the system.


I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

Yes, this is correct.

Well worth reading the links in gbn's answer and I believe the same applies to Oracle's default MVCC as to SQL Server in Snapshot Isolation mode. I would add that if you understand the potential pitfalls, IMO the benefits far outweigh the added difficulties (speaking from an Oracle perspective) - and of course some locking problems legitimately go away, that is the point of MVCC (there is also a class of locking problems that will not go away due to code issues, but I am assuming you understand this).


We are using SNAPSHOT ISOLATION in all of our projects that use SQL Server DB. No more 1205 SQL Errors, that are caused not because of wrong application code, but from default page locking and row locking behaviour.

Performance impact is minimal and so far 7 years have passed, hundreds of millions operations have been processed in different systems, with no problems regarding SNAPSHOT ISOLATION.

Situations where several different threads are updating business critical information in single row in parallel are extremely exceptional, and the chances that SNAPSHOT ISOLATION will be the cause of any inconsistency problem are very much near zero.

If you have an OLTP system, that by design updates single row based on current row data in many threads, of course SNAPSHOTS are not acceptable in such cases.

  • Same here, completely agree.
    – influent
    Jan 3, 2022 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.