Forum Discussion

sajidur07's avatar
sajidur07
Copper Contributor
Jun 21, 2021

Slow secondary replica redo performance

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 Replies

  • bake13's avatar
    bake13
    Icon for Microsoft rankMicrosoft
    Jun 23, 2021

    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.

    • sajidur's avatar
      sajidur
      Copper Contributor
      Jun 27, 2021

      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.