In SQL Server 2017 there are nine (9) settings related to Query Store.  While these are documented in sys.database_query_store_options, I often get asked what the value for each setting “should” be.  I’ve listed out each setting below, along with the default value and considerations around changing the setting.

 

OPERATION_MODE

The default value for a new or upgraded database in SQL Server 2016 or SQL Server 2017 is OFF.  For Azure SQL Database, the default value is READ_WRITE.

If you want to enable Query Store, this needs to be set to READ_WRITE, which is the desired state.

You also have the option of READ_ONLY, whereby new queries, new plans, runtime statistics, and wait statistics (in SQL Server 2017) will not be captured, but any forced plans will still be forced.  This state can occur if you reach the MAX_STORAGE_SIZE_MB limit (see below).  You can check actual state against desired state using the query below:

SELECT [actual_state_desc], [desired_state_desc]
FROM [sys].[database_query_store_options];
GO

It’s recommended to always run in a READ_WRITE state.  I have heard of some environments which switch between READ_WRITE and READ_ONLY.  If you want to understand your workload and have the data needed to troubleshoot performance issues, you need to be capturing information on a continual basis.

 

QUERY_CAPTURE_MODE

The default value for SQL Server 2016 and SQL Server 2017 is ALL.  For Azure SQL Database, the default value is AUTO.

With AUTO, queries that are insignificant from a resource utilization perspective, or executed infrequently, are not captured.  If you need to capture queries that may only execute a few times, or those that use very few resources, then use ALL.  Otherwise, use AUTO, as this will capture the relevant majority of your workload.

There is a third option, NONE, where no new queries are captured.  Runtime and wait statistics will continue to be captured for queries that are already in Query Store.

I recommend setting this option to AUTO, as the number of queries in your environment that need tuning/your attention is a small percentage of the total number of queries that execute.  You won’t miss out on important data if you exclude queries that don’t use a lot of resources or don’t execute very often.

 

MAX_PLANS_PER_QUERY

The default value for SQL Server 2016, SQL Server 2017, and Azure SQL Database is 200.

This setting is an integer, so theoretically you can set it to 2,147,483,647!  If you don’t know how many distinct plans you might have for a query, you can use sys.dm_exec_query_stats and get a count of distinct query_plan_hash values for a given query_hash:

SELECT [query_hash], COUNT (DISTINCT [query_plan_hash])
FROM [sys].[dm_exec_query_stats]
GROUP BY [query_hash]
ORDER BY 2 DESC;
GO

While I would like to believe that 200 distinct plans for a query is really high, I’ve talked to several DBAs who confirmed they had counts in the thousands.  Thus, you may need to increase this settings if you have queries that are unstable and generate a lot of different plans, and you want to capture each different plan.  Understand that a workload with a large number of plans for a query will require more space, hence the limitation.  You can set the limit lower than the possible number of plans to control the size, with the understanding that you won’t capture every plan variation.  The value of 200 is a good starting point for most environments.

 

MAX_STORAGE_SIZE_MB

For SQL Server 2016 and SQL Server 2017 the default value is 100MB.  For Azure SQL Database, the default value is specific to the tier (Basic = 10MB, Standard = 100MB, Premium = 1GB).

The Query Store data is stored in internal tables in the user database (in the PRIMARY filegroup, like other system tables) and exposed through catalog views.  You can configure how much disk space can be used by Query Store.

