Query Store default settings

Foreword 🔗︎

If you are on a SQL Server 2016 or later (and you should be by now), the Query Store should be set to ON.
It’s just too good of a tool to pass up. But while the default settings are good enough for the majority, there might be some workload that could use a little bit of tweaking.

Before we start adjusting the defaults, it might be useful to know what the values are. I could just point you to the official documentation, but where’s the fun in that? Let’s try some more hands-on exercise.

Turn on the Query Store and check the settings 🔗︎

Let’s create a new database and enable the Query Store.

⚠️ Disclaimer: A new database is created from the system model database.
If you have changed the Query Store settings there, you will get a different result.

USE master;
CREATE DATABASE QS_Test; /* Create a new DB */
GO
ALTER DATABASE QS_Test /* Enable the QS */
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

The documentation says we should be getting these values:

⚠️ Disclaimer: This test has been run on SQL Server 2019. On versions 2016 and 2017 the default Capture Mode is Auto.

We can check it with this query

USE QS_Test; /* Change context to our DB */

SELECT
    max_storage_size_mb,
    interval_length_minutes,
    stale_query_threshold_days,
    size_based_cleanup_mode_desc,
    query_capture_mode_desc,
    flush_interval_seconds
FROM sys.database_query_store_options;

The first value (max_storage_size_mb) doesn’t match up. The documentation says 100 but I got 1000.

Also what exactly is the value AUTO for query_capture_mode and size_based_cleanup_mode?

QUERY_CAPTURE_MODE 🔗︎

There are 4 options for the Query_Capture_Mode (described in detail here):

• None
• All
• Custom
• Auto

The first two are pretty self-explanatory.
The Custom is the one that’s interesting. It has four parameters defining the policy.

The first condition out of these three that evaluates to true save the query to the Query Store (like an OR condition)

• STALE_CAPTURE_POLICY_THRESHOLD (default 1 day)

  • Evaluation period or as I like to say: “the time limit”.
    At least one of the three conditions (below) must be true during this time limit for the query to be saved into the Query Store.

• EXECUTION_COUNT (default 30)

• TOTAL_COMPILE_CPU_TIME_MS (default 1000)

• TOTAL_EXECUTION_CPU_TIME_MS (default 100)

I think these are self-explanatory as well. If we take the Custom policy defaults it means that our query has to:

Execute 30 times
OR total compile CPU time needs to be > 1s
OR total execution CPU time needs to be > 100ms
all during 24 hours.

Capture mode Auto 🔗︎

Does that mean that the Auto is the same as the Custom defaults?
To dig into that, we’ll have to use Extended Events. More specifically the query_store_db_settings_and_state event. Just a minimalistic session without a target (Watch Live Data) will do.

CREATE EVENT SESSION [QS_Settings] ON SERVER
ADD EVENT qds.query_store_db_settings_and_state
GO

It runs pretty infrequently (even tens of minutes on my machine) and creates a Query Store settings snapshot for each non-system database.

We can see that this time the values match the default of the Custom execution policy. I’ll leave the size_based_cleanup_mode as an exercise for the reader.