0

In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?

I don’t think so, because query store data is stored in the read/write database, gets copied to all AG read-only replicas, would thus not represent/support any query activity being performed on a given read-only replica, and so could not support automatic tuning for any read-only replicas.

Is this correct? I’m hoping someone can disprove this, or provide some kind of work around. We’re getting serious query sniffing problems on some important read-only replica queries, and if automatic tuning will make this go away, management might buy into an upgrade.

(I was not able to find anything on this on the web, and hardly anything on automatic tuning.)

  • It doesn't work on SQL 2017/2019. You can try optiimize for unknown if you can modify the query text on the read-only replica as a possible workaround – Conor Cunningham MSFT Sep 17 at 1:58
1

In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?

No, it wont as documented in BOL(for azure but still applies to sql server on prem):

Attempting to enable Automatic tuning via T-SQL on the read-only secondary will result in a failure as having a different tuning configuration on the read-only secondary is unsupported.

Is this correct?

Yes, it is. Erin from SQLSkills has a nice FAQ that says : Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy.

if you are running into parameter sniffing issues, you can use query hints (with proper testing) as explained by Paul White in his blog post

| improve this answer | |

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.