sys.database_query_store_options (Transact-sql) sys.database_query_store_options (Transact-SQL)

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

返回此数据库的查询存储选项。Returns the Query Store options for this database.

适用于SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本)、SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later), SQL 数据库SQL Database.

列名称Column name 数据类型Data type 说明Description
desired_statedesired_state smallintsmallint 指示查询存储所需的操作模式,由用户显式设置。Indicates the desired operation mode of Query Store, explicitly set by user.
0 = OFF0 = OFF
1 = READ_ONLY1 = READ_ONLY
2 = READ_WRITE2 = READ_WRITE
desired_state_descdesired_state_desc nvarchar(60)nvarchar(60) 查询存储所需操作模式的文本说明:Textual description of the desired operation mode of Query Store:
OFFOFF
READ_ONLYREAD_ONLY
READ_WRITEREAD_WRITE
actual_stateactual_state smallintsmallint 指示查询存储的操作模式。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 = OFF0 = OFF
1 = READ_ONLY1 = READ_ONLY
2 = READ_WRITE2 = READ_WRITE
3 = 错误3 = ERROR
actual_state_descactual_state_desc nvarchar(60)nvarchar(60) 查询存储实际操作模式的文本说明。Textual description of the actual operation mode of Query Store.
OFFOFF
READ_ONLYREAD_ONLY
READ_WRITEREAD_WRITE
错误ERROR

在某些情况下,实际状态与所需状态不同: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 may operate in read-only mode even if read-write was specified by the user.
-在极端方案中查询存储可能会由于内部错误而进入错误状态。- In extreme scenarios Query Store can enter an ERROR state because of internal errors. 从开始 SQL Server 2017 (14.x)SQL Server 2017 (14.x) ,如果发生这种情况,可以通过 sp_query_store_consistency_check 在受影响的数据库中执行存储过程来恢复查询存储。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. 如果运行 sp_query_store_consistency_check 不起作用,或者使用的是 SQL Server 2016 (13.x)SQL Server 2016 (13.x) ,则需要通过运行 ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL;If running sp_query_store_consistency_check doesn't work, or if you are using SQL Server 2016 (13.x)SQL Server 2016 (13.x), you will need to clear the data by running ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL;
readonly_reasonreadonly_reason intint 当 READ_WRITE desired_state_desc 并且 actual_state_desc READ_ONLY 时, readonly_reason 返回一个位图以指示查询存储处于只读模式的原因。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 -数据库处于只读模式1 - database is in read-only mode

2 -数据库处于单用户模式2 - database is in single-user mode

4 -数据库处于紧急模式4 - database is in emergency mode

8 -数据库是辅助副本 (适用于 Always On 和 Azure SQL 数据库Azure SQL Database 异地复制) 。8 - database is secondary replica (applies to Always On and Azure SQL 数据库Azure SQL Database geo-replication). 只能在 可读 辅助副本上有效观察此值This value can be effectively observed only on readable secondary replicas

65536 -查询存储已达到选项设置的大小限制 MAX_STORAGE_SIZE_MB65536 - the Query Store has reached the size limit set by the MAX_STORAGE_SIZE_MB option. 有关此选项的详细信息,请参阅 ALTER DATABASE SET options (transact-sql) For more information about this option, see ALTER DATABASE SET options (Transact-SQL).

131072 -查询存储中的不同语句数已达到内部内存限制。131072 - The number of different statements in Query Store has reached the internal memory limit. 请考虑删除不需要的查询或升级到更高的服务层,以允许将查询存储传输到读写模式。Consider removing queries that you do not need or upgrading to a higher service tier to enable transferring Query Store to read-write mode.


262144 -等待保留在磁盘上的内存中项的大小已达到内部内存限制。262144 - Size of in-memory items waiting to be persisted on disk has reached the internal memory limit. 查询存储将暂时处于只读模式,直到内存中的项保留在磁盘上。Query Store will be in read-only mode temporarily until the in-memory items are persisted on disk.


524288 -数据库已达到磁盘大小限制。524288 - Database has reached disk size limit. 查询存储是用户数据库的一部分,因此,如果数据库没有更多的可用空间,这意味着查询存储不能再进一步增长。Query Store is part of user database, so if there is no more available space for a database, that means that Query Store cannot grow further anymore.


若要将查询存储操作模式改回为读写模式,请参阅验证查询存储是否正在与查询存储的最佳实践持续收集查询数据部分。To switch the Query Store operations mode back to read-write, see Verify Query Store is Collecting Query Data Continuously section of Best Practice with the Query Store.
current_storage_size_mbcurrent_storage_size_mb bigintbigint 磁盘上的查询存储大小(以 mb 为单位)。Size of Query Store on disk in megabytes.
flush_interval_secondsflush_interval_seconds bigintbigint 将查询存储数据定期刷新到磁盘的时间(以秒为单位)。The period for regular flushing of Query Store data to disk in seconds. 默认值为 900 (15 分钟) 。Default value is 900 (15 min).

使用语句进行更改 ALTER DATABASE <database> SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = <interval>)Change by using the ALTER DATABASE <database> SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = <interval>) statement.
interval_length_minutesinterval_length_minutes bigintbigint 统计信息聚合间隔(分钟)。The statistics aggregation interval in minutes. 不允许使用任意值。Arbitrary values are not allowed. 使用以下项之一:1、5、10、15、30、60和1440分钟。Use one of the following: 1, 5, 10, 15, 30, 60, and 1440 minutes. 默认值为 60 分钟。The default value is 60 minutes.
max_storage_size_mbmax_storage_size_mb bigintbigint 查询存储的最大磁盘大小(mb (MB) )。Maximum disk size for the Query Store in megabytes (MB). 默认值为 100 MB,最大为 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 1 GB (从开始) SQL Server 2019 (15.x)SQL Server 2019 (15.x)Default value is 100 MB up to SQL Server 2017 (14.x)SQL Server 2017 (14.x), and 1 GB starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) .
对于 SQL 数据库SQL Database 高级版,默认值为 1 GB,对于 SQL 数据库SQL Database 基本版,默认值为 10 MB。For SQL 数据库SQL Database Premium edition, default is 1 GB and for SQL 数据库SQL Database Basic edition, default is 10 MB.

