I have a lot of deadlocks going on in my system.

I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it.

One of his concerns is that Snapshot Isolation slows down writes. This is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

A "normal" write can just write to the cache and then be done.

Is this how the row versioning works? Or is it more complex than that? Does it somehow do these in parallel?

Or are writes slower with Snapshot Isolation?

  • 2
    One issue to watch out for when you enable snapshot isolation: your row size will increase by 14 bytes. Make sure you have planned for this increase in capacity and any resulting page splits. Commented Apr 4, 2012 at 2:02

2 Answers 2


is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only 'wait' occurs at COMMIT time when the log has to be hardened. Is true that with versioning both the DB log and the tempdb log have to be hardened, but this does not necessarily implies higher latency (the IO should be paralel on different storage paths, the tempdb is stored on separate drive from your heavily used LDF, right?). For a full blown explanation read How It Works: Bob Dorr's SQL Server I/O Presentation I really hope your DBA understands this better than you are conveying it here.

As I mentioned in your other post: snapshot has no cost for INSERTS and the cost for updates and deletes can easily be mitigated. Row Versioning Resource Usage explains the trade offs. At this moment you should be probably testing with a realistic workload, which is the only way to properly asses the impact you would experience.

  • 4
    BTW consider this: have you, or your DBA, ever used Triggers in the past 7 years? Since SQL 2005 triggers are implemented using snapshot behind the scenes. Same goes if you ever used online index rebuild or MARS. How about if you check right now sys.dm_db_file_space_usage on your production server. If version_store_reserved_page_count is non-zero you are already using the versionstore. Commented Apr 3, 2012 at 22:17
  • I don't think tempdb log ever has to be hardened because redo is never run on tempdb. So the overhead is even lower.
    – usr
    Commented Mar 17, 2013 at 15:01

I believe there are other problems in the application if you are getting deadlocks. Snapshot isolation usually helps reduce wait locks but the root of deadlocks is usually different access methods in the application which should be prevented by adhering to a consistent pattern. Discussions on deadlocks are complicated and there are many resources dedicated to them.

Your DBA is right to have some concerns about changing the isolation level to snapshot since it increases the load on the tempDB. I recommend going to snapshot isolation in general, but I think that's only one piece of addressing your deadlocks. You may end up with dirty writes, where one transaction updates row A then B, and an other transaction updates row B then A.

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.