Problem in AG readonly DB slow redo

Question

We are using SQL Server 2019 and we have 1 write DB and 3 read DB that are connected with AG.
SQL Server Running in windows server 2016 with 1 TB of RAM and 112 Core processor.
DB Size over 4TB and 40 million transaction occur in a single hour.

We are facing problem with Redo time. It's taking time to sync almost 15-20 min

Answer 1

Hi sajid,

Welcome to Microsoft Q&A.
This issue may because the REDO thread is waiting on one or more resources due to other activity on the secondary replica. These resources could be a lock (REDO blocking), disk I/O, RAM or even CPU time.
The redo thread on the secondary replica might be blocked from making data definition language (DDL) changes by a long-running read-only query. The redo thread must be unblocked before it can make further updates available for read workload. We can query the DMV sys.dm_exec_request on the secondary replica to find out which session is blocking the REDO thread, and then you can take corrective action.

select session_id, command, blocking_session_id, wait_time, wait_type, wait_resource     
from sys.dm_exec_requests where command = 'DB STARTUP'  

You can let the reporting workload finish, at which point the redo thread is unblocked, or you can unblock the redo thread immediately by executing the KILL (Transact-SQL) command on the blocking session ID.
Please refer to this doc and this article for troubleshooting.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.