Row Versioning

The SQL Server 2005 database engine implements a new functionality called row versioning that is designed to improve performance by avoiding reader-writer blocking. Whenever a transaction modifies a row, SQL Server uses the tempdb system database to maintain a copy of the original (that is, before image of the) row. If multiple transactions modify a row, multiple versions of the row are stored in a version chain. The versioning information is saved in 14 bytes of the row header. These 14 bytes include transaction sequence number (TXN) and are added to the header when the row is modified for the first time. Because a transaction can read the before versions of a row from the version store in tempdb, the reader does not block the writer.

The following are some of the new features in SQL Server 2005 that depend on row versioning functionality:

• Snapshot isolation level This is a new transaction isolation level that provides functionality similar to that of the REPEATABLE READ isolation level, without the issue of readers blocking writers.

• New implementation of the read-committed isolation level SQL Server 2005 provides a new database option called READ_COMMITED_SNAPSHOT that, when turned on, uses row versioning to provide the same functionality as the READ COMMITTED (the default) isolation level, without the issue of readers blocking writers.

• Multiple active result sets (MARS) MARS is an ADO.NET 2.0 feature that is supported by SQL Server 2005 to allow the execution of multiple statements on a single connection. Row versioning is used to version the rows affected by data modification statements issued by a MARS session.

• Online index operation This is a new high-availability feature introduced in SQL Server 2005 that allows you to create, rebuild, and drop indexes, while allowing concurrent access to the underlying table or clustered index data and any associated nonclustered indexes.

Row versioning is turned off by default. You can enable it by turning on the ALLOW_SNAPSHOT_ISOLATION database option and using the new snapshot isolation feature or by turning on the READ_COMMITED_SNAPSHOT database option and using the default READ COMMITTED isolation level. When row versioning is enabled, you must ensure that the tempdb database has adequate space to hold the version store.

You can monitor row versioning usage by using DMVs such as sys.dm_tran_current_transaction, sys.dm_tran_top_version_generators, sys.dm_tran_version_store, sys.dm_tran_active_snapshot_database_transactions, and sys.dm_tran_transactions_snapshot, as well as various Performance Monitor counterssuch as Free Space in tempdb, Snapshot Transactions, and Version Store Sizeunder the SQLServer:Transactions object. The database engine periodically removes from the version store in tempdb rows that are no longer needed to support snapshot or read-committed transactions.

More details on row versioning and snapshot isolation can be found in Chapter 9.