As with all new features, Query Store is not active for a database by default. You can enable and configure it in SQL Server Management Studio (SSMS) by using the database properties (Query Store Page) or by using Transact-SQL. To enable Query Store, your account must be db_owner of the database or a member of the sysadmin fixed server role.
To enable Query Store for a database, you need to select it and click on Properties. You can find a new Query Store property page at the bottom of the list. As mentioned at the beginning of the section, it is disabled by default, which is indicated by the values Off for both Operation Mode (Actual) and Operation Mode (Requested). To enable it, you need to change the value for the parameter Operation Mode (Requested) to Read Write, as shown in the following screenshot:
Figure 9-3; Enabling Query Store in SQL Server Management Studio
The same action can be done by using Transact-SQL. Use the following statement to enable Query Store in the database WideWorldImporters:
ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON;
As shown in the above section, to enable Query Store you need to set only one parameter or click once. When you do this, you have enabled it with default values for all of its parameters. There is a collection of query store options that can be configured. Again, you can set them through SSMS or Transact-SQL. However, some of them have different names and even metrics in both tools. Here is the list of configurable Query Store parameters:
- Operation Mode defines the operation mode of the query store. Only two modes are supported:
READ_WRITEandREAD_ONLY. Default value isREAD_WRITE, which means that Query Store collects query plans and runtime statistics and writes them to the disk.READ_ONLYmode makes sense only when the collected info in the query store exceeds the maximum allocated space for it. In this case, the mode is set toREAD_ONLYautomatically. The name of the parameter in theALTER DATABASETransact-SQL statement isOPERATION_MODE. - Max Size (MB) determines the space in megabytes allocated to the query store. The parameter data type is
bigint, default value is100. The name of the parameter in theALTER DATABASETransact-SQL statement isSTORAGE_SIZE_MB. - Statistics Collection Interval defines a fixed time window at which runtime execution statistics data is aggregated into the query store. The parameter data type is
bigint, default value is60.The name of the parameter in theALTER DATABASETransact-SQL statement isINTERVAL_LENGTH_MINUTES. - Data Flush Interval (Minutes) determines the frequency at which data written to the query store is persisted to disk. The parameter data type is
bigint, default value is15. The minimum value is 1 minute. If you want to use it in theALTER DATABASETransact-SQL statement its name isDATA_FLUSH_INTERVAL_SECONDS. As you can see, in the Transact-SQL you need to use seconds, and in SSMS, minutes as parameter metric. It seems that consistency is lost somewhere between these two configuration modes. - Query Store Capture Mode defines the scope of queries that will be captured. The parameter data type is
nvarchar, and has the following values:AUTO(only relevant queries based on execution count and resource consumption are captured),ALL(all queries are captured) andNONE(new queries are not captured, only info about already captured queries). Default value isALL. The name of the parameter in the ALTER DATABASE Transact-SQL statement isQUERY_CAPTURE_MODE. - Stale Query Threshold (Days) controls the retention period of persisted runtime statistics and inactive queries. The parameter data type is
bigint, default value is30days. When you use Transact-SQL you need to know that this parameter is a part of another parameter calledCLEANUP_POLICY. The following Transact-SQL code configures the Stale Query Threshold parameter for the Query Store in the databaseWideWorldImportersto60days:
ALTER DATABASE WideWorldImporters
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS =
60));
- Size Based Cleanup Mode controls whether cleanup will be automatically activated when the total amount of data gets close to maximum size. The parameter data type is
nvarchar, and has the following values:AUTO(size based cleanup will be automatically activated when size on disk reaches 90% ofmax_storage_size_mb. Size-based cleanup removes the least expensive and oldest queries first. It stops at approximately 80% ofmax_storage_size_mb),ALL(all queries are captured) andOFF(size based cleanup won't be automatically activated). Default value is OFF. The name of the parameter in theALTER DATABASETransact-SQL statement isSIZE_BASED_CLEANUP_MODE. MAX_PLANS_PER_QUERYdetermines the maximum number of plans maintained for a single query. The parameter data type isint, default value is 200. This parameter is inconsistently implemented, too. It is not even shown on the Query Store property page and can be set only via Transact-SQL.
Enabling Query Store with default values is equivalent to this Transact-SQL statement:
ALTER DATABASE WideWorldImporters
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 100,
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),
QUERY_CAPTURE_MODE = ALL,
SIZE_BASED_CLEANUP_MODE = OFF,
MAX_PLANS_PER_QUERY = 200
);
Default configuration is good for small databases or when you want to enable the feature and learn about it with the real query workload. However, for large databases and volatile database workloads you might need to change values for some Query Store options.
What are the most important settings, and are default values a good starting point for using Query Store in your database? The most important values are Max Size, Size Based Cleanup Mode, Statistics Collection Interval, and Query Capture Mode.
- Max Size: When maximum storage size allocated for Query Store is reached, then Query Store switches to Read Only operation mode and no info is captured anymore: you can only read already captured data. In most of the cases, this is not what you want—you are usually interested in the recent data. To leave the most recent data in Query Store, you would need to set
Size Based Cleanup Modeto Auto, which instructs a background process to remove the oldest data from the store, when the data size approaches the max size, keeping the most recent data in Query Store, similar to flight recorders in aircrafts. However, even for a moderate workload, 100 MB for storage is not enough: I've seen moderate databases, where Query Store contains queries from the last 24 hours only. Therefore, I would suggest you to increase this value at least to 1 GB and setSize Based Cleanup Modeto Auto to ensure that the most recent data is available and to avoid switching to Read Only operation mode. - Statistics Collection Interval: You can leave this on its default value (1 hour ) if you don't need to track queries over time in less granular intervals. If your database workload is volatile and depends on time patterns, you can consider using a smaller value. However, bear in mind that this will increase the amount of runtime statistics data.
- Query Store Capture Mode: Should be set to auto to instruct Query Store to capture info about only relevant queries based on execution count and resource consumption. This will exclude some queries and captured info would not reflect the whole workload, but the most relevant and important information will be there.