使用语句进行更改 ALTER DATABASE <database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <size>)Change by using the ALTER DATABASE <database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <size>) statement.
stale_query_threshold_daysstale_query_threshold_days bigintbigint 查询的信息在查询存储中保留的天数。Number of days that the information for a query is kept in the Query Store. 默认值为 30Default value is 30. 设置为0可禁用保留策略。Set to 0 to disable the retention policy.
对于 SQL 数据库SQL Database 基本版,默认值为 7 天。For SQL 数据库SQL Database Basic edition, default is 7 days.

使用语句进行更改 ALTER DATABASE <database> SET QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <value> ) )Change by using the ALTER DATABASE <database> SET QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <value> ) ) statement.
max_plans_per_querymax_plans_per_query bigintbigint 限制最大存储计划数。Limits the maximum number of stored plans. 默认值为 200Default value is 200. 如果达到最大值,查询存储将停止捕获该查询的新计划。If the maximum value is reached, Query Store stops capturing new plans for that query. 如果设置为0,则将删除已捕获计划数的限制。Setting to 0 removes the limitation with regards to the number of captured plans.

使用语句进行更改 ALTER DATABASE<database> SET QUERY_STORE (MAX_PLANS_PER_QUERY = <n>)Change by using the ALTER DATABASE<database> SET QUERY_STORE (MAX_PLANS_PER_QUERY = <n>) statement.
query_capture_modequery_capture_mode smallintsmallint 当前处于活动状态的查询捕获模式:The currently active query capture mode:

1 = 所有-捕获所有查询。1 = ALL - all queries are captured. 这是 SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 和更高版本) 的默认配置值。This is the default configuration value for SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later).

2 = 根据执行计数和资源消耗自动捕获相关查询。2 = AUTO - capture relevant queries based on execution count and resource consumption. 这是 SQL 数据库SQL Database 的默认配置值。This is the default configuration value for SQL 数据库SQL Database.

3 = 无-停止捕获新查询。3 = NONE - stop capturing new queries. 查询存储将继续为已经捕获的查询收集编译和运行时统计信息。Query Store will continue to collect compile and runtime statistics for queries that were captured already. 请谨慎使用此配置,因为可能会遗漏捕获重要的查询。Use this configuration cautiously since you may miss capturing important queries.
query_capture_mode_descquery_capture_mode_desc nvarchar(60)nvarchar(60) 查询存储实际捕获模式的文本说明:Textual description of the actual capture mode of Query Store:

所有 (默认值都 SQL Server 2016 (13.x)SQL Server 2016 (13.x)) ALL (default for SQL Server 2016 (13.x)SQL Server 2016 (13.x))

自动 () 的默认值 SQL 数据库SQL DatabaseAUTO (default for SQL 数据库SQL Database)

NONE
size_based_cleanup_modesize_based_cleanup_mode smallintsmallint 控制当数据总量接近最大大小时是否自动激活清除:Controls whether cleanup will be automatically activated when total amount of data gets close to maximum size:

0 = 不会自动激活基于大小的清理。0 = OFF - size-based cleanup won't be automatically activated.

1 = 如果磁盘上的大小达到max_storage_size_mb90% ,将自动激活自动大小的清理。1 = AUTO - size-based cleanup will be 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. 达到约 80%max_storage_size_mb 时,它将停止。It stops when approximately 80 percent of max_storage_size_mb is reached.
size_based_cleanup_mode_descsize_based_cleanup_mode_desc nvarchar(60)nvarchar(60) 查询存储的实际基于大小的清理模式的文本说明:Textual description of the actual size-based cleanup mode of Query Store:

OFFOFF
自动 (默认值) AUTO (default)
wait_stats_capture_modewait_stats_capture_mode smallintsmallint 控制查询存储是否执行等待统计信息的捕获:Controls whether Query Store performs capture of wait statistics:

0 = OFF0 = OFF
1 = 开启1 = ON
适用于SQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更高版本。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and later.
wait_stats_capture_mode_descwait_stats_capture_mode_desc nvarchar(60)nvarchar(60) 实际等待统计信息捕获模式的文本说明:Textual description of the actual wait statistics capture mode:

OFFOFF
(默认) ON (default)
适用于SQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更高版本。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and later.

权限Permissions

需要 VIEW DATABASE STATE 权限。Requires the VIEW DATABASE STATE permission.

另请参阅See Also

sys.query_context_settings (Transact-sql) sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-sql) sys.query_store_plan (Transact-SQL)
sys.query_store_query (Transact-sql) sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-sql) sys.query_store_query_text (Transact-SQL)
sys.query_store_runtime_stats (Transact-sql) sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL)
sys.query_store_runtime_stats_interval (Transact-sql) sys.query_store_runtime_stats_interval (Transact-SQL)
相关视图、函数和过程 Monitoring Performance By Using the Query Store
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
sys.fn_stmt_sql_handle_from_sql_stmt (Transact-sql) sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)
查询存储存储过程 (Transact-SQL)Query Store Stored Procedures (Transact-SQL)