SQL Server 2016/2017: Availability group secondary replica redo model and performance

I ran into the same issue and have SQL 2019 (40 core) on Win 2022. When I turned on TF 3459 on the secondaries it seemed that the parallel redo threads would not release on the small databases. It made me a little peeved and I thought it did not work due to TF 8002 on these servers to limit CPU or maybe CU version CU18. I have other servers in another site which are Win 2019 and SQL 2019 (32 core CU18 plus Security update) and it worked there but still some smaller databases seemed to hold onto the parallel threads. So I thought about it and the smaller databases were not always active and so did not send data across the AG so they were static for the most part. So I decided to try and create a dummy table in one of these databases and then drop the table, that would send traffic across the AG for that database. Once I did that when I had TF 3459 enabled that database dropped it's parallel threads and became single thread redo playback. So I was able to get all the small non-active databases to release their parallel threads by creating and dropping a dummy table in each database with the issue on the primary replica for that database I then disabled TF 3459 and when I checked the large active databases that I wanted to have parallel threads now had them.

So a partial solution to the issue in SQL 2019 (maybe 2016 and 2017 with updates mentioned in the article) you can turn on and off TF 3459 without a SQL Server service restart on SQL 2019 CU18. That means turn on TF 3459 and set to single thread for all databases. Then in on the primary for the affected databases that will not release their parallel threads create and delete a dummy table if there is no AG traffic otherwise it will not release the parallel threads from the database on secondary. If there is traffic on the AG for all databases then disable TF 3459 and you should see the most active databases pick up parallel redo threads.

Hope this helps people with SQL 2016, 2017 and 2019 with the 100 thread parallel redo issues. On SQL 2022 the issue is fixed with a different algorithm that takes load into account. On my servers it seemed that it databases with traffic picked up the parallel redo threads but not always the way I wanted if there were times when traffic was down the small databases again could pick up the parallel threads so there may be process needed to find the right time or generate data to make a database more active. 

Thanks Brian