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.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
Why doesn't your "here" link appear in the Linked section on the right sidebar? Well, the correctness or validity of your answer depends on the answer to my question: "Why to link changes of the same records in SNAPHOT Isolation?" which (linking between version stores of the same record), according to quoted MSDN source, seems to be the same for both RCSI and SNAPSHOT –  Fulproof Dec 14 '13 at 15:29
    
@Fulproof: Dunno. But it does appear in the Related questions list. –  Jon Seigel Dec 14 '13 at 17:06
    
But this question doesn't appear in either "Related" or "Linked" section of your "here" question. I believe it is quite different in stackoverflow.com and other sites of StackExchange –  Fulproof Dec 15 '13 at 0:49

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.