Availability Group Replica delay
Considering the presence of high HADR_DATABASE_FLOW_CONTROL waits on the primary, and the combination of PARALLEL_REDO_FLOW_CONTROL and PARALLEL_REDO_TRAN_TURN waits on the secondary, it sounds like parallel redo might not be a good fit for your workload.


  Can anyone think of any ddl,dml operations or other task that could cause this type of behavior?


Microsoft calls out one example in their blog post on parallel redo:


  PARALLEL_REDO_TRAN_TURN - Only happens in a readable secondary replica when new insert triggers page-split system transaction, or record update in a heap table generates a forwarded record


If your workload results in lots of page splits or forwarded records, then you should consider disabling parallel redo on the secondary by turning on documented trace flag 3459 (note: as a permanent solution, you will want to set this as a startup trace flag in SQL Server Configuration Manager):

DBCC TRACEON (3459,-1);


If you need to revert back to using parallel redo on your build, you will need to restart the SQL Server service after disabling the trace flag.  This was fixed (to not require a restart) in SP1 CU10 and SP2 CU2.  It doesn't look like it's fixed in the RTM branch.  See this KB for details: https://support.microsoft.com/en-us/help/4339858/fix-parallel-redo-does-not-work-after-you-disable-trace-flag-3459-in-a

More broadly, there are several fixes for issues with parallel redo in SP1 and SP2.  You should consider patching SQL Server to see if that resolves your issue as well.



If you want to confirm that page splits or forwarded fetches are actively occurring, try running sp_BlitzFirst:

EXEC dbo.sp_BlitzFirst;


If you want to confirm that your heaps have lots of forwarded records, you can use a DMV query like this one (note: replace Your_Table_Name with the name of a heap in your database):

SELECT 
    OBJECT_NAME(object_id) AS table_name, 
    forwarded_record_count,
    page_count
FROM sys.dm_db_index_physical_stats 
(
    DB_ID(), 
    OBJECT_ID(N'Your_Table_Name'), 
    DEFAULT, 
    DEFAULT, 
    'DETAILED'
);