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:
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!