How to Modify Query Store Settings in SQL Server

In SQL Server, Query Store configuration settings can be adjusted after it’s been enabled to better suit your database’s workload and performance requirements. You might need to increase storage limits, change capture modes, or adjust data retention policies as your needs evolve.

To modify Query Store settings, use the ALTER DATABASE command with the options you want to change.

For example:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (
    MAX_STORAGE_SIZE_MB = 2000,
    INTERVAL_LENGTH_MINUTES = 30
);

Replace YourDatabaseName with the name of your database. You only need to specify the settings you want to change. Any options you don’t include will retain their current values.

Common Configuration Changes

Increasing storage limits when Query Store has reached capacity:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 5000);

Changing capture mode to be more or less selective:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

Adjusting data retention to keep history longer or purge it sooner:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 60)
);

Enabling automatic cleanup when approaching storage limits:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Changing statistics aggregation interval for more or less granular performance tracking:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Enabling wait statistics collection (SQL Server 2017+):

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);

Switching Between Operational Modes

You can switch Query Store between read-write and read-only mode without disabling it.

Switch to read-only (stops capturing new data):

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (OPERATION_MODE = READ_ONLY);

Switch back to read-write (resume capturing):

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Read-only mode can be useful during maintenance windows or when you want to preserve existing data without adding new captures temporarily.

Modifying Multiple Settings at Once

You can change several settings in a single command. To do this, simply separate the settings with commas:

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
);

This approach is more efficient than running multiple ALTER DATABASE statements and ensures all changes take effect together.

Verifying Configuration Changes

After modifying settings, you can use the following query to verify the changes took effect:

SELECT 
    max_storage_size_mb,
    query_capture_mode_desc,
    stale_query_threshold_days,
    size_based_cleanup_mode_desc,
    interval_length_minutes
FROM sys.database_query_store_options;

Configuration changes take effect immediately, but some changes (like increasing storage limits) may take a moment to reflect in the system views.

Available Query Store Configuration Options

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