How to Check Query Store Status and Configuration in SQL Server

SQL Server’s Query Store operates independently on each database and can be in different states depending on its configuration and current storage usage. Checking the status helps you verify that Query Store is working as expected and identify any issues that might prevent data collection.

To check Query Store status and configuration, query the sys.database_query_store_options view:

SELECT 
    actual_state_desc,
    readonly_reason,
    desired_state_desc,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc,
    stale_query_threshold_days,
    max_plans_per_query,
    flush_interval_seconds,
    interval_length_minutes,
    wait_stats_capture_mode_desc
FROM sys.database_query_store_options;

Example output:

actual_state_desc  readonly_reason  desired_state_desc  current_storage_size_mb  max_storage_size_mb  query_capture_mode_desc  size_based_cleanup_mode_desc  stale_query_threshold_days  max_plans_per_query  flush_interval_seconds  interval_length_minutes  wait_stats_capture_mode_desc
-----------------  ---------------  ------------------  -----------------------  -------------------  -----------------------  ----------------------------  --------------------------  -------------------  ----------------------  -----------------------  ----------------------------
READ_WRITE         0                READ_WRITE          1                        1000                 ALL                      AUTO                          30                          200                  900                     60                       ON                          

Understanding the Configuration Fields

Here’s what each of the above columns are for:

Parameter Name	Description
actual_state_desc	The current operational state of Query Store. These can be OFF (disabled), READ_ONLY (not capturing new data), READ_WRITE (actively capturing), or ERROR (encountered a problem).
readonly_reason	Explains why Query Store is in read-only mode if applicable, such as reaching the storage limit, being manually set to read-only, or encountering errors.
desired_state_desc	The state you’ve configured Query Store to be in, which may differ temporarily from the actual state during transitions or when automatic protections trigger.
current_storage_size_mb	How much disk space Query Store is currently using for all captured data.
max_storage_size_mb	The maximum storage allocated to Query Store before it stops capturing data or triggers cleanup.
query_capture_mode_desc	Which queries are being captured. These can be ALL (everything), AUTO (resource-intensive queries only), CUSTOM (based on custom policies), or NONE (capture disabled).
size_based_cleanup_mode_desc	Whether Query Store automatically removes old data when approaching the storage limit (AUTO) or requires manual intervention (OFF).
stale_query_threshold_days	How many days query data is retained before being eligible for automatic cleanup.
max_plans_per_query	The maximum number of execution plans stored for each individual query to prevent a single query from consuming excessive storage.
flush_interval_seconds	How often Query Store flushes buffered data from memory to disk, balancing performance overhead against data durability.
interval_length_minutes	How often runtime statistics are aggregated into summary intervals, affecting the granularity of performance history.
wait_stats_capture_mode_desc	Whether Query Store is capturing wait statistics (ON) or not (OFF), available in SQL Server 2017 and later.

Common Status Scenarios

Here are some common configurations you might encounter:

• READ_WRITE with no readonly_reason – Query Store is operating normally and capturing data.
• READ_ONLY with readonly_reason indicating storage limit – Query Store has reached its maximum storage size and stopped capturing new data. You need to either increase MAX_STORAGE_SIZE_MB or clear old data.
• OFF – Query Store is disabled and not capturing any data.
• Actual state differs from desired state – This can happen temporarily during transitions or indicate a configuration issue that needs investigation.

Viewing All Configuration Settings

To see every Query Store configuration option, you can replace the column names with the asterisk wildcard:

SELECT *
FROM sys.database_query_store_options;

This returns all settings including additional columns like custom capture policy thresholds, internal tracking fields, and version information that may be useful for advanced troubleshooting or auditing purposes.