Query Store and Availability Groups

Last week at the PASS Summit I presented a pre-con and general session on Query Store. I had several questions related to Query Store and Availability Groups, so I thought I’d pull them all together and answer them here.

 

Q: What happens to the Query Store data when a database is in an Availability Group?

A: You can enable Query Store for any user database, including one in an AG, and since that data is stored in internal tables in the database, it exists in the replica copies as well (just like other system tables and user tables).

 

Q: Can you enable Query Store for a read-only replica?

A: No.  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.  I did create a Connect UserVoice item for this request.  If this is of interest to you, please up-vote it: Enable Query Store for collection on a read-only replica in an Availability Group.  The more votes this has, the better the possibility that Microsoft will implement it, so feel free to share with your friends and have them vote too!

*Update March 8, 2018: Connect has ended and all requests were ported to UserVoice…and your votes are still needed.  Please vote when you have a chance!

 

Q: Can you force a plan for a query on a read-only replica if you force it first for that query on the primary?

A: No.  With a read-only database this is not possible.

 

Q: Can you create a separate user database on the secondary, enable Query Store for it, and then issue queries against the read-only replica through that user database and have them captured in Query Store?

A: No.  (But it’s a really creative idea.)

 

Have other questions related to Query Store and AGs?  Let me know!  Happy to answer them.

