Troubleshooting SQL Deadlocks with Read Committed Isolation using Row versioning

Background : SQL server 2005 introduces a new isolation level called the row versioning with Read Committed Snapshot. This setting is useful in case where there are reader-writer conflicts only. With this setting on, SQL server maintains versions of each row of data in tempdb. Readers do not acquire a shared lock on the data being read and hence helps update operations to take place without any blocking. While reading the engine retreives the most recent committed version of the row.

How to : Enabling Read Committed Isolation using row-versioning is a simple database level configuration setting. Use the below statement to turn it on

   ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Case Study : In one of the recent engagements, we had a situation where several SQL deadlocks would occur under load which would eventually show up as an error in the event viewer on the web server causing the end user workflow to fail. Looking at the Deadlock graph in the SQL profiler it was found that the deadlocks were due to reader writer conflicts. We tested the application both with the default Read Committed and Read Committed Snapshot on for the same workload to see if it would eliminate deadlocks. The testing results looked pretty impressive as shown below.

Also the interesting fact about this feature is that it can be enabled without effecting the application.

Tradeoffs : As always, there are certain tradeoffs of setting this option on.

1) Impacts read performance due to chains of row versions

2) Impacts update performance

3) Increases Tempdb space usage.

Consider testing your application to check for  the performance impact of turning this setting on.

PS : Thanks to my collegues Jimmy May and Eddie lau for their help on this one