Row Versioning-Based Isolation Level (READ_COMMITTED_SNAPSHOT) for Microsoft SQL Server

Edit online

This feature is available in Microsoft SQL Server 2005 and later versions,

This feature can help in the following ways:
  • Resolve concurrency issues such as excessive blocking
  • Reduce deadlocks
The following T-SQL statements enable the READ_COMMITTED_SNAPSHOT for a database:
  • ALTER DATABASE <DB NAME> SET ALLOW_SNAPSHOT_ISOLATION ON;
  • ALTER DATABASE <DB NAME> SET READ_COMMITTED_SNAPSHOT ON;

This snapshot option increases the number of I/Os as well as the size of tempdb. It is important to have tempdb on fast disks as well as to have it sized according to your workload.

For more information about Using and Understanding Snapshot Isolation and Row Versioning, refer to the Microsoft Developer Network Web site, which can be accessed from: http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx.