Skip to content

Latest commit

 

History

History
74 lines (64 loc) · 11.4 KB

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

File metadata and controls

74 lines (64 loc) · 11.4 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.database_query_store_options (Transact-SQL)
sys.database_query_store_options returns the Query Store options for this database.
rwestMSFT
randolphwest
wiassaf, randolphwest
05/23/2024
sql
system-objects
reference
DATABASE_QUERY_STORE_OPTIONS_TSQL
DATABASE_QUERY_STORE_OPTIONS
SYS.DATABASE_QUERY_STORE_OPTIONS_TSQL
SYS.DATABASE_QUERY_STORE_OPTIONS
sys.database_query_store_options catalog view
TSQL
=azuresqldb-current || >=sql-server-2016 || =azure-sqldw-latest || >=sql-server-linux-2017 || =azuresqldb-mi-current

sys.database_query_store_options (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi-asa]

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 STATE permission.

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