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:
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.