Does enabling readable secondary in the availability group put any load on the primary server's tempdb?



  • Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary.

    This will also automatically enable the snapshot isolation level on the secondary which will result in additional load on the secondary server's tempdb.

    Is there any chance this will put any load on the primary server's tempdb?



  • The load on TempDB in an availability group will be determined by the work load happening on a given replica, plus the isolation level of the replica. With a secondary replica having read enabled, it’s now possible for workloads to occur on that replica, and impact TempDB. Additionally, RCSI is automatically enabled on readable secondaries, resulting in extra overhead on TempDB as different versions of rows are stored in TempDB as queries are running.

    However, the primary will not take on this same isolation behavior, simply due to the secondary having reads enabled. Without RCSI being enabled on the primary, it will not see the additional overhead.

    Now, if you choose to enable RCSI on the primary, then it’s TempDB usage would be impacted in the same manner as the readable secondary replicas.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2