SQL SERVER – Parallel Redo on AlwaysOn Secondary – DIRTY_PAGE_TABLE_LOCK

SQL SERVER - Parallel Redo on AlwaysOn Secondary - DIRTY_PAGE_TABLE_LOCK ParallelLinesOpticalIllusion 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:

  1. DIRTY_PAGE_TABLE_LOCK (this was the top wait)
  2. REDO_THREAD_PENDING_WORK
  3. PARALLEL_REDO_DRAIN_WORKER
  4. 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)

, , , ,
Previous Post
SQL SERVER – Impact of Transaction on the Free Log Space
Next Post
SQL SERVER – How to Know Transaction Isolation Level for Each Session?

Related Posts

5 Comments. Leave new

  • is this a bug? the new ways (parallel redo) should be more efficient .

    Reply
  • My SQL instance (SQL 2016 SP2) faced the same situation, and I had to apply this flag. Thanks for your article, Pinal!

    Reply
  • I had this issue on my cluster SQL 2017 CU10 after a reboot of the secondary node.

    Reply
  • Deepak Almeida
    March 20, 2019 4:12 pm

    does this trace flag needs to be implemented both on primary and secondary

    Reply
  • 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.

    Reply

Leave a Reply

Menu