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).
- 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.
- 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.
- 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.
- Not sure whether you have an ad hoc workload? Check out my post on Examining the Performance Impact of an Adhoc Workload.
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.