Skip to content

sys-database-query-store-options-transact-sql.md

Latest commit

 

History

History
78 lines (67 loc) · 12 KB

sys-database-query-store-options-transact-sql.md

File metadata and controls

78 lines (67 loc) · 12 KB
title sys.database_query_store_options (Transact-SQL)
description sys.database_query_store_options returns the Query Store options for this database.
author rwestMSFT
ms.author randolphwest
ms.reviewer wiassaf, randolphwest
ms.date 02/25/2026
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
DATABASE_QUERY_STORE_OPTIONS_TSQL
DATABASE_QUERY_STORE_OPTIONS
SYS.DATABASE_QUERY_STORE_OPTIONS_TSQL
SYS.DATABASE_QUERY_STORE_OPTIONS
helpviewer_keywords
sys.database_query_store_options catalog view
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || =azure-sqldw-latest || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

sys.database_query_store_options (Transact-SQL)

[!INCLUDE SQL Server 2016 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics FabricSQLDB]

Returns the Query Store options for this database.

Column name Data type Description
desired_state smallint Indicates the desired operation mode of Query Store, explicitly set by user.

0 = OFF
1 = READ_ONLY
2 = READ_WRITE
4 = READ_CAPTURE_SECONDARY
desired_state_desc nvarchar(60) Textual description of the desired operation mode of Query Store:

OFF
READ_ONLY
READ_WRITE
READ_CAPTURE_SECONDARY
actual_state smallint Indicates the operation mode of Query Store. In addition to list of desired states required by the user, actual state can be an error state.

0 = OFF
1 = READ_ONLY
2 = READ_WRITE
3 = ERROR
4 = READ_CAPTURE_SECONDARY
actual_state_desc nvarchar(60) Textual description of the actual operation mode of Query Store.

OFF
READ_ONLY
READ_WRITE
ERROR
READ_CAPTURE_SECONDARY

There are situations when actual state is different from the desired state:
- If the database is set to read-only mode, or if Query Store size exceeds its configured quota, Query Store might operate in read-only mode even if you specify read-write.
- In extreme scenarios Query Store can enter an ERROR state because of internal errors. In [!INCLUDE ssSQL17] and later versions, if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. If running sp_query_store_consistency_check doesn't work, or if you're using [!INCLUDE sssql16-md], you need to clear the data by running ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL;
readonly_reason int When the desired_state_desc is READ_WRITE and the actual_state_desc is READ_ONLY, readonly_reason returns a bit map to indicate why the Query Store is in readonly mode.

1 - database is in read-only mode
2 - database is in single-user mode
4 - database is in emergency mode
8 - database is secondary replica (applies to availability groups and [!INCLUDE ssazure-sqldb] geo-replication). This value can be effectively observed only on readable secondary replicas
65536 - the Query Store reached the size limit set by the max_storage_size_mb option. For more information about this option, see ALTER DATABASE SET options.
131072 - The number of different statements in Query Store reached the internal memory limit. Consider removing queries that you don't need or upgrading to a higher service tier to enable transferring Query Store to read-write mode.
262144 - Size of in-memory items waiting to be persisted on disk reached the internal memory limit. Query Store is in read-only mode temporarily until the in-memory items are persisted on disk.
524288 - Database reached disk size limit. Query Store is part of user database, so if there's no more available space for a database, that means that Query Store can't grow further anymore.

To switch the Query Store operations mode back to read-write, see Verify that Query Store collects query data continuously.
current_storage_size_mb bigint Size of Query Store on disk in megabytes.
flush_interval_seconds bigint The period for regular flushing of Query Store data to disk in seconds. Default value is 900 (15 min).

Change by using the ALTER DATABASE <database> SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = <interval>) statement.
interval_length_minutes bigint The statistics aggregation interval in minutes. Arbitrary values aren't allowed. Use one of the following values: 1, 5, 10, 15, 30, 60, and 1440 minutes. The default value is 60 minutes.
max_storage_size_mb bigint Maximum disk size for the Query Store in megabytes (MB). Default value is 100 MB up to [!INCLUDE ssSQL17], and 1 GB in [!INCLUDE sql-server-2019] and later versions.

