sql-server-2008c#-4.0transactions

Pros and cons of READ_COMMITTED_SNAPSHOT


What are the pros and cons of setting READ_COMMITTED_SNAPSHOT ON in SQL server 2008?

Actually i was running through the the problem of transaction deadlockS, but by setting READ_COMMITTED_SNAPSHOT ON on and disabling the Lock Escalation (only on table that used in transactions that causing deadlock). This finished the problem of deadlock by about 90%; but i am worried it might have some other problems like performance etc.

Any help will be highly appreciated.


Solution

  • Benefits of RCSI (Read Committed Snapshot Isolation):

    This isn't free, however; tempdb is used to keep what it calls a "version store." Which can mean:

    Also, row version information adds 14 bytes per row.

    Common alternatives to RCSI usually involve splitting up the write activity from reporting. This can be done with various HA technologies such as log shipping, mirroring + snapshots, or Availability Groups + read-only secondaries in SQL Server 2012.

    Some official doc references: