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
Output from sys.database_query_store_options for model

Output from sys.database_query_store_options for model

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:

Output from sys.database_query_store_options for msdb

Output from sys.database_query_store_options for msdb

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:

Output from sys.database_query_store_options for QSTest user database

Output from sys.database_query_store_options for QSTest user database

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.

SQL Server Query Store: Data Storage Options (and a Request!)

Microsoft recently announced that SQL Server 2016 RTM will be available June 1, 2016, and it was also confirmed that Query Store will be available in ALL editions of SQL Server.  This is pretty exciting, as there was some concern that it would be Enterprise-only.  I’ve done a couple sessions on Query Store this year, and the response to the feature has been fantastic.  If you haven’t checked it out yet, I highly recommend it (download 2016 RC3 here), and in this post I want to talk about the Query Store data – where it’s located and how it’s retained.

Query Store Data Storage

Enabling Query Store is pretty simple.  It’s done a per-database basis (note that master and tempdb cannot be enabled for Query Store) either through the UI or via T-SQL.  I first restored a copy of the AdventureWorks2016 database (download from here).   Interestingly enough, Query Store is already enabled for the database.  But if it were not, to enable it you right-click on the database and select Properties, go to the Query Store page, and then select Read Write for the Operation Mode (Requested).  This is the code that runs if you script it out from the UI:

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

 

Multiple settings are then configured to the default values for Query Store – it’s analogous to running this code:

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

 

The setting I want to point out today is MAX_STORAGE_SIZE.  Understand that with Query Store, the data is persisted *IN* the user database.  Which means that if you backup and restore the database to another instance (or detach and attach), the Query Store data goes with the database.  This is pretty handy.  You have the ability to set how much space can be used by Query Store within the user database with the MAX_STORAGE_SIZE setting.  The default value, 1024MB, is a good start.  Because workloads vary, and what data *you* might want to keep is different than what someone else want to keep, and there aren’t any recommendations as to what this size “should” be at this point.  It’s something you’re going to have test out in your environment – just like figuring out the “best” size for the transaction log for a database, or the size of tempdb for an instance.  Note that the more varied the workload (more unique plans), the more space you might need. Remus Rusanu points out in a recent post that the SQL Server team vastly under-estimated the space needed for a client workload – it consumed the Query Store allocated space in minutes rather than months.  Disk space is pretty cheap – I would probably start with a few GB of space for Query Store and monitor how much is used with this query:

SELECT
actual_state_desc,
current_storage_size_mb,
max_storage_size_mb,
flush_interval_seconds,
interval_length_minutes,
size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
GO

SQL Server can automatically start to purge data from the store based on how much of that allocated space is used.  This is determined by size_based_cleanup_mode_desc setting, which defaults to AUTO.  With AUTO set, SQL Server will start purging data when the space used gets close the max_storage_size_mb setting.  If you have this set to OFF, then when current_storage_size_mb is reached, Query Store will stop collecting data and actual_state_desc will switch to READ_ONLY.

 Using Query Store to Find Query Regressions or Improvements

Ok, so now we’re clear on those storage-related settings.  There’s more of a conversation to have about how you’re going to use this data to find regressed queries, etc…but that’s an entirely separate post.  I want you now to think about the value of this data and how can you use it to measure the impact of code changes, SQL Server patches and upgrades, hardware changes and so on.

When you restore a copy of your production database to your Test/Dev/QA/UAT environment (because you have at least one of those, right?), the Query Store data from production will come with it.  This is great because you then run your workload in that environment and do comparisons using the Query Store data.  Awesome.  But what about this scenario, proposed by an attendee of last week’s IEPTO2 class…

You have an automated process developed in-house to backup and restore production databases down to lower environments (like Test/Dev/QA/UAT) on a regular basis (daily, weekly).  Because the Query Store data lives in the user database, you actually want to clear the Query Store data once the database is restored in the lower environment.  Then you test new code, etc. against the database.

When the production data is restored again, all data collected during testing in Test/Dev/QA/UAT will be lost…unless there is a way to export it.  Meaning, let’s save off that Query Store generated from testing, before the refresh so I can do comparisons between THOSE tests.

Now, there are catalog views that allow you to view the Query Store data.  You can copy that data into another database using SELECT INTO, and then do comparisons, but wouldn’t it be nice to have some kind of export option?  There’s a Connect item for that:

Export Query Store tables separately from the database tables: https://connect.microsoft.com/SQLServer/feedback/details/2620017/export-query-store-tables-separately-from-the-database-tables

If you think this is something that would be useful for Query Store, please up vote it!  Again, Query Store is available in ALL editions of SQL Server 2016, this is definitely a feature you CAN use and will want to use!  This potential option won’t make it into RTM, but with the change in how SQL Server is releasing CUs, the more important the SQL Server team sees this option (as a result of votes), the faster it might make it in a release.

If you haven’t looked into Query Store, I definitely recommend doing so!  When I demo’d the feature last week, one of our attendees said that he would consider skipping 2014 and going straight to 2016, just for this feature alone. A feature that’s available in EVERY Edition.