Boosting Performance with READ_COMMITTED_SNAPSHOT

I had a problem with one of our database in our testing environment, it performs OK, but we know it can be faster. There are millions of data in that database. Everything are properly indexed and the stored procedure plan is optimum.

I was not aware about read committed snapshot option when the dba suggested this option. I was reading more information and was convinced that this setting would help performance.

We set this configuration, the application is now running really fast, processing hundreds of row per second. Our application has a lot of transactions per second, multiple read and multiple write within the same seconds. With this option, readers won’t block writers, and vice versa.

Take a look at this option, it may give the performance boost that your application needs. Kimberley Tripp has a good article on msdn about this subject. https://msdn.microsoft.com/en-us/library/ms345124.aspx. A long read, but it has good information.

Of course, you should always measure it, and nothing comes for free, it consumes more space in the temp db.