38 thoughts on “Query Store and Availability Groups

  1. So if I have a database in an AG and I enable Query Store, then fail it over to the secondary replica, will I be able to look at the reports and everything like I was in the primary? thanks

  2. After failing over, do you find that forced plans continue to be enforced? So for example, on your primary replica you have a query with a forced plan. You failover to the secondary replica. Does the query plan continue to be enforced?

    In my experience, all of my forced plans stop working upon failover, & I need to go through the process of identifying the queries & forcing the plans. Which sucks.

    1. John, if you fail over, yes, the forced plan should still be forced on the new primary. In the same manner, if you backup your database and restore it to another environment, plans should be forced in the new environment as well. If you’re not seeing that, then I recommend you open a case with Microsoft, as that’s not expected. Do you see any forcing failures in sys.query_store_plan after a failover from a primary?

      Erin

  3. Hi Erin, I am finding that I cannot amend any Query Store settings, even to turn Query Store off, because the database is part of an Availability Group. We’re on 2016 SP1, CU8. I am sure we’ve not faced this in the past, so I am wondering if it’s the latest CU that has imposed some sort of restriction.

    Does this behaviour seem right to you?

    USE [master]
    GO
    ALTER DATABASE [our_db] SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO)
    GO

    Msg 1468, Level 16, State 3, Line 3
    The operation cannot be performed on database “our_db” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
    Msg 5069, Level 16, State 1, Line 3
    ALTER DATABASE statement failed.

    Many thanks

    1. Hi Jimmy-

      That behavior does not seem correct. I tested in SQL Server 2016 SP1 CU6 with a database that was in an AG and didn’t have any issues. I also tested in 2017 and had no issues. I don’t have an environment that’s 2016 SP1 CU8 in which I can test…do you have another environments in which you can test? I haven’t looked through all the release notes for the CUs to see if that was an issue that was fixed. The only other thing I could try is asking on Twitter to see if anyone else has run into that behavior…

      Erin

    2. Hi Jimmy-

      I was fortunate enough to have someone help out on Twitter who tested in the same version (SQL 2016 SP1 CU8) and did not have the same issue. I also confirmed with the engineers from Microsoft that you can change the Query Store settings for a database in an AG. So…is there any chance you’re trying to run this on the secondary instead of the primary?

      Erin

      1. Hi Erin, thanks very much for looking into this. Yes, apologies, it turns out that I was indeed running this statement on the Secondary. I had been trying out some read only routing options in SSMS and had inadvertently left these in place, and thus was (unknowingly) routed to a Secondary of the AG. Sorry to post such a ridiculous question!

        In a desperate effort to salvage some pride and make a positive contribution :), let me say this in case it is of interest regarding Query Store behaviour generally (I am venturing away from AG here) … when I ran the following statement (on the correct node!), our plan cache was emptied out:

        — Please note our Query Capture Mode had been ALL and we were in a desired state of READ_ONLY
        ALTER DATABASE [our_db] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 21), QUERY_CAPTURE_MODE = AUTO)

        I was under the impression that Query Store’s “side effects” on the Plan Cache were fixed in earlier CUs. Does this behaviour surprise you, that the Plan Cache was emptied out? This seems to happen consistently with our ALTER DATABASE SET QUERY STORE changes.

        I’m going off topic (QS and AG) so please let me know if i should post this somewhere else. Anyway, thanks again.

        1. Hi Jimmy-

          I’m glad to hear you have this resolved!

          With regard to the plan cache being cleared, can you confirm how you’re seeing that? (message in the ERRROLOG or something else) This is not expected.

          Thanks,

          Erin

  4. Hi Erin, it does indeed, thanks! (And just to confirm, per your article, I didn’t see this in the Error Log; it was just an observation of the plan cache itself). Thanks again.

  5. Hi ,

    I have enable query store in Alwayon group in one DB ( sql server 2016 latest cu)
    but after enable my ldf file size continuously growing .
    I try to but take more than 10 minutes and stopped ( MY DB size 40 GB )
    ALTER DATABASE [TestDB] SET QUERY_STORE = OFF

    Can you suggest me ?

  6. Hi Erin,

    Is it possible to utilize PlanGuides instead of QueryStore to control queries on ReadOnly replica?
    It does not work for me, but I would like to get a second opinion just to make sure I understand it correctly.

    1. Aleksey-

      It is not possible to use a plan guide on a read-only replica because it’s a READ-ONLY copy of the database. It does not allow modifications in any way, and creating a plan guide requires updating system tables in the database.

      Erin

      1. Hi Erin,

        Thank you for the input but I guess you have not completely understood what I am after.
        All required PlanGuides have been created on Primary replica and I believe got populated to the ReadOnly one (as this is a binary copy of the entire DB).
        So, the idea is why the existing Guides cannot be utilized on ReadOnly replica by the Engine?
        My reports running against my Primary utilizing PlanGuides, but they do not perform well against ReadOnly Replica as Guides are not in use and I cannot influence it in any way.

        Thank you again.

        1. Ah, I see what you’re asking. The behavior you are seeing is correct, and it’s also true for forced plans. Even if you have a plan forced on a primary, or if you have plan guide on a primary, it is not used on a secondary. It is because it is a read-only replica and while it would seem that using a forced plan or a plan guide is a “read-only” operation, there’s more to it than that.

          1. Please It will be helpful if you elaborate more on

            “plan guide is a “read-only” operation, there’s more to it than that.”

          2. If you create a plan guide on a primary, or force a plan on a primary, the plan guide or forced plan will not be used on the secondary. This is expected behavior at this time, because the secondaries are READ-ONLY.

            With regard to “plan guide is a “read-only” operation, there’s more to it than that”

            What I was trying to say is that some people will think, well if there is already a plan guide there, wouldn’t the engine just have to “read” that information from the READ-ONLY database, and then use that plan guide? No. That is not how it works.

          3. Plan guides don’t work on read-only replicas on “SQL Server 2016 (SP2) (KB4052908)”
            But they do work on “SQL Server 2016 (SP2-CU12) (KB4536648)”

  7. Does the duration as logged by QueryStore include the time for log hardening on the readable secondary? We are using SYNCHRONOUS COMMIT mode. the reason I ask is that I’m seeing a pretty large discrepancy between what query store reports vs SSIS task logging which executes the sproc.

    1. Hi Xian-

      Thanks for your question and I apologize for the delay in reply – this comment got missed somehow.

      The duration logged by Query Store does NOT include the time for log hardening on the readable secondary. The elapsed time for a query is simply the time between when query execution starts and ends on the PRIMARY. Anything that happens after execution ends on the primary is not considered.

      Let me know if that doesn’t address what you’re seeing,

      Erin

  8. Hi Erin,
    I don’t see QueryStore section on secondary replica in SSMS. Views work just fine, but no reports available.
    In other hand, I have same database copied using log-shipping in stand-by mode and there I can see Query Store (READ_ONLY) section.
    Do you experience same behavior?

    1. Sergei-

      I’m not clear on the exact configuration, as you say secondary replica and then you mention log shipping. Can you please clarify?

      If you’re talking about a read-only replica that’s part of an Availability Group, then in Management Studio you cannot access the database via Object Explorer because it’s read-only. If you connect via Query Window with ApplicationIntent = ReadOnly, then you can query the views (and in some cases that may be preferable to running queries on the primary).

      Erin

      1. Hi Erin,
        We have both AlwaysOn and Log Shipping configured for same database. AlwaysOn in local site and log-shipped DB in remote site.
        The goal was to review/query QueryStore data in real-time without impacting primary database at all.
        It seems that this is by-design behavior.
        Thank you!

        1. Hi Erin,
          It seems that Microsoft has fixed that behavior for AlwaysOn read-only replicas in SSMS.
          With SSMS 17.9 I can see QueryStore reports but in database properties I still see QuereStore as disabled. With 18.5 it is the same as with log shipped database: QueryStore says Read_only, it is enabled in properties and shows all reports.
          As expected, read-only replica shows QueryStore data from Primary DB.

  9. Hi Erin,
    We have both AlwaysOn and Log Shipping configured for same database. AlwaysOn in local site and log-shipped DB in remote site.
    The goal was to review/query QueryStore data in real-time without impacting primary database at all.
    It seems that this is by-design behavior.
    Thank you!

  10. Since we are forcing the query to a plan on the primary, how do we get the same plan on a secondary? It would seem important to get the same performance from the query on all read replicas.

    1. Tim-

      You currently have no ability to force a plan on the secondary. If you want to get a desired plan into cache on the secondary, and you were able to get the plan you wanted on the primary, I would first try using the same input parameters that you used on the primary when executing it on the secondary to see if that generates the same plan.

      Erin

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.