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.