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!

4 thoughts on “Query Store in SQL Server 2019 (CTP 3.0)

  1. Erin, do you know what happened to the proposed feature to add query store for READ secondaries in an AlwaysOn group? (using TEMPDB as the store). I heard this was in the works, but I have not seen any documentation.

    1. I believe it’s something they are still looking to do. Understand that it’s not an easy change to make as the replica is read-only, and I don’t believe that using tempdb as the store is the route they would take. What would happen when you restarted the instance and tempdb was recreated?

      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.