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.
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.
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 STARTUPcommand 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
- 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.