SQL Server’s Query Store provides a handy way to track query performance over time, making it easier to troubleshoot issues and optimize workloads. To get the most out of it, it helps to understand the various configuration options that control how it collects, stores, and manages data.
The following table lists all configuration options available when modifying Query Store settings:
| Option | Values | Description |
|---|---|---|
OPERATION_MODE | READ_WRITE, READ_ONLY | Controls whether Query Store actively captures new data (READ_WRITE) or only allows reading existing data (READ_ONLY) |
QUERY_CAPTURE_MODE | ALL, AUTO, CUSTOM, NONE | Determines which queries are captured: ALL captures everything, AUTO captures resource-intensive queries, CUSTOM uses custom policies, NONE disables capture while keeping Query Store enabled |
MAX_STORAGE_SIZE_MB | Number (default: 100) | Maximum disk space in megabytes that Query Store can use before switching to read-only mode or triggering cleanup |
DATA_FLUSH_INTERVAL_SECONDS | Number (default: 900) | How often buffered data is written from memory to disk, balancing performance overhead with data durability |
INTERVAL_LENGTH_MINUTES | Number (default: 60) | How often runtime statistics are aggregated into summary intervals, affecting performance history granularity |
SIZE_BASED_CLEANUP_MODE | AUTO, OFF | When set to AUTO, automatically removes old data when approaching storage limit; OFF requires manual cleanup |
CLEANUP_POLICY | (STALE_QUERY_THRESHOLD_DAYS = number) | Number of days to retain query data before it becomes eligible for automatic cleanup (default: 30) |
MAX_PLANS_PER_QUERY | Number (default: 200) | Maximum number of execution plans stored per individual query to prevent excessive storage consumption |
WAIT_STATS_CAPTURE_MODE | ON, OFF | Enables or disables collection of wait statistics (SQL Server 2017+) |
QUERY_CAPTURE_POLICY | See custom options below | Used with QUERY_CAPTURE_MODE = CUSTOM to define fine-grained capture criteria |
Custom Query Capture Policy Options
When using QUERY_CAPTURE_MODE = CUSTOM, these additional options are available within QUERY_CAPTURE_POLICY:
| Option | Values | Description |
|---|---|---|
STALE_CAPTURE_POLICY_THRESHOLD | Number + DAYS or HOURS | How long before a query’s cached plan is considered stale and recaptured |
EXECUTION_COUNT | Number | Minimum number of executions required before a query is captured |
TOTAL_COMPILE_CPU_TIME_MS | Number | Minimum compile CPU time in milliseconds required to capture a query |
TOTAL_EXECUTION_CPU_TIME_MS | Number | Minimum execution CPU time in milliseconds required to capture a query |
Example of Usage
Here’s an example of enabling Query Store on a database, along with various options:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);If you already have Query Store enabled, but you just want to change some of the configuration options, you can omit the = ON part, and just go straight to the configuration options.
For example:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (
MAX_STORAGE_SIZE_MB = 3000,
QUERY_CAPTURE_MODE = AUTO,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 45),
SIZE_BASED_CLEANUP_MODE = AUTO,
INTERVAL_LENGTH_MINUTES = 60
);If you use QUERY_CAPTURE_MODE = CUSTOM, then you will need to provide the settings in the QUERY_CAPTURE_POLICY parameter.
For example:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 10,
TOTAL_COMPILE_CPU_TIME_MS = 100,
TOTAL_EXECUTION_CPU_TIME_MS = 500
),
MAX_STORAGE_SIZE_MB = 1000
);If you disable Query Store on a database, it will retain its configuration settings. That means that if you re-enable it again without specifying any config options, it will use the ones that were previously specified. You can of course override these with new settings if required.