question

r_balaji_g avatar image
r_balaji_g asked

How to priority for AlwaysON Redo

Hello We are running SQL Server 2012 SP1 and setup multiple DBs under same AG (async). RO Secondary server h/w capacity is lot less than the Primary. There is heavy write activity on one of DBs on the primary, which is not actively queried on the RO Secondary. RO Server's Log hardening & Redo activity for this DB is saturating most of the I/O bandwidth and to an extent CPU bandwidth on the RO side. Is there a way to set I/O and CPU utilization limits(using Resource Governor or some other means) for AG Log Hardening activity & Redo Activity? This will help not choke other user queries on RO server. Understood, this might lead to log growth on the Primary on the write heavy DB... Just want to strike a balance here...
sql 2012alwayson
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hang on. Isn't the primary goal of an AO-AG configuration to have a working secondary in the event of a disaster taking out the primary, and everything else is a bonus? And your secondary is already suffering from being unable to keep up with the write activity being generated by the primary? And your primary is having (or about to have) TLog problems due to the secondary not being able to keep up? And you're worrying about how to improve performance of the non-AOAG-related functionality at the expense of the performance of the key failover protection? Just checking I've got this straight...
2 Likes 2 ·
Agree with Thomas. If you are not wanting the DR aspect, why not split the AGs up to exclude the write heavy DBs or even use another method (such as log shipping, and exclude the write heavy databases). If you do want DR, you seriously need to consider matching the hardware.
2 Likes 2 ·
We have FCI cluster to worry about HA and SAN replication to worry for DR. The question here is just about AG. The purpose of AG in our architecture is to worry only about Offloading read workload to secondary (asynchronous AG). Now, my question is, is there way to throttle how much io, CPU, memory can AG consume on the secondary - at instance or AG database level? If so, how to achieve it?
0 Likes 0 ·
Dropping into this thread a little late... The redo process isn't tsql based and I don't believe you can use resource governor to restrict its activity, though i have seen an example of using resource governor to limit the reporting load so that the redo thread can better keep up - exactly the opposite of the problem you are trying to solve. You mention that you use FCI and SAN replication to take care of HA and DR. Since the AG is *only* for the read-only workload, and this busy database is *never* used for read-only queries, it seems like the best throttle for your situation would be to remove that database from the AG completely. Having the busy database in the AG is doing nothing to solve any business problem that has been discussed, and it's actively interfering with the business problem that you are using the AG to solve.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.