0

I understand that Read Committed Snapshot (RCS) operates at statement level, and Allow Snapshot Isolation (ASI) operates at transaction level, and both store row versions in tempdb. I also understand that ASI must be enabled in the DB, and in the settings of each connection.

My question is concerning the level of load that is placed on tempdb under RCS versus ASI. For example, for the same workload, would the load on tempdb be similar, or would the load on tempdb typically be higher (because it has to store more row versions)?

Improve this question
6
  • littlekendra.com/2016/02/18/…
    – Marian
    Oct 9, 2018 at 12:05
  • @Marian thank so much for the link, but I need performance comparisons in Version Store, do you have other links like this?
    – tigeur
    Oct 9, 2018 at 13:49
  • I'm afraid you'll have to do your own tests for the type of application you have. The load is the most important here. You can read some example here
    – Marian
    Oct 10, 2018 at 8:32
  • @Marian thanks again, I'm really considering this suggestion. I just not started the lab yet because I'm creating the database schema, doing researching the best ways to how to start and spending a lot of time with App things.
    – tigeur
    Oct 10, 2018 at 9:18
  • @sepupic, I have clarified the question, please review if you could.
    – tigeur
    Oct 10, 2018 at 10:04

1 Answer 1

Reset to default
2

Just turning on the database setting will affect for how long the versioned rows need to stay in tempdb (since transactions that could be running in true snapshot would have repeatable read). I.e., enabling ASI will keep row versions for a longer time than enabling (only) RCSI.

As for performance for the applications we can't say. Only enabling ASI doesn't change client application behaviour unless they also explicitly ask to run with snapshot isolation. I.e., doesn't change default behaviour for apps.

Assuming that all your apps consistently are modified to use the snapshot isolation (pretty unlikely) then the performance will be pretty similar between the two. Readers aren't blocked by writers. You can get into edge cases with update conflicts and how they are handled with error handling, re-try etc. But there are so many dependencies here, so that cannot be generalized - and those are edge-cases anyhow so it is highly unlikely that they will have a measurable impact.

Improve this answer
4
  • Thank you @Tibor but I really need to know about the performance (IO, CPU, Len..) difference between both.
    – tigeur
    Oct 9, 2018 at 16:27
  • And that is impossible to answer since, as I tried to say, there are so many dependencies. The only way for you to know is for you to try with your load/application and measure that. Assuming not too many update conflicts, and not too many requiring repeatable read inside a transaction, then it should be the same performance. But if you do have lots of transactions where you need the original row (which RCSI doesn't give you) and perhaps also re-try logic if you become victim for an update conflict, then true snapshot will be more expensive.
    – Tibor Karaszi
    Oct 10, 2018 at 9:27
  • Thank you so much. I have clarified the question, please review if you could.
    – tigeur
    Oct 10, 2018 at 10:06
  • Enabling (even if not use?) ASI will cause the data (old row versions) in tempdb to be stored for a longer time. The data need to be there until all transactions that started when you did a modification has also completed - to give them repeatable read. How much longer depends on the length of the transactions, but, yes, the data will be there for a longer time, potentially having more than one old row version and in the end more space in tempdb.
    – Tibor Karaszi
    Oct 10, 2018 at 11:12

Your Answer

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

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