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)
