HA: Reporting workload blocks redo threads due to IO contention

  • Maxer

    SSCrazy Eights

    Points: 8960

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-primary-changes-not-reflected-on-secondary?view=sql-server-2017#BKMK_REDOBLOCK

     

    We have HA setup and we are finding that the redo thread falls behind.

     

    This tends to happen when there are large reporting workloads run against our secondary synchronous node.

     

    Are there any options besides just "run fewer queries against the other node"?

     

    If it was blocking we could address a specific query, but there is no blocking.  We just notice that data transfer ramps up and sometimes replication falls behind during this heavy usage.

     

    I don't really like MSFT's suggestions of just throttling queries, then I have SQL server I am paying for sitting idle.

    Is there nothing that can be done to allow the redo thread to keep doing its thing?  Make it a higher priority I suppose? *Set the governor to only limit queries IF the redo thread if wanting more IO?

     

  • DinoRS

    SSCrazy

    Points: 2679

    Yes sure, another option is get a lower latency replication network and a better performing disk subsystem. No need to leave your SQL Server idling around, get appropriate sized HW and unless you hog your CPU to 100% (which in turn means: more resources or more powerful CPUs for the same core count) AAG Redo should be faster.

    • This reply was modified 1 year, 5 months ago by  DinoRS. Reason: spelling

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply