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