As most of you might know that my expert area in SQL Server in performance tuning. But, I also deal with almost every issue related to SQL Server engine. Many times, it so happens then clients call me for performance tuning and while fixing, we see some other problem. In this blog, we would learn about Parallel Redo on AlwaysOn Secondary causing new waits which were introduced in SQL Server 2016.
As mentioned earlier, one of my clients is using AlwaysOn Availability Group feature and contacted me for assistance in performance tuning. They were seeing latency associated with the redo on the secondary. When I logged in to secondary, I could clearly see high waits due to “PARALLEL_REDO_WORKER_WAIT_WORK”. There were few other waits on the top of the list. Another predominant wait types which we saw were:
- DIRTY_PAGE_TABLE_LOCK (this was the top wait)
- REDO_THREAD_PENDING_WORK
- PARALLEL_REDO_DRAIN_WORKER
- PARALLEL_REDO_FLOW_CONTROL
Based on my search, it all appears when there is parallel redo activity happening. This can also happen during recovery of the database (restart of SQL Service or if we take the database offline/online). Based on documentation this is a new feature introduced in SQL Server 2016. The secondary was used for reading purpose and they were doing load testing on SQL Server.
WORKAROUND/SOLUTION
When I asked them to show me the testing environment and steps they take to simulate the issue, they said that there is no much infra required. They just rebuild the index online on primary replica and perform select on secondary replica so that it scans complete table. While doing that they see waits. After a lot of search on the internet, I was able to find the way to disabled parallel redo. You can also do it in SQL Server by using trace flag 3459. SQL SERVER – What is Trace Flag – An Introduction
Using configuration manager, you can enable trace flag and use the older functionality of redo the way it used to happen for ages.
Have you ever faced issue with new features in SQL Server released by Microsoft? I am sure there are many! Please comment and let others know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
is this a bug? the new ways (parallel redo) should be more efficient .
My SQL instance (SQL 2016 SP2) faced the same situation, and I had to apply this flag. Thanks for your article, Pinal!
I had this issue on my cluster SQL 2017 CU10 after a reboot of the secondary node.
does this trace flag needs to be implemented both on primary and secondary
Hi,
In our Environment our PROD 2 DB Server consuming total memory without any operations performed on that server.
It is taking total memory 8192 MB which we are allocated to server and CPU Utilization it is showing only 1%.
We are using SQL server 2016 SP1 and we configured Always ON.
And from last few days It is showing PARALLEL REDO TA & PARALLEL REDO HE Processes are running and those are consuming more memory
Please give me the solution.
Thanks,
Madhu.
Hi Pinal,
Thank you for this post.
I have an issue where my Database is 52TB and after SQL or Windows patching, due to it’s size, it’s last to come online and is not one of the first 6 DBs to be able to utilize the Paralled Redo.
Is there anything I can do in this case to make sure the largest DB gets to use the Paralled Redo ?
Thanks again.