We are facing issues with REDO queue fluctuating as we have readable secondary configuration.
Based on my understanding in newer versions of SQL Server, redo threads have been made parallel. Current version is SQL Server 2017 running on VM with 64 cores.
I am seeing problems when select queries on the secondary run on the same cpu/scheduler where AG threads are running. I am assuming AG thread yields its scheduler and takes a back seat to let other queries run. And I believe this is the problem because queries coming in seem to be hitting same cpu_id where AG threads are doing redo work.
Example below: spid 91 is select query on cpu_id 6 and at same time spid 123 and 144 running threads for AG's as found from sys.dm_exec_requests dmv. REDO queue starts building up when 91 came up.
I am not aware if there is any process which makes your queries go to different cores rather than one where AGs thread might be running.
Can we control this to make queries hit other schedulers and not one where AG working?
I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag help as mentioned here
There might be some many other schedulers available. I am not able to understand why certain process is stuck in slot of Numa. I guess newer versions have all soft numa enabled so that makes bucket of 8 soft numas. 8*8 schedulers for 64 available. Is disabling soft numa a good idea here?
Database isolation is RCSI but I don't think that matters here because internally it's changed to snapshot based on design of readable secondaries.