1

I’m running into an issue. My readOnly replica (on Node B) takes twice as long to execute a query as on my primary (Node A). But when I fail over to Node B (which becomes my primary) it runs fine, but then it runs slow on the new replica so I don't think its the hardware.

I checked the index fragmentation and they are identical and there is currently nothing running on the servers except my query. Execution Plans are identical. The power is set to High Performance.

We are running SQL 2017 on windows 2016 server.

Any suggestions on what else to check?

enter image description here

Improve this question
15
  • I'm thinking it's a network delay between primary (node A) to the replica (node B)? If your node B is the primary, do you see the same delay on node A or does the issue no longer persist? I think you said that, but it's a little ambiguous.
    – Shaulinator
    Apr 9, 2018 at 18:52
  • when I failover, the same delay is experienced on the new replica that didn't have the issue while it was the primary and the now new primary runs fine.
    – Tomasz
    Apr 9, 2018 at 19:06
  • Is it a synchronous or asynchronous availability group?
    – Shaulinator
    Apr 9, 2018 at 19:10
  • synchronous. In the same data center, on the same network.
    – Tomasz
    Apr 9, 2018 at 19:14
  • i should also mention that the hardware is identical.
    – Tomasz
    Apr 9, 2018 at 19:19

1 Answer 1

Reset to default
1

To fix our problem we had to enable Trace Flag 3459.

We are currently having this issue on SQL Server 2017 CU6 (14.0.3025.34). Not much documentation on this Flag, but from what I gather, enabling this flag will slow down our failover process. I'm going to test some more, and update if i run into anything show stoppers.

Improve this answer
2
  • trace flags: docs.microsoft.com/en-us/sql/t-sql/database-console-commands/…
    – Tomasz
    Apr 20, 2018 at 19:14
  • Reply from Microsoft: the performance regression seen on the secondary is currently by-design caused by the default parallel-redo behavior and the proposed suggestion is to disable parallel redo by enabling Traceflag 3459. Having said that, we are looking into enhancing this design by eliminating or minimizing the need to check for page redo’s, especially no data modifications are pending in the redo queue. There is no ETA on when this change will be complete and will be shipped in the regular cumulative updates but as soon as I find out more, I shall let you know.
    – Tomasz
    May 17, 2018 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.