1

I need help for an always-on issue after upgrading to 2016 from 2014. Redo thread is causing blocking for all the select query on the secondary replica. all long running select queries are getting blocked by redo thread for a specific database. All DBs are already in sync. redo thread is getting blocked once I am running SQL job to fetch the data from secondary database.. redo queue size is also 0 and DB also synchronized before running the job. read routing URL also configured. Has anyone faced such a scenario? Please help with this.

I have tried multiple things to rid of deadlock issue and enabled the Is_Read_Commited_Snapshot option as well but still, that REDO thread ( DB sync) is blocked.

user181726
  • 13
  • 3

1 Answers1

2

As per my understanding, this issue could be due to the multi-threading model or parallelism.

Until SQL Server 2016, the transaction log redo was handled by single redo thread for each database, this model is called as serial redo.

Starting from SQL Server 2016, the default redo model is parallel redo. It provides multiple threads for transactions and additional worker threads for dirty page flush IO operations.

While working with the concurrent workload, DIRTY_PAGE_TABLE_LOCK waits are generated frequently by the worker thread, and at the same time, query threads will try to access the tables, this will cause the performance issues on both SELECT query and redo operations.

This issue was fixed on the latest Cummulative Update - https://support.microsoft.com/en-in/help/4135048/cumulative-update-1-for-sql-server-2016-sp2

As a workaround, you can disable the parallel redo model and enable the serial redo by enabling the trace flag 3459.

I hope this helps! Thank you.

Rathish Kumar B
  • 2,304
  • 5
  • 24
  • 36
  • Thanks for your response, i will try with this solution and let you know. – user181726 May 28 '19 at 14:33
  • Thanks for giving the work around, i have applied it and it resolved REDO thread blocking issue on secondary server. – user181726 May 29 '19 at 11:21
  • @user181726, Welcome, if it resolve the issue, kindly accept the solution and close it. Thanks. – Rathish Kumar B May 29 '19 at 11:25
  • Yes, This is resolved my issue. How to accept and close it. – user181726 May 30 '19 at 07:01
  • There is tick mark, left side of the answer, up sign for answer useful, down sign for answer not useful and tick mark for answer solved your problem. I hope it helps. – Rathish Kumar B May 30 '19 at 07:05
  • I have accepted the answer – user181726 May 30 '19 at 08:54
  • Hello Ratish, After getting enable the trace flag 3459 , my transaction log backup is large around 1 GB on primary replica and it is configured in every 15 minutes.Is this trace causing Tlog backup larger or some other thing is causing T log backup file size larger than usual. Please suggest – user181726 May 31 '19 at 14:17
  • If there is a huge number of transactions executed in 15 mins, there is a chance. To understand more about this, refer this page: https://serverfault.com/a/298290/355547, I am checking on enabling flag 3459. – Rathish Kumar B Jun 01 '19 at 09:10