Query Store Best Practices

I’m a huge fan of Query Store, which regular readers may know, but there’s a need to write a bit more about Query Store best practices.  This isn’t a “you must use this feature” post, this is a “here is what you must know if you want to use this feature” post.

I have a lot of content about Query Store, but maybe what’s really important gets lost amongst everything else.  Glenn prompted me to write this, after I worked with two customers last week that ran into issues related to their configuration of Query Store.  Listed below are the things you must know before you enable Query Store.  If you already have Query Store enabled and running, I recommend reviewing the information to ensure you are following best practices.


  • Review my post on Query Store Settings to understand what different settings exist for Query Store, what values are recommended for each setting, and why.
    • Most important: QUERY_CAPTURE_MODE set to AUTO, MAX_STORAGE_SIZE_MB set to 10GB at the absolute max, something less ideally (you may need to adjust CLEANUP_POLICY to keep less data, depending on your workload).
    • Edit: November 17, 2020: If you are using SQL Server 2019 and have an ad hoc workload, I recommend CUSTOM for CAPTURE_MODE.

Trace Flags

  • Review my post on Query Store Trace Flags to understand the two Trace Flags which are Query Store-related, what they do, and why you want to enable them.
    • I definitely recommend both 7752 and 7745.  If you have 7752 enabled and you are trying to make changes to Query Store configuration after a restart, please review my post discussing the Query Store data load.  There is no shortcut to get this to load faster, and no way to kill it.  This is why proper settings are important.
    • Edit: November 17, 2020: Trace flag 7752 is enabled by default in SQL Server 2019.


  • Review my post on Query Store Performance Overhead (updated November 2020). If you have a high-volume, ad hoc workload, it is quite possible that you should not enable Query Store.  Jonathan told me about a customer of his that enabled Query Store and within two (2) hours, they had filled up 10GB of Query Store.  We recommended that they not use Query Store at this time.  There are improvements coming in SQL Server 2019 that will hopefully help manage Query Store with this type of workload.


  • I highly recommend that you run SQL Server 2016 SP2 CU7 or SQL Server 2017 CU15 (current CUs at the time of writing, I’ll try to keep this updated). There is a very important fix in CU7 that I strongly suggest you have in place.  I don’t see this fix in a CU for SQL Server 2017 yet, so it’s possible you could run into a problem if you have an ad hoc workload.
    • Edit: November 17, 2020: The current CUs for each release at the time of this writing are strongly recommended (please see Query Store Performance Overhead…Updated).  The CUs are:
      • SQL Server 2019 CU8
      • SQL Server 2017 CU22
      • SQL Server 2016 SP2 CU15

Ad hoc?

If you still have questions about how to configure Query Store or what to watch out for, please leave a comment!  I’m happy to share as much information and experience as possible.

7 thoughts on “Query Store Best Practices

  1. Hi Erin –

    Thanks for taking the time to write this. We’re thinking about starting with Query Store in our dev / test environments, which we just upgraded to 2019. Any concerns with the 2019 releases?


  2. Hi Erin, great stuff about the QS. Your presentations are always very informative. I wanted to ask a question during today’s PASS presentation but forgot to ask. We have an 8 replica AG cluster with some databases having 5-6 secondaries… Is QS data being synchronized to every replica and how much I/O does it consume?

    1. Hi Michael-

      Correct – the QS data is synchronized to every replica. In terms of I/O, I don’t know if you mean disk space in the user database (that’s something you can pull from sys.database_query_store_options), or something else.


Leave a Reply

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

Other articles

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.