Sql-server – Does RCSI use less or same space than SNAPSHOT? Why

snapshot-isolationsql servertempdbtempdb-version-storetransaction

Trying to better understand row-versioning based transaction isolations – RCSI (Read Committed Snapshot Isolation and SNAPSHOT) – in SQL Server…

MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

  • *"For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons:
    • It consumes less tempdb space than snapshot isolation"*

which contradicts to the statement from Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":

  • "Row Versioning in Read Committed Using Row Versioning
    For queries that are running under read committed using row versioning, row versions are necessary only for the duration of each SELECT statement in the transaction. However, row versions are maintained until the end of the transaction. This is an important point to consider for tempdb storage. The storage impact will be the same as it would be for snapshot isolation."

So, why does RCSI use less (or same) space?

UPDATE:
Probably I formulated the original question bad but it implies that there are RTFM explanations and confirmations for both contradictory "theories" and I'd like to understand:
Which is correct?
Why is the one is really correct?
and
Why isn't another really correct (Why is another wrong?)?

SUBQUESTION:

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

Best Answer

Read committed snapshot isolation (statement-level snapshot isolation) potentially uses less storage than snapshot isolation (transaction-level snapshot isolation) because usually the row versions aren't required for as long a period of time after they've been generated.

For transaction-level snapshot isolation, the versions have to stick around for the entire duration of a transaction, which could be quite a long time (or quite a long time, as seen here). Of course, statements can run for a long time, too, but it's usually the case that a transaction will take longer.

To emphasise the point: A transaction running under snapshot isolation is guaranteed to see committed data as it existed at the start of the transaction. This means row versions have to be maintained until the whole transaction completes. Where the same transaction is running under RCSI, row versions can potentially be released after each statement. Overall, snapshot isolation tends to result in a larger version store size than RCSI on the same workload.

The same versions are generated with either or both settings enabled. I believe Kimberly was referring to storage impact in the sense of write rate, not storage used, so the two statements you mention aren't contradictory; just a little unclear or unspecific.

Related Question