Thursday, October 17, 2024

Enable RCSI on BC SQL to improve database locking issues (ADR and Optimized Locking)

Advice here

https://demiliani.com/2023/11/23/dynamics-365-business-central-sql-server-and-read-committed-snapshot-isolation-impact/


Check if RCSI is enabled

SELECT name,

       is_read_committed_snapshot_on

FROM sys.databases

WHERE name = DB_NAME();


 Enable RCSI 

ALTER DATABASE YOURDATABASE SET READ_COMMITTED_SNAPSHOT ON GO 

 

Do a full index rebuild on everything after enabling it 




--------------------------------------

Related

------------------------------------

Also consider enabling ADR, and Optimized locking if you are on SQL 2019+


https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking?view=azuresqldb-current&viewFallbackFrom=sql-server-ver16#is-optimized-locking-enabled