This settings should be increased for an on-premises solution.  It may need to be increased for SQL Database, there are multiple factors that affect how much space you will need for Query Store data.  These factors are:

  • The value for QUERY_CAPTURE_MODE; if you’re capturing ALL queries, you will have more information than if using AUTO.  The amount of data is difficult to predict – it depends on your workload (Do you have a lot of queries that run just one time?  Do you have a lot of queries that use very little resources?).
  • The length of time you retain data in Query Store (CLEANUP_POLICY).  The more data you keep, the more space you will need.
  • Whether you’re running SQL Server 2017 and capturing wait statistics information (WAIT_STATISTICS_CAPTURE_MODE).  The wait statistics information is extremely valuable, but it is more data to keep and retain.
  • The value for INTERVAL_LENGTH_MINUTES.  The lower this value, the more runtime statistics data you will have and thus you will need more space.
  • Type of workload.  If you have an ad-hoc workload that has high variation in query text, then you will have more individual queries stored, and thus more plans and more runtime and wait statistics as that information.  If you have a stable workload that does not have ad-hoc queries or queries generated by dynamic strings or ORM tools like NHibernate or Entity Framework), then you will have a fewer number queries and less data overall.

As you can see, there is no “answer” for what the value for MAX_STORAGE_SIZE_MB should be.  I recommend starting with 2GB allocated, and then monitor via sys.database_query_store_options and Extended Events.  For some solutions, 1GB is plenty.  For other solutions, you may need 5GB or more.

 

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)

The default value for SQL Server 2016, SQL Server 2017, and Azure SQL Database is 30, with the exception of the Basic tier for Azure SQL Database, which defaults to 7 days.

How much historical data do you want to keep?  If you’re a shop that develops in production, you might want to keep more history.  If your workload is pretty stable and you only roll-out changes quarterly or less frequently, 30 days may be enough information for you.  The more data that you retain, the more disk space you will need.  If you’re not certain about workload, I recommend starting with at least 30 days for this setting, and over the first couple months of use you’ll figure out if you want to keep older data.

 

SIZE_BASED_CLEANUP_MODE

The default value for SQL Server 2016, SQL Server 2017, and Azure SQL Database is AUTO, and I recommend leaving it as such.

With a value of AUTO, as Query Store gets close to the storage size allocated by MAX_STORAGE_SIZE_MB it will automatically purge out the oldest data to make sure there is enough space for new data.  There is a possibility for data that has not reached the CLEANUP_POLICY to be removed (e.g. if MAX_STORAGE_SIZE_MB is 2GB and CLEANUP_POLICY is 30 days, and you reach 2GB in 15 days, data will start to be removed).

You can set this to OFF, but in that scenario, if the MAX_STORAGE_SIZE_MB is reached the OPERATION_MODE will change to READ_ONLY and you will no longer capture new data.  It is recommended to leave this set to AUTO and adjust MAX_STORAGE_SIZE_MB as appropriate.

 

DATA_FLUSH_INTERVAL_SECONDS

The default value for SQL Server 2016, SQL Server 2017, and Azure SQL Database is 900 (15 minutes).

It is recommended to leave this value at the default.

 

INTERVAL_LENGTH_MINUTES

The default value for SQL Server 2016, SQL Server 2017, and Azure SQL Database is 60.

This is a critical setting, as it determines the window of time across which runtime statistics will be aggregated.  You can only select fixed values for this settings (1, 5, 10, 15, 30, 60, 1440).  The smaller this value, the smaller the window of time for which you will have runtime stats.  This will allow you to look at data at a more granular level.  However, the smaller the value the more data you will capture and thus the more space that is needed.

For the client environments that I support, I’ve set this to 30, as I like a smaller window of time for analysis and based on the performance issues I’ve had to troubleshoot thus far, that’s been a good window.  If you have space constraints or concerns, then leave it at the default of 60.

 

WAIT_STATISTICS_CAPTURE_MODE

The default value for SQL Server 2016, SQL Server 2017, and Azure SQL Database is ON.

If you upgrade a database which has Query Store enabled from SQL Server 2016 to SQL Server 2017, the WAIT_STATISTICS_CAPTURE_MODE will be enabled on upgrade.  If you have a database on SQL Server 2017 and enable Query Store, this option will be enabled.

I recommend enabling this option if you’re on SQL Server 2017 as this information can be extremely valuable when troubleshooting query performance.  Note that you may need to increase MAX_STORAGE_SIZE_MB to accommodate this additional data.