Slow secondary replica redo performance

[sajidur07]

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.

[#2]

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.

[#3]

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.