As a SQL Server expert, I often encounter various issues related to SQL Server engine while assisting clients with performance tuning. Recently, one of my clients using the AlwaysOn Availability Group feature reached out to me for help in optimizing their system’s performance. They were experiencing latency associated with the redo process on the secondary replica.

Upon investigating the issue, I discovered that the high waits were due to the “PARALLEL_REDO_WORKER_WAIT_WORK” wait type. Additionally, there were a few other predominant wait types such as “DIRTY_PAGE_TABLE_LOCK”, “REDO_THREAD_PENDING_WORK”, and “PARALLEL_REDO_DRAIN_WORKER”. These waits are typically observed when there is parallel redo activity happening, which can occur during database recovery or when the database is taken offline/online.

Further research revealed that this is a new feature introduced in SQL Server 2016. The secondary replica, in this case, was primarily used for reading purposes and was undergoing load testing. The issue occurred when they rebuilt an index online on the primary replica and performed a select operation on the secondary replica, resulting in a complete table scan and the observed waits.

To address this issue, I recommended a workaround/solution to my client. By using trace flag 3459, they could disable parallel redo and revert to the older functionality of redo, as it used to happen in previous versions of SQL Server. This trace flag can be enabled through the SQL Server Configuration Manager.

It is not uncommon to encounter issues with new features introduced by Microsoft in SQL Server. As a database professional, it is essential to stay updated with the latest features and their potential impact on performance. If you have faced similar issues or have any insights to share, please leave a comment below and let others benefit from your experience.