For [!INCLUDE sssds] Premium edition, the default is 1 GB, and for [!INCLUDE sssds] Basic edition, the default is 10 MB.

Change by using the ALTER DATABASE <database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <size>) statement.
stale_query_threshold_days bigint Number of days that the information for a query is kept in the Query Store. Default value is 30. Set to 0 to disable the retention policy.
For [!INCLUDE sssds] Basic edition, the default is 7 days.

Change by using the ALTER DATABASE <database> SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = <value>)) statement.
max_plans_per_query bigint Limits the maximum number of stored plans. Default value is 200. If the maximum value is reached, Query Store stops capturing new plans for that query. Setting to 0 removes the limitation for the number of captured plans.

Change by using the ALTER DATABASE<database> SET QUERY_STORE (MAX_PLANS_PER_QUERY = <n>) statement.
query_capture_mode smallint The currently active query capture mode:

1 = ALL - all queries are captured. This is the default configuration value for [!INCLUDE sssql16-md] and later versions.

2 = AUTO - capture relevant queries based on execution count and resource consumption. This is the default configuration value for [!INCLUDE sssds].

3 = NONE - stop capturing new queries. Query Store continues to collect compile and runtime statistics for queries that were captured already. Use this configuration cautiously since you might miss capturing important queries.

4 = CUSTOM - Allows more control over the query capture policy using the QUERY_CAPTURE_POLICY options.

Applies to: [!INCLUDE ssSQL19] and later versions.
query_capture_mode_desc nvarchar(60) Textual description of the actual capture mode of Query Store:

ALL (default for [!INCLUDE sssql16-md] and later versions)

AUTO (default for [!INCLUDE sssds])

NONE

CUSTOM
capture_policy_execution_count int Query capture mode CUSTOM policy option. Defines the number of times a query is executed over the evaluation period. The default is 30.

Applies to: [!INCLUDE ssSQL19] and later versions.
capture_policy_total_compile_cpu_time_ms bigint Query capture mode CUSTOM policy option. Defines total elapsed compile CPU time used by a query over the evaluation period. The default is 1000.

Applies to: [!INCLUDE ssSQL19] and later versions.
capture_policy_total_execution_cpu_time_ms bigint Query capture mode CUSTOM policy option. Defines total elapsed execution CPU time used by a query over the evaluation period. The default is 100.

Applies to: [!INCLUDE ssSQL19] and later versions.
capture_policy_stale_threshold_hours int Query capture mode CUSTOM policy option. Defines the evaluation interval period to determine if a query should be captured. The default is 24 hours.

Applies to: [!INCLUDE ssSQL19] and later versions.
size_based_cleanup_mode smallint Controls whether cleanup is automatically activated when total amount of data gets close to maximum size:

0 = OFF - size-based cleanup aren't automatically activated.
1 = AUTO - size-based cleanup is automatically activated when size on disk reaches 90 percent of max_storage_size_mb. This is the default configuration value.

Size-based cleanup removes the least expensive and oldest queries first. It stops when approximately 80 percent of max_storage_size_mb is reached.
size_based_cleanup_mode_desc nvarchar(60) Textual description of the actual size-based cleanup mode of Query Store:

OFF
AUTO (default)
wait_stats_capture_mode smallint Controls whether Query Store performs capture of wait statistics:

0 = OFF
1 = ON

Applies to: [!INCLUDE ssSQL17] and later versions.
wait_stats_capture_mode_desc nvarchar(60) Textual description of the actual wait statistics capture mode:

OFF
ON (default)

Applies to: [!INCLUDE ssSQL17] and later versions.
actual_state_additional_info nvarchar(4000) Currently unused.

Permissions

Requires the VIEW DATABASE PERFORMANCE STATE permission, or a greater permission such as VIEW DATABASE STATE.

In [!INCLUDE sssql16-md] through [!INCLUDE sssql19-md], requires the VIEW DATABASE STATE permission. In [!INCLUDE sssql22-md] and later versions, requires the VIEW DATABASE PERFORMANCE STATE permission on the database, or a greater permission such as VIEW DATABASE STATE.

Remarks

An actual_state_desc value of READ_CAPTURE_SECONDARY is the expected state when Query Store for secondary replicas is enabled. For more information, see Query Store for secondary replicas.

Related content