none
Redo queue building up constantly for up to one hour RRS feed

  • Question

  • We use SQL Server version 14.0.3103.1 with Always on Availability group.

    In the production server we noticed Redo queue building up constantly for up to one hour. This occurs randomly and during this time the redo comes to a standstill. We tried to reproduce the scenario in staging with tables and scripts independent of the customer data and we now have a scenario that looks similar.

    In this scenario we notice PARALLEL REDO TASK waiting on DPT_ENTRY_LOCK and redo queue size increasing constantly. With the production issue we noticed queries were waiting on DPT_ENTRY_LOCK wait type

    We reproduced with 12 connections, 6 connections updating tables in the primary and 6 connections selecting from readable secondary (both sync and async).

    Below is our monitoring query and the output.

    selecttop10 *fromsys.dm_os_spinlock_stats

          wherename='DP_LIST'

    go

    select*fromsys.dm_exec_requestswherewait_type ='DPT_ENTRY_LOCK'

    go

    selectwait_time_ms/waiting_tasks_count,*fromsys.dm_os_wait_stats

          wherewait_type like'%DPT_ENTRY_LOCK%'

                andwaiting_tasks_count >0

          orderbywait_time_ms desc

    go

    selectdb_name(database_id),redo_queue_size,redo_rate,*fromsys.dm_hadr_database_replica_states

    go

    When we disable parallel redo with trace flag 3459, we don’t have the problem, the redo queue does not buildup.

    This is causing problems because the system becomes unusable during this time, especially reading from secondary.


    Wednesday, May 29, 2019 5:47 AM

All replies

  • Hi AlexCriss,

     

    >>In this scenario we notice PARALLEL REDO TASK waiting on DPT_ENTRY_LOCK and redo queue size increasing constantly. With the production issue we noticed queries were waiting on DPT_ENTRY_LOCK wait type

     

    This wait type is when a thread is waiting for access to the list of dirty pages during parallel redo or a read query on an Availability Group readable secondary replica. When you have a large number of transaction on your primary that have to be flushed to the secondary, and you have a high number of reads on a secondary that access the same tables, you end up with this lock.

     

    If you are experiencing this wait type as one of the highest waits on your AG secondaries, you can disable parallel redo by turning on trace flag 3459 using DBCC TRACEON (3459, -1), or adding it as a startup trace flag using the Configuration Manager. The trace flag does not come into effect until the instance is restarted.

     

    For more details, please refer to https://www.sqlskills.com/help/waits/dpt_entry_lock/

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 30, 2019 6:42 AM