Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  StanleyJohns Apr 4 '12 at 2:02

1 Answer 1

up vote 12 down vote accepted

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.

share|improve this answer
3  
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. –  Remus Rusanu Apr 3 '12 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 Mar 17 '13 at 15:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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