Analysis of Locking Issues on Secondary Replicas in AlwaysOn Availability Groups
-
We have an AlwaysOn architecture with four replicas: two running in synchronous commit mode (cluster) and two in asynchronous commit mode. On the primary replica, we run SSIS workloads. The secondary replicas are used for reporting, with read-only operations routed through ApplicationIntent = ReadOnly. We also enabled AG load balancing to distribute the reporting workload across the secondary replicas.
However, we are observing that some reports take longer than expected. This leads to LCK_M_SCH_* locks on the secondary replicas, which slow down the AG synchronization process and can even cause the replication to be suspended.
We are considering two potential approaches to mitigate this issue:
Switching all replicas to asynchronous commit mode, to prevent the primary from being impacted by blocking on the secondary replicas.
Enabling READ_COMMITTED_SNAPSHOT (RCSI) to reduce read-related locking on the secondary replicas and minimize the impact of reporting queries on the redo thread.
Do you have any recommendations or experience with these approaches, or any additional suggestions to help reduce or avoid these locking issues in this scenario?
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply