Isolation Level (RCSI – Read Committed Snapshot Isolation)

Check if the database of Dynamics 365 CE organization is set READ_COMMITTED_SNAPSHOT to ON. When it is set to OFF, locking contention can adversely impact the performance of Microsoft SQL Server. READ COMMITTED ISOLATION can be used to minimize potential locking contention while protecting transactions from “dirty” reads of uncommitted data modifications. With dirty reads, data within the current transaction can potentially be modified by other transactions between individual statements, resulting in non-repeatable reads or phantom data.

Set by default during a standard installation of Microsoft SQL Server, READ COMMITTED isolation specifies that statements cannot read data that has been modified but not committed by other transactions. With READ_COMMITTED_SNAPSHOT set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

Considerations for documentation purposes

  1. Before considering implementing RCSI in your environment, it is recommended to complete query tuning exercises against the most expensive queries in the environment. This, along with proper SQL Server configuration settings and Dynamics 365 CE Settings, often will reduce or eliminate the need to modify the RCSI configuration.
  2. Verify that any customizations, integrations, or third-party applications are fully tested with RCSI to ensure the setting does not cause any inconsistent results.
  3. RCSI uses TempDB and enabling it could hinder the performance of your environment if your TempDB configuration is not optimal. For TempDB, ensure you have enough disk space, disk I/O, correct number of TempDB files and that the TempDB files are on a proper physical storage configuration (such as SSD's and separate from the CRM DB Files) will help reduce risk associated with TempDB performance.
  4. It is recommended to test the effects of this setting in a non-production environment before implementing in your production environment.

NOTE: It is important to consider possible adverse effects of enabling RCSI before implementing the change below. It is strongly recommended to work with Microsoft Support to determine if your environment would benefit from a change to this setting.

Making the change (only for documentation, it is already set to ON)

If you have consulted Microsoft Support, validated and considered the above points and fully tested the setting, the change can be made to your Dynamics CRM database by following these steps:

  1. Log on to SQL Server hosting CRM Databases using administrative credentials.
  2. Run the following T-SQL statement against CRM organization database(s):

ALTER DATABASE [CRMDatabaseName] SET READ_COMMITTED_SNAPSHOT ON

To validate that RCSI has been enabled for your CRM Organization database, execute the following query:

Select name 'DB Name', is_read_committed_snapshot_on 'RCSI' from sys.databases WITH(NOLOCK)

Find the CRM Organization Database that you enabled RCSI for, and there should be a 1 in the “RCSI” column of the query results.

NOTE: Exclusive database access is needed for the setting to take affect so it may be necessary to stop services that are connecting to the database (W3WP, Async, etc.)

To disable RCSI, run the following statement against the CRM Organization database(s):

ALTER DATABASE [CRMDatabaseName] SET READ_COMMITTED_SNAPSHOT OFF

To validate that RCSI has been disabled for your CRM Organization database, execute the following query:

Select name 'DB Name', is_read_committed_snapshot_on 'RCSI' from sys.databases WITH(NOLOCK)

Find the CRM Organization Database that you disabled RCSI for, and there should be a 0 in the “RCSI” column of the query results.

Walter Carlin - MBA, MCSE, MCSA, MCT, MCTS, MCPS, MBSS, MCITP, MS

Senior Customer Engineer - Dynamics 365 - Microsoft - Brazil