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.

Settings

  • 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).

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.

Performance

  • Review my post on Query Store Performance Overhead. 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.

Version

  • 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.

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.

Important Query Store Fixes – Summer 2019

In January I blogged about important Query Store fixes that were available in current CUs. Check my January post for fixes through SQL Server 2016 SP2 CU5 and SQL Server 2017 CU13.

I’m using this post to track Query Store fixes/enhancements since those releases, so you can find updates here as additional CUs are released throughout the summer.

SQL Server 2016 SP2 CU7

FIX: “Non-yielding Scheduler” occurs when you clean up in-memory runtime statistics of Query Store in SQL Server 2016

I definitely recommend that you update to this CU if you are using Query Store with SQL Server 2016 SP2, especially if you have an ad hoc or mixed workload.

Remember that runtime statistics and some query information is stored in-memory, and this CU addresses an issue with cleanup of the runtime statistics, based on the CLEANUP_POLICY and MAX_STORAGE_SIZE_MB.

I have seen issues where the space used for Query Store exceeds the MAX_STORAGE_SIZE_MB settings, and this CU should help address that behavior.  Again, it’s important to make sure your Query Store is sized appropriately, based on the type of workload you have (ad hoc will require more space) and how long you want to keep your data.  While it’s nice to keep 30 days of data, in some scenarios that doesn’t fit within the 10GB “ideal” size for Query Store.

I don’t see this fix in a 2017 CU.

Query Store in SQL Server 2019 (CTP 3.0)

Friends, CTP 3.0 dropped today, and it includes some changes for Query Store in SQL Server 2019!  I am so excited!!  I’ve downloaded it and have WideWorldImporters installed and have a lot of testing planned, but if you’re impatient, guess what?  The documentation is already updated!  If you check out the ALTER DATABASE SET page you will see that Query Store now has a new option for QUERY_CAPTURE_MODE: CUSTOM.  For those of you with ad hoc workloads, this will help.

Remember that with QUERY_CAPTURE_MODE, the default for SQL Server 2016 and SQL Server 2017 is ALL, and for Azure SQL Database it is AUTO.  I previously recommended that everyone use AUTO, as this means you do not capture queries that are insignificant (thresholds determined by Microsoft and not publicly documented, but it’s at least 3 executions and some amount of of CPU).

The CUSTOM option allows you determine what queries are captured based on:

  • EXECUTION_COUNT
  • TOTAL_COMPILE_CPU_TIME_MS
  • TOTAL_EXECUTION_CPU_TIME_MS

These three options operate in an OR manner.  For example, if I set the values as follows:

  • EXECUTION_COUNT = 100
  • TOTAL_COMPILE_CPU_TIME_MS = 60000
  • TOTAL_EXECUTION_CPU_TIME_MS = 60000

A query would only be captured in Query Store if it executed at least 100 times, if the total compile time for the query was at least 60 seconds, or if the total execution time (for all executions) was at least 60 seconds.

You also control the interval across which those options are tracked via the STALE_CAPTURE_POLICY_THRESHOLD setting.  This can be as low as 1 hour and as high as 7 days.  If I set it to 1 hour, then if the query did not exceed any of the thresholds within the 1 hour time frame, it wouldn’t be captured.  Ultimately, you can control what Query Store captures based on executions, compile time, or duration, which will help keep the size of the Query Store data much more manageable.

In addition to these new options, the default values for two settings changed in SQL Server 2019.  Specifically:

  • MAX_STORAGE_SIZE_MB increased from 100MB to 1000MB
  • QUERY_STORE_CAPTURE_MODE changed from ALL to AUTO

I have a fair bit of testing I want to do to see these settings in action, but I wanted to give you all a heads up if you’re looking to upgrade to 2019 when it’s released, or if you have had a less-than-positive experience with Query Store and wonder if it’s something you’ll ever be able to use.  The answer is yes, and this is a step in the direction to make it happen.  More to come!