Enabling Query Store is easy, but configuring it properly can be tricky. In this post we’ll analyse all the different settings and how to properly adapt them to get the most out of Query Store for your particular situation.
General
The general settings are portrayed differently in the GUI than they are when changing settings using T-SQL. This post will focus on the settings accessible using SSMS.
Operation Mode
- OFF: Query Store is disabled. It won’t capture any information on running queries, and no changes on its settings or data are allowed.
- READ_ONLY: Query Store is enabled for read-only operations, and settings changes.
- READ_WRITE: Query Store is enabled for read & write operations and settings changes. It will capture queries’ information based on its current configuration.
Monitoring
This group of settings configures of data is aggregated and flushed to disk.
Data Flush Interval (minutes)
The frequency at which data is persisted to disk. This event can trigger an internal cleanup process that in the best case scenario provokes a surge in the CPU usage, and in the worst case may potentially change your database’s Query Store into read-only mode. A whole post will be published about Query Store and how its space requirements and cleanup processes.
Since the amount of data loaded into memory doesn’t tend to be much, it is normally safe to have small intervals: test different values and monitor its impact on the CPU and I/O of the server if you find any issue.
Statistics Collection Interval
There is a predefined set of intervals to select from: the smaller the interval, the more granular information you can get from the Query Store. Let’s see two opposite examples, assuming a certain regularity on the database’s activity:
1 Minute: details of the activity on the database can be looked into down to the minute level, so certain analysis like query regression can highlight issues just one minute after it happens.
1 Hour: details and reports are only fully accurate after the o’clock. All reports such as query regression or top resource consuming won’t be comparable to previous intervals until the whole hour has passed and the data has been aggregated, potentially causing a delay when analysing current issues. But on the other hand, on stable systems it will save much disk space, since this interval could require roughly 1/60 of what the “1 minute” interval does. The math on Query Store’s space requirements is more complex than that, but you can get an overall idea.
Query Store Retention
Defines what data will remain in Query Store, and for how long before it is deleted.
Max Plans Per Query
A query may have different execution plans caused by changes in the index, statistics, or parameters used in the query. The default may be a bit too much (200 plans for a single query), but I have encountered some queries with dozens like the one below.
Max Size (MB)
Query Store’s objects are stored in the PRIMARY filegroup of the database, and this cannot be changed. Take this into consideration when estimating the database size, and if possible try always to move all your database objects out of the PRIMARY filegroup so that Query Store and the actual data won’t have to compete for space.
Query Store Capture Mode
- All: Captures all queries, no matter how small they look.
- Auto: Lets Query Store decide which queries are worth capturing.
- Custom: Lets you configure certain settings to decide what queries will be captured.
- None: No new queries will be captured, but statistics on the queries already registered in Query Store will still be captured.
Size Based Cleanup Mode
- Auto: When Query Store reaches 90% of its Max Size, an internal process will start deleting queries (starting with the ones with a lower CPU consumption) until it is below 85%.
- Off: No cleanup of the database will be triggered based on the Query Store current space utilisation.
Stale Query Threshold (Days)
Any statistics stored older than this number of days will be deleted.
Wait Statistics Capture Mode
Flag (On/Off) to enable the capture of Wait Statistics introduced in SQL 2017.
Query Store Capture Policy
This functionality was introduced in SQL 2019 in order to improve the opaque “Auto” Query Store Capture Mode previously available. Exceeding any of these thresholds in the time state in the State Threshold parameter will cause Query Store to log the query’s stats.
Execution Count
Number of executions a certain query must reach in the allocated Stale Threshold.
Total Compile CPU Time (ms)
Total time spent compiling the query in the allocated Stale Threshold. This threshold applies to the sum of compilation times for each the plans generated for the same query.
Total Execution CPU Time (ms)
Total CPU time spent execution the query in the allocated Stale Threshold. This threshold applies to the total CPU of the query, regardless of the execution plan used.
Stale Threshold
There is a predefined set of intervals to select from: this applies to all the settings above (Execution Count, Total Compile CPU Time, and Total Execution CPU).
There are some caveats on this capture mode, though:
It is not possible to review what information is the Custom mode analysing to decide what to persist or not. Although Query Store keeps the data belonging to the open interval in memory until the interval is closed and aggregated, there is no way to get information on what data is temporary stored during the Stale Threshold interval before it is closed and persistent to the system tables. Once I finish my investigation on the new capture mode I’ll publish a detailed post on the subject.
Notes on this post
All the tests have been performed on a SQL 2019 instance using SSMS 18.4. Different versions of both SQL server and the SSMS client may present slight differences.