CODEWITHSUNDEEP
sql-server-2008c#-4.0transactions
yo chauhan
yo chauhan

Reputation: 12315

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.

Upvotes: 13

Views: 13866

Answers (2)

Saeb Amini
Saeb Amini

Reputation: 24419

It's worth noting that Microsoft recommends enabling RCSI (Enable read committed snapshot isolation as best practice) via READ_COMMITTED_SNAPSHOT, and it is enabled by default in Azure SQL Databases:

To maximize the benefits of optimized locking, it is recommended to enable read committed snapshot isolation (RCSI) on the database and use read committed isolation as the default isolation level.

In Azure SQL Database, RCSI is enabled by default and read committed is the default isolation level

While there are some considerations already mentioned in the other answer, there are also memory benefits due to fewer locks:

In addition to reduced blocking, the lock memory required will be reduced. This is because readers don't take any locks, and writers take only short duration locks, instead of locks that expire at the end of the transaction.

That gave me a lot more confidence going with this enabled in a similar scenario, and my guess is that non-Azure SQL Servers are shipped without this as the default for compatibility reasons.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

Benefits of RCSI (Read Committed Snapshot Isolation):

  • provides a consistent view of the data at the time the query started
  • no blocking
  • fewer locks / escalations

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

  • space & I/O requirements for tempdb increase to maintain versions
  • potential performance degradation if long-running transactions require versions to be held for long periods and/or if many versions exist

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:

Upvotes: 18

Related Questions