SQL Server Query Store: Default Settings

Last week I wrote a quick post about storage settings for Query Store.  Today I’m going to take a moment to discuss the default settings that are applied when you initially enable Query Store.  I’m not going go through all the settings and what they mean, you can find that information here.  But I do want to discuss from where they originate.

When you enable Query Store through the UI, you have the option to change any of options (e.g. Max Size (MB), Size Based Cleanup Mode).  The values for the settings are pulled from model, just like other database defaults (Recovery Model, Compatability Mode, etc.).  Now, what’s interesting is that you can change these defaults in model, but only using T-SQL.  In fact, you can only enable Query Store for model and msdb using TSQL, the Query Store page is not available for either database when you expand Databases, then System Database, and right-click on the database and select Properties.

The Query Store documentation specifically states that it cannot be enabled for master and tempdb, which leads us to believe we can enable it for model and msdb.  Now, I haven’t figured out a use case for either (I have yet to troubleshoot query performance against model or msdb), but I figured I’d try it for both.  I started with model, just using the basic syntax which would pull the default values for the options:

USE [master];
GO
ALTER DATABASE [model] SET QUERY_STORE = ON;
GO
ALTER DATABASE [model] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

Then I checked to confirm the values that were set:

USE [model];
GO
SELECT actual_state_desc, readonly_reason, desired_state_desc, current_storage_size_mb,
max_storage_size_mb, flush_interval_seconds, interval_length_minutes,
stale_query_threshold_days, size_based_cleanup_mode_desc,
query_capture_mode_desc, max_plans_per_query
FROM sys.database_query_store_options;
GO

My output was empty.  Huh?

I tested msdb.

USE [master];
GO
ALTER DATABASE [msdb] SET QUERY_STORE = ON;
GO
ALTER DATABASE [msdb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
USE [msdb];
GO
SELECT actual_state_desc, readonly_reason, desired_state_desc, current_storage_size_mb,
max_storage_size_mb, flush_interval_seconds, interval_length_minutes,
stale_query_threshold_days, size_based_cleanup_mode_desc,
query_capture_mode_desc, max_plans_per_query
FROM sys.database_query_store_options;
GO

This time I had output:

Ok.  So…maybe I can only use model to change default values.  Let’s see if that works.  I edited the Query Store settings for model:

USE [master];
GO
ALTER DATABASE [model] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 78),
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
MAX_STORAGE_SIZE_MB = 2048,
QUERY_CAPTURE_MODE = ALL,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200);
GO

Then I created a new database and enabled Query Store:

CREATE DATABASE [QSTest]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'QSTest', FILENAME = N'C:\Databases\QSTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'QSTest_log', FILENAME = N'C:\Databases\QSTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB );
GO
USE [master];
GO
ALTER DATABASE [QSTest] SET QUERY_STORE = ON;
GO
ALTER DATABASE [QSTest] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

I checked sys.database_query_store_options:

It definitely picked up the changes I made to model.

So…if I can’t see the settings for model in sys.database_query_store_options, then is Query Store really enabled?  I ran a few queries against model, then checked the Query Store catalog views:

USE [model];
GO
SELECT *
FROM sys.all_objects;
GO
SELECT *
FROM sys.all_columns;
GO
SELECT *
FROM sys.database_files;
GO
SELECT qst.query_text_id, qsq.query_id, qsq.object_id, qsq.count_compiles, qst.query_sql_text, qst.statement_sql_handle, qsq.query_hash
FROM sys.query_store_query_text qst
JOIN sys.query_store_query qsq ON qst.query_text_id = qsq.query_text_id;
GO

Guess what?  There was no data in the Query Store tables.  So…let’s repeat the test against msdb.

USE [msdb];
GO
SELECT *
FROM sys.all_objects;
GO
SELECT *
FROM sys.all_columns;
GO
SELECT *
FROM sys.database_files;
GO
SELECT qst.query_text_id, qsq.query_id, qsq.object_id, qsq.count_compiles, qst.query_sql_text, qst.statement_sql_handle, qsq.query_hash
FROM sys.query_store_query_text qst
JOIN sys.query_store_query qsq ON qst.query_text_id = qsq.query_text_id;
GO

In this case, there WAS data in the Query Store tables.

So, what does this mean?  That you can only truly enable and use Query Store against one system database: msdb.  It cannot be enabled for master and tempdb (it throws an error: Msg 12438, Level 16, State 1, Line 19
Cannot perform action because Query Store cannot be enabled on system database tempdb.) and while it can be enabled for model, it’s only for the purposes of changing the default settings for Query Store for new databases on this instance (I’ve confirmed this with the SQL Server team).  Is that something you want to do?  Perhaps…but I can do those on a per-database case when I’m initially enabling Query Store, or later on if so desired. I’m always wary of changing settings in model, though I do change Recovery Model to SIMPLE so I don’t end up with a ridiculously large transaction log…the default is FULL and if someone doesn’t setup transaction log backups then that log is going to grow until it consumes all space on the disk.  I also set Auto Growth values to fixed, reasonable numbers.

For each database where I enable Query Store, I’d consider the workload and then look at the settings.  I tend to think that the default value of 100MB for MAX_STORAGE_SIZE_MB is really low, and I would be inclined to bump up STALE_QUERY_THRESHOLD_DAYS from 30 to something a bit higher.  I’d also probably drop  DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it.  This setting determines how often Query Store data is flushed to disk.  If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.  I’d also think about changing INTERVAL_LENGTH_MINUTES to a value smaller than 60 if I wanted to aggregate my query data over a smaller amount of time.  Sometimes interesting events happen within a 60 minute time frame, and they get can lost when data is aggregated across that window.  However, aggregating more frequently means I’m adding processing overhead to the system – there’s a trade-off there to figure out.

The take home message…you probably don’t need to enable Query Store for any system database, but if you want to change the default values that user databases pick up when you enable it, you can do that using model.