Introduction
What is it?
If the database option READ_COMMITTED_SNAPSHOT is ON, SQL Server uses a row-versioning implementation of the read committed isolation level. When this is enabled, transactions requesting read committed isolation automatically use the RCSI implementation; no changes to existing T-SQL code is required to use RCSI. Note carefully though that this is not the same as saying that code will behave the same under RCSI as when using the locking implementation of read committed, in fact this is quite generally not the case.
There is nothing in the SQL standard that requires the data read by a read committed transaction to be the most-recently committed data. The SQL Server RCSI implementation takes advantage of this to provide transactions with a point-in-time view of committed data, where that point in time is the moment the current statement began execution (not the moment any containing transaction started).
This is quite different from the behaviour of the SQL Server locking implementation of read committed, where the statement sees the most-recently committed data as of the moment each item is physically read. Locking read committed releases shared locks as quickly as possible, so the set of data encountered may come from very different points in time.
To summarize, locking read committed sees each row as it was at the time it was briefly locked and physically read; RCSI sees all rows as they were at the time the statement began. Both implementations are guaranteed to never see uncommitted data, but the data they encounter can be very different.
Benefits
Less locking and better performance
Problem
Change database snapshot isolation level to Read Committed Snapshot Isolation (aka “RCSI”)
Solution
Based on
https://www.brentozar.com/archive/2014/12/read-committed-snapshot-isolation-writers-block-rcsi
https://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level
DataSteve 