What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?

eg.
Will this affect recovery of the database?
Is there anything else we need to do to take advantage of this?

I plan to execute these commands:

ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

I am looking into this because I am sick of locking problems in SQL Server 2005. I am hoping this might reduce the the occasional deadlocks our users see, help overall performance of our application and encourage our developers to do more than one operation per transaction without fear.

Summary

• If you have locking problems then you have a problem with your code: it isn't the database engine

• It isn't a magic bullet

• You may add more problems

Load

It will also increase load on your tempdb and CPU. Also see:

• "Performance Impact: The Potential Cost of Read_Committed_Snapshot" (Linchi Shea)

Safety

Most important, snapshot isolations are not safe in many cases by default. Read "Snapshot isolation" (Wikipedia) for more on write-skew anomalies. The next section is "Making Snapshot Isolation Serializable" to get around this.


In general, therefore, snapshot isolation puts some of the problem of maintaining non-trivial constraints onto the user, who may not appreciate either the potential pitfalls or the possible solutions. The upside to this transfer is better performance.

Also see:

• "The Potential Dangers of the Read Committed Snapshot Isolation Level" (JimMcLeod, disputed in comments by Alex Kuznetsov)

• Deadlocked!: "read committed snapshot" Explained (Nick Berardi)

• Serializable vs. Snapshot Isolation Level, the Marble problem (Craig Freedman)

• Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent (Alex Kuznetsov)

StackExchange Database Administrators Q#5014, answer score: 56