Optimistic Isolation Levels - Pro SQL Server Internals - page 419

Figure 21-2. Readers and version store

As you can guess, while optimistic isolation levels help reduce blocking, there are some tradeoffs. Most significant
among these is that they contribute to tempdb load. Using optimistic isolation levels on highly volatile systems can
lead to very heavy tempdb activity and significantly increase tempdb size. We will look at this issue in greater detail
later in this chapter.
There is overhead during data modification and retrieval. SQL Server needs to copy the data to tempdb as well
as maintain a linked list of the version records. Similarly, it needs to traverse that list when reading data. This adds
additional CPU and I/O load.
Finally, optimistic isolation levels contribute to index fragmentation. When a row is modified, SQL Server
increases the row size by 14 bytes due to the version pointer. If a page is tightly packed and a new version of the row
does not fit into the page, it will lead to a page split and further fragmentation. This is very similar to the insert/update
pattern we discussed in Chapter 5, “Index Fragmentation.” Those 14 bytes will stay in the row even after records are
removed from the version store until the index is rebuilt.

■ if optimistic isolation levels are used, it is recommended that you reserve some space on the pages by using
FILLFACTOR less than 100. it reduces page splits due to row size increases because of the version store pointers.

Tip

Optimistic Transaction Isolation Levels
There are two optimistic transaction isolation levels: READ COMMITTED SNAPSHOT and SNAPSHOT . To be precise,
SNAPSHOT is a separate transaction isolation level while READ COMMITTED SNAPSHOT is a database option that changes
the behavior of the readers in READ COMMITTED transaction isolation level.
Let's examine these levels in depth.

READ COMMITTED SNAPSHOT Isolation Level
Both optimistic isolation levels need to be enabled on the database level. You can enable READ COMMITTED SNAPSHOT
(RCSI) with the ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON statement.