I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?


Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

It depends on the sync mode you have used when configuring Availability group - Sync or Async !

On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's why it's important to test your workload when embracing AlwaysON.

From : Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

While mapping reporting workload to snapshot isolation eliminates blocking between DML workload as applied by the REDO thread on the secondary replica and the read or reporting workload, it does not eliminate the potential blocking of REDO thread when it is executing a DDL operation.

If using

  • Synchronous mode

    • Blocking issue on your secondary replica will impact performance of your queries on Primary replica. So a read workload (select) ran on secondary might blocked the redo thread from applying the changes that comes from primary replica. This means that the primary replica has to wait for changes to be applied to all secondary SYNC replica before it commits locally & might end-up in timeouts or blocking or deadlocking.

      The REDO thread can be seen on the Readable Secondary as the DB STARTUP command in sys.dm_exec_requests. If that thread is being blocked, then your read workload on the secondary could be causing an impact on the primary.

      For more details check - Scenario 1: Blocked REDO due to large query on the secondary replica

  • Asynchronous mode

    • The primary does not wait for acknowledgement from secondary. A blocking issue on secondary is just isolated to secondary wherein the redo queue will grow on the secondary until the locks are clear and the redo thread is able to apply the log blocks. This won't affect the primary replica.

Your definition of "real-time or almost real-time" needs more thought keeping in mind the sync method used, the network latency and how busy is primary replica and the log activity that needs to be transported secondary.

SQL Server 2016 has done some major enhancements in AlwaysON realm e.g.

  • 2
    A blocked REDO thread does not impact "performance of your queries on Primary replica". IO contention on the secondary can delay saving the log records on the secondary, which can impact commit times on the primary. But this has nothing to do with the REDO thread. The primary does not wait for REDO to apply the change; just for it to be written to the log file. See blogs.msdn.microsoft.com/sqlserverstorageengine/2011/12/22/… – David Browne - Microsoft Jul 11 '18 at 18:04

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.