All geek questions in one place

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 ran into the problem of blocking transactions, but setting READ_COMMITTED_SNAPSHOT ON on and disabling Escalation locks (only in the table used in deadlock transactions). This completed the deadlock problem by about 90%; but I am worried that it may have other problems, such as performance, etc.

Any help would be greatly appreciated.

+6
source share
1 answer

Advantages of RCSI:

  • provides a consistent presentation of data at the time the request is launched
  • without blocking
  • less locks / escalations

It is not free; tempdb is used to save what it calls a "version repository." It could mean:

  • space and I / O requirements to increase tempdb for version support
  • potential performance degradation if lengthy transactions require versions to persist for extended periods of time and / or if multiple versions exist

In addition, string version information adds 14 bytes to the string.

Common RCSI alternatives typically include separating write activity from reporting. You can do this using various HA technologies, such as sending logs, mirroring + snapshots, or availability groups + read-only persistents in SQL Server 2012.

Some official doc links:

+10
source

Source: https://habr.com/ru/post/917335/

More articles:


All Articles