Read Committed Snapshot Isolation (RCSI)–Know before you use it for your Dynamics CRM Database

‘Read Committed Snapshot Isolation’ Or RCSI is short is something I continuously keep hearing in my CRM implementations every time there is any discussion related to the CRM performance.  On the lighter side of it, the term itself is very catchy isn’t it.

Since it’s not a database related blog, I will keep the concepts very simple here and focus more on the Dynamics CRM performance part of it. Typically from SQL sense, an isolation level is the degree to which one transaction must be isolated from resource or data modifications made by other transactions. The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior.

By default the isolation level for your CRM Organization connection is ‘Read Committed’. You can check by running the below command. You would get a result similar to the below.

DBCC UserOptions

In ‘read committed’ isolation, your SQL statement views the most-recently committed data as of the moment each item is physically read. To put it simple, for read committed isolation, each row is locked briefly and physically read.

RCSI improves on this by removing row locking part totally while reading of rows. It provides transaction with the point-in-time view of the committed data, where the point-in-time is the time when the transaction starts executing. When this isolation level is maintained, SQL server maintains row versioning and during read no shared locks are acquired physically because the entire transaction reads from the row version store rather than being accessed directly.

To put in the words of MSDN – “Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server”

From the explanation above, it make obvious sense that it would result in increase in performance. And normally whenever there is any performance discussion on CRM, the question that I come across is – ‘we are thinking of implementing RCSI and it should increase performance. What’s your thoughts?’

Before we implement anything, we should also learn the caveats of it and personally I know many customers who have suffered (transactions rolled back and other stuffs). While this makes an attractive proposition for implementing, let’s see what can be the downside to it.

• RCSI leverages the tempdb database to store a copy of the original row and adds a transaction sequence number to the  row. So it is important that the physical environment is configured to cope with this, primarily in terms of tempdb performance and memory/disk space requirements.
• Many of CRM customers have complained that updates are rolled back frequently after enabling RCSI. This is because snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised.
• When snapshot is enabled, there are no shared locks. Any statement running with snapshot isolation cannot see any committed database modifications that occur after the statement starts executing. The longer the statement runs for, the more out-of-date its view of the database becomes, and the greater the scope for possibly-unintended consequences.

For e.g – You have a long running query which determines whether an email should be sent at certain step of the query, based on some attribute value of an entity. Since this is running in snapshot, the transaction at the beginning has taken a copy of the data for the transaction in the tempdb database. So when the email sending condition is evaluated, the value of the determining field might have changed in the database. But since the query is working on the snapshot of data some time back (might be few seconds), it would still evaluate the e-mail sending condition to true.

This is off-course an example. However I hope you guess what analogy I am trying to draw here.

So before you implement, just think what is the concurrency level for your CRM usage and then discuss with your DBA before reaching a decision.

Finally if you go ahead and enable the RCSI, run the below commands.

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE <Your Crm Database>
SET READ_COMMITTED_SNAPSHOT ON

Hope this helps!