Read committed snapshot consumes less tempdb space as compair to Snapshot why?

Question

Read committed snapshot Isolation level consumes less tempdb space as compare to Snapshot Isolation level how?

Answer 1

In Read Committed Snapshot isolation level SQL Server stores only 1 "old" version of the row. This is most recent committed data. In Snapshot isolation level SQL Server stores multiple "old" versions of the row based on the oldest uncommitted snapshot transaction. 

Another difference that with snapshot isolation level we store more data. E.g. snapshot guarantees transaction level consistency. When you start snapshot transaction and obtain transaction LSN (for example selecting data from one table), at that point we will start storing old versions of the rows for all tables even if you did not read them in that transaction. When we use read committed snapshot it does not happen - it guarantees statement level consistency only.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Hi Dmitri,

Thank you so much, It helps a lot. Kindly provide me some reference material. Thanks