Jun 21 2021 08:59 PM
We are using SQL Server 2019 Database for our microfinance solutions.
OS: Windows Server 2016
RAM: 1TB
CPU: 4 Socket 56 Core, 112 Logical processor
We have 3 read databases that are connected with AG. Read DB Configuration is lower than primary DB.
We have a high volume of transactions like over 30million transactions in a single hour.
The Problem is
We are facing slow redo performance in secondary read DB.
Jun 23 2021 03:55 AM
Hi @sajidur07 --
Secondary replicas that are considerably smaller (you didn't include secondary replicas compute specs) in terms of compute power are most always going to have an issue with redo performance with that level of transactions. You mentioned the secondary replicas as read-only replicas which implies that there is also a read/reporting workload consuming compute resources (which in turn adds to the redo queue issue).
Without right-sizing your secondary replicas, this will be difficult to fix. My suggestions is to look at the wait statistics on the secondary replicas to understand which resources are most constrained (RAM, CPU, etc.) and start there with adding resources. Take care.
Jun 27 2021 03:02 AM
Hi @bake13
We checked wait statistics and resources.
Resources use is normal. It consumes only 20-30% CPU and RAM using below 75%.
It's working fine 20-25min. When AG stops syncing After that there are some waits is coming.
And also we notice that when DB Startup wait type raise then database got stuck.