Dynamics 365 Business Central: SQL Server and Read Committed Snapshot Isolation impact.

One of the greatest thing introduced in the latest Dynamics 365 Business Central release if cerainly the TRI STATE LOCKING feature and the new READ COMMITTED transaction isolation level.

I wrote in the past about this new transaction isolation level and how it helps a lot on reducing locks. You can find my old post here explaining what happens on SQL level. And today my friend Duilio Tacconi has written a great post explaining with a demo the impact on enabling the Tri-state locking feature on Dynamics 365 Business Central SaaS and on-premises.

But as Duilio explained, on SaaS you can just turn on the feature via Feature Management page, while on-premises you need to do something more: enabling Read Committed Snapshot Isolation on SQL Server.

I had on my drafts a yet ready small post for sharing what I think are some important informations to remember for on-premises environments (yes, I0m still talking about Dynamics 365 Business Central on-premises) in order to efficiently use the new locking feature and probably now it’s the right time to publish it.

Read Committed Snapshot Isolation (RCSI) is a feature that must be turned on on SQL Server simply by using the following command:

ALTER DATABASE YOURDATABASE SET READ_COMMITTED_SNAPSHOT ON
GO

What this setting will do?

In simple words, instead of locking a record during the reading operation with a Shared Lock, SQL Server will transparently return you the old committed record version from the Version Store (the internal SQL store used for row versioning). When a modification is made to a row, a copy of the original row is stored in the version store. This allows other transactions to read the original version of the row while the modification is being made

The Version Store is stored in the TempDb system database.

When using Read Committed Snapshot Isolation your current record version points with 14 additional bytes to an older record version which is stored in the TempDb.

What happens under the hood with RCSI enabled?

When RCSI is enabled on an existing database (like your migrated Dynamics 365 Business Central on-premises SQL database) and an operation updates a record, SQL stored the old record version in the TempDb and add 14 bytes to the current record on the data page pointing to the previous record version. If the record was N bytes in size, now it’s N+14.

Problems? No…

But now imagine that the data page in your Heap Table or in your Clustered Index has no free space available anymore for this 14 additional bytes. In that case a forwarding record process is introduced. Forwarded Records are the data row in a heap which has moved from the original page to the new page. When SQL Server scans the heap to read the necessary data, they often encounter forwarding record pointers which usually exist on the different page leading SQL Server to read more pages than required while dealing with the heap. And this can cause performance issues on the heap table because additional I/O is involved (disk latency). Read/Writes will eventually increase in Tempdb when snapshot isolationlevel is turned on.

Here is a query that can be useful to check read/writes operations by database. Irrespective of any isolation level, you can identify total I/O for each database:

SELECT name AS 'Database Name'
      ,SUM(num_of_reads) AS 'Number of Read'
      ,SUM(num_of_writes) AS 'Number of Writes' 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D  
      ON I.database_id = d.database_id
GROUP BY name ORDER BY 'Number of Read' DESC;

And here is a query for calculating Disk Latency for your different database drives:

SELECT  LEFT(physical_name, 1) AS drive,
        CAST(SUM(io_stall_read_ms) / 
            (1.0 + SUM(num_of_reads)) AS NUMERIC(10,1)) 
                          AS 'avg_read_disk_latency_ms',
        CAST(SUM(io_stall_write_ms) / 
            (1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1)) 
                          AS 'avg_write_disk_latency_ms',
        CAST((SUM(io_stall)) / 
            (1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1)) 
                          AS 'avg_disk_latency_ms'
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
        JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
                                       AND mf.file_id = divfs.file_id
GROUP BY LEFT(physical_name, 1)
ORDER BY avg_disk_latency_ms DESC;

What is recommended to do?

To avoid possible performance degratation expecially on I/O operations when enabling the RCSI feature on SQL Server in a Dynamics 365 Business Central on-premises installation, I suggest to do the following:

• If you’re starting with a fresh Dynamics 365 Business Central on-premises installation (fresh SQL database), enable Read Committed Snapshot Isolation immediately when creating the database. In this case SQL Server will add the 14 additional bytes immediately to your records and you can avoid the additional I/O operations.
• If you’re enabling the RCSI feature in an existing Dynamics 365 Business Central on-premises database, rebuild your indexes with a lower Fill Factor, or rebuild your Heap Tables as soon as you have Read Committed Snapshot Isolation (RCSI) enabled:

To rebuild your heap table, you can do the following (it also updates the non-clustered index on the heap table):

ALTER TABLE YOURTABLE REBUILD;

To rebuild an index with a fill factor (in this example 90), you can do the following:

ALTER INDEX YOURINDEXNAME ON YOURTABLE  
REBUILD WITH (FILLFACTOR = 90);   
GO  

Should I enable RCSI on my Dynamics 365 Business Central on-premises installation?

My answer is: YES!

Read Committed Snapshot Isolation solves a lot of blocking situations between reads and writes and also helps a lot on reducing deadlocks. Benefits are a lot for your Dynamics 365 Business Central environment.

If you’re on-premises there’s only an extra activity related to database management that you need to have in place if you want to have an optimized SQL database.

• Check yout TempDb performances and growth (TempDb could fill so check sys.dm_tran_version_store_space_usage) and in case of problems act accordingly.
• TempDb should be big enough and with autogrowth setted to a fixed value (no % please)
• Place TempDB on different disks than dtaa files (this is a general best practice but in this scenario it’s very important).
• Often rebuild indexes.