Optimistic Isolation Levels - Pro SQL Server Internals - page 428

Figure 21-12. Version generation and cleanup rates

There are three other performance counters related to optimistic isolation levels:

1. Snapshot Transactions. This shows the total number of active snapshot transactions.

2. Update Conflict Ratio. This shows the ratio of the number of update conflicts to the total

number of update snapshot transactions.

3. Longest Transaction Running Time. This shows the duration in seconds of the oldest
active transaction that is using row versioning.
There are a few Dynamic Management Views (DMVs) that can be useful in troubleshooting various issues related
to the version store and transactions in general. Look at Transaction Related Dynamic Management Views and
Functions section at: http://technet.microsoft.com/en-us/library/ms178621.aspx for further reading.

Summary
SQL Server uses a row-versioning model with optimistic isolation levels. Queries access “old” committed versions of
rows rather than being blocked by the incompatibility of shared (S), update (U), and exclusive (X) locks. There are two
optimistic transaction isolation levels available: READ COMMITTED SNAPSHOT and SNAPSHOT.
READ COMMITTED SNAPSHOT is a database option that changes the behavior of readers in READ
COMMITTED mode. It does not change the behavior of writers—there is still blocking due to (U)/(U) and (U)/(X)
locks incompatibility. READ COMMITTED SNAPSHOT does not require any code changes, and it can be used as an
emergency technique when a system is experiencing blocking issues.
READ COMMITTED SNAPSHOT provides statement-level consistency; that is, the query reads a snapshot of the
data at the time the query started.
SNAPSHOT isolation level is a separate transaction isolation level that needs to be explicitly specified in the code.
This level provides transaction-level consistency; that is, the query accesses a snapshot of the data at the time the
transaction started.
With SNAPSHOT isolation level, writers do not block each other, with the exception of the situation where both
sessions are updating the same rows. That situation leads either to blocking or to a 3960 error.
While optimistic isolation levels reduce blocking, they can significantly increase tempdb load, especially in OLTP
systems where data is constantly changing. You should consider the tradeoffs of using them at the implementation
stage, perform tempdb optimization, and monitor the system to make sure that the version store is not abused.