查询存储最佳做法Best practices with Query Store

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本文概述使用 SQL Server 查询存储处理工作负载的最佳做法。This article outlines the best practices for using SQL Server Query Store with your workload.

使用最新 SQL Server Management StudioSQL Server Management StudioUse the latest SQL Server Management StudioSQL Server Management Studio

SQL Server Management StudioSQL Server Management Studio 提供了一组用户界面,旨在配置查询存储和使用收集的工作负载数据。has a set of user interfaces designed for configuring Query Store and for consuming collected data about your workload. 单击此处下载最新版 Management StudioManagement StudioDownload the latest version of Management StudioManagement Studio here.

有关如何使用查询存储进行故障排除的简要说明,请参阅查询存储 Azure 博客。For a quick description on how to use Query Store in troubleshooting scenarios, see Query Store Azure blogs.

在 Azure SQL 数据库中使用 Query Performance InsightUse Query Performance Insight in Azure SQL Database

如果在 Azure SQL 数据库SQL Database 中运行查询存储,则可使用 Query Performance Insight 来分析一定时段内的 DTU 消耗情况 。If you run Query Store in Azure SQL 数据库SQL Database, you can use Query Performance Insight to analyze DTU consumption over time. 虽然可以使用 Management StudioManagement Studio 来获取所有查询的详细资源消耗情况(例如 CPU、内存和 I/O),但使用 Query Performance Insight 可以快速且有效地确定查询对数据库总体 DTU 消耗情况的影响。While you can use Management StudioManagement Studio to get detailed resource consumption for all your queries, such as CPU, memory, and I/O, Query Performance Insight gives you a quick and efficient way to determine their impact on overall DTU consumption for your database. 有关详细信息,请参阅 Azure SQL Database Query Performance Insight(Azure SQL 数据库的 Query Performance Insight)。For more information, see Azure SQL Database Query Performance Insight.

将查询存储与弹性池数据库配合使用Use Query Store with elastic pool databases

可以毫无顾忌地在所有数据库中使用 Query Store,甚至是在密集打包的池中。You can use Query Store in all databases without concerns, in even densely packed pools. 与资源过度使用(为弹性池中的大量数据库启用了查询存储时可能会遇到这种情况)相关的所有问题都已得到了解决。All issues related to excessive resource usage that might have occurred when Query Store was enabled for the large number of databases in the elastic pools have been resolved.

始终根据工作负载调整查询存储Keep Query Store adjusted to your workload

根据工作负荷和性能故障排除要求来配置 Query Store。Configure Query Store based on your workload and performance troubleshooting requirements. 默认参数是启动的理想参数,但应监视查询存储在一定时段内的行为表现,并对其配置进行相应的调整。The default parameters are good enough to start, but you should monitor how Query Store behaves over time and adjust its configuration accordingly.

查询存储属性Query Store properties

下面是设置参数值时应遵循的准则:Here are guidelines to follow for setting parameter values:

最大大小 (MB) :为查询存储在数据库中占用的数据空间指定一个限制。Max Size (MB): Specifies the limit for the data space that Query Store takes inside your database. 这是最重要的设置,直接影响查询存储的操作模式。This is the most important setting that directly affects the operation mode of Query Store.

当查询存储收集查询、执行计划和统计信息时,其在数据库中的大小会一直增长,直至达到此限制。While Query Store collects queries, execution plans, and statistics, its size in the database grows until this limit is reached. 达到此限制后,Query Store 会自动将操作模式更改为只读,并停止收集新数据,这意味着你的性能分析自此不再精确。When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.

SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中的默认值为 100 MB。The default value in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x) is 100 MB. 如果工作负载会生成大量不同的查询和计划,或者想要让查询历史记录保存较长的时间,则此大小可能不足。This size might not be sufficient if your workload generates a large number of different queries and plans or if you want to keep query history for a longer period of time. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,默认值是 1 GB。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is 1 GB. 请跟踪当前的空间使用情况,增大“最大大小 (MB)”的值以防查询存储转换到只读模式 。Keep track of current space usage and increase the Max Size (MB) value to prevent Query Store from transitioning to read-only mode.

重要

没有严格执行“最大大小 (MB)”限制 。The Max Size (MB) limit isn't strictly enforced. 仅当查询存储将数据写入磁盘时才检查存储大小。Storage size is checked only when Query Store writes data to disk. 此间隔由“数据刷新间隔(分钟)”选项设置 。This interval is set by the Data Flush Interval (Minutes) option. 如果查询存储已违反存储大小检查之间的最大大小限制,则它将转换为只读模式。If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode. 如果启用了“基于大小的清理模式”,则也会触发强制实施最大大小限制的清理机制 。If Size Based Cleanup Mode is enabled, the cleanup mechanism to enforce the maximum size limit is also triggered.

使用 Management StudioManagement Studio 或执行以下脚本,以便获取有关 Query Store 大小的最新信息:Use Management StudioManagement Studio or execute the following script to get the latest information about Query Store size:

USE [QueryStoreDB];  
GO  
  
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason  
FROM sys.database_query_store_options;  

以下脚本将设置新的“最大大小 (MB)”值 :The following script sets a new value for Max Size (MB):

ALTER DATABASE [QueryStoreDB]  
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);  

数据刷新间隔(分钟) :它定义将收集的运行时统计信息保存到磁盘的频率。Data Flush Interval (Minutes): It defines the frequency to persist collected runtime statistics to disk. 它在图形用户界面 (GUI) 中以分钟为单位,但在 Transact-SQLTransact-SQL 中以秒为单位。It's expressed in minutes in the graphical user interface (GUI), but in Transact-SQLTransact-SQL it's expressed in seconds. 默认值为 900 秒,在图形用户界面中为 15 分钟。The default is 900 seconds, which is 15 minutes in the graphical user interface. 如果工作负载不生成大量不同的查询和计划或者你能够接受在数据库关闭之前花更长的时间来保留数据,可考虑使用更大的值。Consider using a higher value if your workload doesn't generate a large number of different queries and plans, or if you can withstand longer time to persist data before a database shutdown.

备注

如果出现故障转移或关闭命令,使用跟踪标志 7745 会阻止查询存储数据写入磁盘。Using trace flag 7745 prevents Query Store data from being written to disk in case of a failover or shutdown command. 有关详情,请查阅在任务关键型服务器上使用跟踪标志改善灾难恢复部分。For more information, see the Use trace flags on mission-critical servers to improve recovery from disaster section.

使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL 为“数据刷新间隔”设置不同的值 :Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Data Flush Interval:

ALTER DATABASE [QueryStoreDB] 
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);  

统计信息收集间隔:定义已收集的运行时统计信息的粒度级别(以分钟为单位)。Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. 默认值为 60 分钟。The default is 60 minutes. 如果需要更细的粒度或更短的时间来检测和缓解问题,可考虑使用较小的值。Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. 请记住,该值会直接影响查询存储数据的大小。Keep in mind that the value directly affects the size of Query Store data. 使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL 为“统计信息收集间隔”设置不同的值 :Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Statistics Collection Interval:

ALTER DATABASE [QueryStoreDB] 
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);  

过时查询阈值(天) :基于时间的清除策略,用于控制持久化运行时统计信息和非活动查询的保持期(以天为单位)。Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. 查询存储默认配置为将数据保留 30 天,这对于你的方案来说可能过长。By default, Query Store is configured to keep the data for 30 days, which might be unnecessarily long for your scenario.

避免保留你并不打算使用的历史数据。Avoid keeping historical data that you don't plan to use. 这样可以减少变为只读状态的次数。This practice reduces changes to read-only status. 查询存储数据的大小以及检测和解决问题的时间将会变得更可预测。The size of Query Store data and the time to detect and mitigate the issue will be more predictable. 使用 Management StudioManagement Studio 或以下脚本配置基于时间的清理策略:Use Management StudioManagement Studio or the following script to configure time-based cleanup policy:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));  

基于大小的清除模式:指定在查询存储数据大小达到限制时,是否启用自动数据清理功能。Size Based Cleanup Mode: Specifies whether automatic data cleanup takes place when Query Store data size approaches the limit. 请激活基于大小的清理功能,确保查询存储始终以读写模式运行并收集最新数据。Activate size-based cleanup to make sure that Query Store always runs in read-write mode and collects the latest data.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);  

查询存储捕获模式:指定查询存储的查询捕获策略。Query Store Capture Mode: Specifies the query capture policy for Query Store.

  • 全部:捕获所有查询。All: Captures all queries. 此选项在 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中为默认值。This option is the default in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
  • AUTO:忽略不太频繁的查询以及编译和执行持续时间不长的查询。Auto: Infrequent queries and queries with insignificant compile and execution duration are ignored. 执行计数、编译和运行时持续时间的阈值由内部决定。Thresholds for execution count, compile, and runtime duration are internally determined. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,这是默认选项。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default option.
  • :查询存储停止捕获新查询。None: Query Store stops capturing new queries.
  • 自定义:支持额外控件和微调数据收集策略功能。Custom: Allows additional control and the capability to fine-tune the data collection policy. 新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。The new custom settings define what happens during the internal capture policy time threshold. 这是评估配置条件的时间边界,如果所有值为 true,则查询存储可以捕获查询。This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store.

以下脚本将 QUERY_CAPTURE_MODE 设置为 AUTO:The following script sets QUERY_CAPTURE_MODE to AUTO:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);  

示例Examples

以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO,并在 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中设置其他建议选项:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2016 (13.x)SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO,并在 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中设置其他建议选项以包括等待统计信息:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2017 (14.x)SQL Server 2017 (14.x) to include wait statistics:

ALTER DATABASE [QueryStoreDB] 
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE, 
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000, 
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO, 
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
    );

下面的示例将 QUERY_CAPTURE_MODE 设置为 AUTO,并在 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中设置其他建议选项,同时视需要使用默认值设置 CUSTOM 捕获策略,而不是使用新的默认 AUTO 捕获模式 :The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2019 (15.x)SQL Server 2019 (15.x), and optionally sets the CUSTOM capture policy with its defaults, instead of the new default AUTO capture mode:

ALTER DATABASE [QueryStoreDB]  
SET QUERY_STORE = ON 
    (
      OPERATION_MODE = READ_WRITE, 
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000, 
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO, 
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100 
      )
    );

开始进行查询性能故障排除Start with query performance troubleshooting

查询存储工作流的故障排除很简单,如下图所示:The troubleshooting workflow with Query Store is simple, as shown in the following diagram:

查询存储排除故障Query Store troubleshooting

按上一节的说明通过 Management StudioManagement Studio 来启用查询存储,或者执行以下 Transact-SQLTransact-SQL 语句:Enable Query Store by using Management StudioManagement Studio, as described in the previous section, or execute the following Transact-SQLTransact-SQL statement:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;  

查询存储收集能够准确代表工作负载的数据集需要一定的时间。It takes some time until Query Store collects the data set that accurately represents your workload. 通常情况下,即使是很复杂的工作负荷,一天的时间也足够了。Usually, one day is enough even for very complex workloads. 但是,在启用此功能后,就可以立即开始浏览数据并确定需要注意的查询。However, you can start exploring the data and identify queries that need your attention immediately after you enable the feature. 前往 Management StudioManagement Studio 的对象资源管理器中数据库节点下的查询存储子文件夹,然后打开特定方案的故障排除视图。Go to the Query Store subfolder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.

Management StudioManagement Studio 查询存储视图在操作时使用一组执行度量值,每个度量值都表示为下述任意统计函数:Query Store views operate with the set of execution metrics, each expressed as any of the following statistic functions:

SQL ServerSQL Server 版本version 执行度量值Execution metric 统计函数Statistic function
SQL Server 2016 (13.x)SQL Server 2016 (13.x) CPU 时间、持续时间、执行计数、逻辑读取次数、逻辑写入次数、内存消耗、物理读取次数、CLR 时间、并行度 (DOP) 和行计数CPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism (DOP), and Row count 平均值、最大值、最小值、标准偏差、总数Average, Maximum, Minimum, Standard Deviation, Total
SQL Server 2017 (14.x)SQL Server 2017 (14.x) CPU 时间、持续时间、执行计数、逻辑读取次数、逻辑写入次数、内存消耗、物理读取次数、CLR 时间、并行度、行计数、日志内存、TempDB 内存和等待时间CPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism, Row count, Log memory, TempDB memory, and Wait times 平均值、最大值、最小值、标准偏差、总数Average, Maximum, Minimum, Standard Deviation, Total

下图显示了如何查找 Query Store 视图:The following graphic shows how to locate Query Store views:

查询存储视图Query Store views

下表说明了何时使用每个 Query Store 视图:The following table explains when to use each of the Query Store views:

SQL Server Management StudioSQL Server Management Studio view 应用场景Scenario
回归查询Regressed Queries 查明哪些查询的执行度量值最近进行了回归(例如,变得更糟)。Pinpoint queries for which execution metrics have recently regressed (for example, changed to worse).
使用此视图将应用程序中观察到的性能问题与需要进行修复或改进的实际查询关联起来。Use this view to correlate observed performance problems in your application with the actual queries that need to be fixed or improved.
总体资源消耗Overall Resource Consumption 针对任意执行度量值分析数据库的总资源消耗量。Analyze the total resource consumption for the database for any of the execution metrics.
使用此视图可以确定资源模式(白天工作负荷与夜间工作负荷的比较),并优化数据库的总体消耗。Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.
资源使用排名靠前的查询Top Resource Consuming Queries 选择所关注的执行度量值,确定在指定的时间间隔内具有最极端值的查询。Choose an execution metric of interest, and identify queries that had the most extreme values for a provided time interval.
此视图可以帮助你关注最相关的查询,这些查询对数据库资源消耗的影响最大。Use this view to focus your attention on the most relevant queries that have the biggest impact to database resource consumption.
具有强制计划的查询Queries With Forced Plans 使用查询存储列出以前的强制计划。Lists previously forced plans using Query Store.
使用此视图快速访问当前的所有强制计划。Use this view to quickly access all currently forced plans.
变化程度高的查询Queries With High Variation 分析执行变化程度较高的查询,此类变化可涉及任何可用的维度,例如所需时间间隔内的持续时间、CPU 时间、IO 和内存使用情况。Analyze queries with high-execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage, in the desired time interval.
使用此视图可以标识性能有很大差异且可能会影响用户跨应用程序体验的查询。Use this view to identify queries with widely variant performance that can be affecting user experience across your applications.
查询等待统计信息Query Wait Statistics 分析数据库中最活跃的等待类别和对所选等待类别贡献最大的查询。Analyze wait categories that are most active in a database and which queries contribute most to the selected wait category.
使用此视图分析等待统计信息并识别可能在应用程序中影响用户体验的查询。Use this view to analyze wait statistics and identify queries that might be affecting user experience across your applications.

适用范围:从 SQL Server Management StudioSQL Server Management Studio v18.0 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始。Applies to: Starting with SQL Server Management StudioSQL Server Management Studio v18.0 and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
跟踪的查询Tracked Queries 实时跟踪最重要查询的执行情况。Track the execution of the most important queries in real time. 通常情况下,使用此视图是因为你计划强制执行相关查询,因此需确保查询性能的稳定性。Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.

提示

如需详细了解如何使用 Management StudioManagement Studio 来确定资源使用排名靠前的查询并修复那些因计划选择变化而导致回归的查询,请参阅 Azure 博客中的查询存储。For a detailed description of how to use Management StudioManagement Studio to identify the top resource-consuming queries and fix those that regressed due to the change of a plan choice, see Query Store Azure Blogs.

如果确定某个查询的性能不够理想,则可根据问题性质进行操作。When you identify a query with suboptimal performance, your action depends on the nature of the problem.

  • 如果所执行的查询具有多个计划,而最后一个计划明显不如前面的计划,则可通过计划强制机制来强制使用最佳计划。If the query was executed with multiple plans and the last plan is significantly worse than the previous plan, you can use the plan forcing mechanism to force it. SQL ServerSQL Server 尝试强制实施优化器中的计划。tries to force the plan in the optimizer. 如果计划强制实施失败,将触发 XEvent,并指示优化器正常优化。If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

    查询存储强制计划Query Store force plan

    备注

    前面的图形针对特定的查询计划会显示不同的形状,以下是可能出现的每种形状的对应含义:The previous graphic might feature different shapes for specific query plans, with the following meanings for each possible status:

    形状Shape 含义Meaning
    CircleCircle 查询已完成,这意味着常规执行成功完成。Query completed, which means that a regular execution successfully finished.
    SquareSquare 已取消,这意味着客户端发起的执行中止。Cancelled, which means that a client-initiated aborted execution.
    TriangleTriangle 失败,这意味着异常执行中止。Failed, which means that an exception aborted execution.

    此外,形状大小反映指定时间间隔内的查询执行计数。Also, the size of the shape reflects the query execution count within the specified time interval. 如果执行次数较多,该形状会变大。The size increases with a higher number of executions.

  • 你可以认为,你的查询因为缺少索引而无法达到最佳执行效果。You might conclude that your query is missing an index for optimal execution. 此信息显示在查询执行计划中。This information is surfaced within the query execution plan. 使用查询存储创建缺失的索引并检查查询性能。Create the missing index, and check the query performance by using Query Store.

    查询存储显示计划Query Store show plan

如果你在 SQL 数据库SQL Database上运行工作负荷,可注册获取 SQL 数据库SQL Database 索引顾问,然后即可自动接收索引建议。If you run your workload on SQL 数据库SQL Database, sign up for SQL 数据库SQL Database Index Advisor to automatically receive index recommendations.

  • 在某些情况下,如果你看到执行计划中估计的行数和实际的行数存在显著差异,则可强制执行统计信息的重新编译。In some cases, you might enforce statistic recompilation if you see that the difference between the estimated and the actual number of rows in the execution plan is significant.
  • 重写有问题的查询,例如可以充分利用查询参数化或实现更优化的逻辑。Rewrite problematic queries, for example, to take advantage of query parameterization or to implement more optimal logic.

确保查询存储持续收集查询数据Verify that Query Store collects query data continuously

查询存储可在无提示的情况下更改操作模式。Query Store can silently change the operation mode. 请定期监视查询存储的状态以确保查询存储正常运行,并采取相应措施,避免发生不必要的故障。Regularly monitor the state of Query Store to ensure that Query Store is operating, and to take action to avoid failures due to preventable causes. 执行以下查询,以便确定操作模式并查看最相关的参数:Execute the following query to determine the operation mode and view the most relevant parameters:

USE [QueryStoreDB];  
GO  
  
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

actual_state_descdesired_state_desc 之间存在差异,这表明自动更改了操作模式。The difference between the actual_state_desc and desired_state_desc indicates that a change of the operation mode occurred automatically. 最常见的更改是查询存储在无提示的情况下切换到只读模式。The most common change is for Query Store to silently switch to read-only mode. 在极罕见的情况下,查询存储可能会因内部错误而导致处于错误状态。In extremely rare circumstances, Query Store can end up in the ERROR state because of internal errors.

当实际状态为只读时,可使用 readonly_reason 列来确定根本原因。When the actual state is read-only, use the readonly_reason column to determine the root cause. 通常情况下,你会发现,查询存储转换为只读模式是因为超出了大小配额。Typically, you find that Query Store transitioned to read-only mode because the size quota was exceeded. 这种情况下,readonly_reason 设置为 65536。In that case, the readonly_reason is set to 65536. 有关其他原因,请参阅 sys.database_query_store_options (Transact-SQL)For other reasons, see sys.database_query_store_options (Transact-SQL).

考虑执行以下步骤将 Query Store 切换为读写模式并激活数据收集功能:Consider the following steps to switch Query Store to read-write mode and activate data collection:

  • 使用 ALTER DATABASEMAX_STORAGE_SIZE_MB选项增大最大存储大小。Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.

  • 使用以下语句清理 Query Store 数据:Clean up Query Store data by using the following statement:

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;  
    

在应用这两项或其中一项步骤时,可以执行以下语句,通过显式方式将操作模式改回为读写:You can apply one or both of these steps by executing the following statement that explicitly changes the operation mode back to read-write:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);  

采用以下前摄性步骤:Take the following steps to be proactive:

  • 遵循最佳实践规范即可避免在无提示情况下更改操作模式。You can prevent silent changes of operation mode by applying best practices. 如果可以确保查询存储大小始终小于最大允许值,则会极大地降低转换为只读模式的几率。Ensure that Query Store size is always below the maximally allowed value to dramatically reduce a chance of transitioning to read-only mode. 根据配置查询存储部分所述,可激活基于大小的策略,使查询存储在大小接近极限时自动清除数据。Activate size-based policy as described in the Configure Query Store section so that Query Store automatically cleans data when the size approaches the limit.
  • 为了确保最新的数据能够得到保留,可将基于时间的策略配置为定期删除过时信息。To make sure that most recent data is retained, configure time-based policy to remove stale information regularly.
  • 最后,请考虑将“查询存储捕获模式”设置为“Auto”,因为这样通常可以筛选掉与工作负载不太相关的查询 。Finally, consider setting Query Store Capture Mode to Auto because it filters out queries that are usually less relevant for your workload.

错误状态ERROR state

若要恢复查询存储,可尝试以显式方式设置读写模式,然后再次检查实际状态。To recover Query Store, try explicitly setting the read-write mode and check the actual state again.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  
  
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

如果问题仍然存在,则表明磁盘上的查询存储数据已永久损坏。If the problem persists, it indicates that corruption of Query Store data is persisted on the disk.

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), Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure within the affected database. 必须先禁用查询存储,然后才能尝试恢复操作。Query Store must be disabled before you attempt the recovery operation. 对于 SQL Server 2016 (13.x)SQL Server 2016 (13.x),需要从查询存储中清除数据,如下所示。For SQL Server 2016 (13.x)SQL Server 2016 (13.x), you need to clear the data from Query Store as shown.

如果恢复失败,可先尝试清除查询存储,然后再设置读写模式。If the recovery was unsuccessful, you can try clearing Query Store before you set the read-write mode.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE CLEAR;  
GO  
  
ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  
  
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

设置最佳查询存储捕获模式Set the optimal Query Store Capture Mode

在 Query Store 中保留最相关数据。Keep the most relevant data in Query Store. 下表描述了每个查询存储捕获模式的典型方案:The following table describes typical scenarios for each Query Store Capture Mode:

Query Store 捕获模式Query Store Capture Mode 应用场景Scenario
全部All 对工作负载进行彻底地分析,分析所有查询的形状及其执行频率和其他统计信息。Analyze your workload thoroughly in terms of all queries' shapes and their execution frequencies and other statistics.

确定工作负荷中的新查询。Identify new queries in your workload.

检测是否使用了即席查询来确定是否有机会进行用户参数化或自动参数化。Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.

注意:这是 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中的默认捕获模式。Note: This is the default capture mode in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
AutoAuto 关注相关且可操作的查询。Focus your attention on relevant and actionable queries. 例如,那些定期执行的查询或资源消耗很大的查询。An example is those queries that execute regularly or that have significant resource consumption.

注意:从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,这是默认捕获模式。Note: Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default capture mode.
None 你已经捕获了需要在运行时监视的查询集,因此需消除其他查询可能会带来的干扰。You've already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries might introduce.

“无”适用于测试和基准测试环境。None is suitable for testing and benchmarking environments.

“无”也适用于需要提供已配置的 Query Store 配置来监视其应用程序工作负荷的软件供应商。None is also appropriate for software vendors who ship Query Store configuration configured to monitor their application workload.

在使用“无”时应格外小心,因为可能无法跟踪和优化重要的新查询。None should be used with caution because you might miss the opportunity to track and optimize important new queries. 避免使用“无”,除非你的特定方案需要使用它。Avoid using None unless you have a specific scenario that requires it.
自定义Custom SQL Server 2019 (15.x)SQL Server 2019 (15.x) ALTER DATABASE SET QUERY_STORE 命令下引入自定义捕获模式。introduces a Custom capture mode under the ALTER DATABASE SET QUERY_STORE command. 启用后,在新的“查询存储捕获策略”设置下有额外可用的查询存储配置,可用于微调特定服务器中的数据收集。When enabled, additional Query Store configurations are available under a new Query Store capture policy setting to fine-tune data collection in a specific server.

新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。The new custom settings define what happens during the internal capture policy time threshold. 这是评估配置条件的时间边界,如果所有值为 true,则查询存储可以捕获查询。This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store. 有关详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information, see ALTER DATABASE SET Options (Transact-SQL).

备注

当查询存储捕获模式设置为“全部”、“自动”或“自定义”时,始终捕获游标、存储过程中的查询和本机编译的查询 。Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. 若要捕获本机编译的查询,请使用 sys.sp_xtp_control_query_exec_stats 启用每个查询统计信息的收集。To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

在 Query Store 中保留最相关数据Keep the most relevant data in Query Store

将查询存储配置为只包含最相关的数据,这样在持续运行的时候对常规工作负载的影响最小,方便进行故障排除。Configure Query Store to contain only the relevant data so that it runs continuously and provides a great troubleshooting experience with a minimal impact on your regular workload.
下表提供最佳实践:The following table provides best practices:

最佳实践Best practice 设置Setting
对保留的历史数据进行限制。Limit retained historical data. 配置基于时间的策略以激活自动清理功能。Configure time-based policy to activate autocleanup.
筛选掉不相关的查询。Filter out nonrelevant queries. 将“查询存储捕获模式”配置为“自动” 。Configure Query Store Capture Mode to Auto.
达到最大大小时,删除不太相关的查询。Delete less relevant queries when the maximum size is reached. 激活基于大小的清理策略。Activate size-based cleanup policy.

避免使用非参数化查询Avoid using non-parameterized queries

在不必要的情况下使用非参数化查询不是最佳做法。Using non-parameterized queries when that isn't necessary isn't a best practice. 临时分析就是一个示例。An example is in the case of ad-hoc analysis. 不能重复使用缓存的计划,因为这会强制查询优化器在碰到每个唯一的查询文本时都进行查询编译。Cached plans can't be reused, which forces Query Optimizer to compile queries for every unique query text. 有关详细信息,请参阅强制参数化使用指南For more information, see Guidelines for using forced parameterization.

此外,查询存储可能会很快超过大小配额,因为可能会存在大量不同的查询文本,导致存在大量不同但具有相同形状的执行计划。Also, Query Store can rapidly exceed the size quota because of a potentially large number of different query texts and consequently a large number of different execution plans with similar shape. 结果就是,工作负载性能无法优化,查询存储可能会切换为只读模式,或者可能会不断删除数据以应对传入的查询。As a result, performance of your workload is suboptimal, and Query Store might switch to read-only mode or constantly delete data to try to keep up with the incoming queries.

请考虑以下选项:Consider the following options:

  • 在适用时进行参数化查询。Parameterize queries where applicable. 例如,在存储过程或 sp_executesql 中包装查询。For example, wrap queries inside a stored procedure or sp_executesql. 有关详细信息,请参阅参数和执行计划重用For more information, see Parameters and execution plan reuse.
  • 如果工作负载包含许多一次性使用的临时批处理且查询计划各不相同,请使用针对临时工作负载进行优化选项。Use the optimize for ad hoc workloads option if your workload contains many single-use ad-hoc batches with different query plans.
    • 将不同 query_hash 值的数目与 sys.query_store_query 中项的总数进行比较。Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. 如果该比率接近 1,则说明临时工作负载生成了不同的查询。If the ratio is close to 1, your ad-hoc workload generates different queries.
  • 如果不同查询计划的数量不多,请对数据库或部分查询应用强制参数化Apply forced parameterization for the database or for a subset of queries if the number of different query plans isn't large.
    • 请参阅计划指南,仅对选定查询强制执行参数化操作。Use a plan guide to force parameterization only for the selected query.
    • 如果工作负载中不同查询计划的数目很小,则使用 parameterization database option 命令配置强制的参数化操作。Configure forced parameterization by using the parameterization database option command, if there are a small number of different query plans in your workload. 例如,当不同 query_hash 的计数与 sys.query_store_query 中项的总数之比远小于 1 时。An example is when the ratio between the count of distinct query_hash and the total number of entries in sys.query_store_query is much less than 1.
  • 将 QUERY_CAPTURE_MODE 设置为 AUTO 即可自动筛选掉资源消耗小的即席查询。Set QUERY_CAPTURE_MODE to AUTO to automatically filter out ad-hoc queries with small resource consumption.

避免对包含对象使用 DROP 和 CREATE 模式Avoid a DROP and CREATE pattern for containing objects

查询存储会将查询条目与包含对象(例如存储过程、函数和触发器)相关联。Query Store associates query entry with a containing object, such as stored procedure, function, and trigger. 重新创建包含对象时,会针对同一查询文本生成新的查询条目。When you re-create a containing object, a new query entry is generated for the same query text. 这会阻止你跟踪该查询在一定时段内的性能统计信息,并会使用计划强制机制。This prevents you from tracking performance statistics for that query over time and using a plan forcing mechanism. 若要避免这种情况,请尽可能使用 ALTER <object> 过程来更改包含对象定义。To avoid this situation, use the ALTER <object> process to change a containing object definition whenever it's possible.

定期检查强制计划的状态Check the status of forced plans regularly

可以方便地使用计划强制机制来修复关键查询的性能问题,使这些查询的结果更可预测。Plan forcing is a convenient mechanism to fix performance for the critical queries and make them more predictable. 与计划提示和计划指南一样,强制实施某项计划并不能确保在今后的执行过程中会用到它。As with plan hints and plan guides, forcing a plan isn't a guarantee that it will be used in future executions. 通常情况下,如果对数据库架构的更改导致执行计划所引用的对象被更改或删除,计划强制就会失败。Typically, when database schema changes in a way that objects referenced by the execution plan are altered or dropped, plan forcing starts failing. 在这种情况下,SQL ServerSQL Server 会回退到重新编译查询,而强制失败的实际原因则显示在 sys.query_store_plan 中。In that case, SQL ServerSQL Server falls back to query recompilation while the actual forcing failure reason is surfaced in sys.query_store_plan. 以下查询返回强制计划的相关信息:The following query returns information about forced plans:

USE [QueryStoreDB];  
GO  
  
SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,  
    force_failure_count, last_force_failure_reason_desc  
FROM sys.query_store_plan AS p  
JOIN sys.query_store_query AS q on p.query_id = q.query_id  
WHERE is_forced_plan = 1;  

有关原因的完整列表,请参阅 sys.query_store_planFor a full list of reasons, see sys.query_store_plan. 你还可以使用 query_store_plan_forcing_failed XEvent 来跟踪和故障排除计划强制失败情况。You can also use the query_store_plan_forcing_failed XEvent to track and troubleshoot plan forcing failures.

避免在使用强制计划执行查询时重命名数据库Avoid renaming databases for queries with forced plans

执行计划使用由三个部分组成的名称(例如 database.schema.object)来引用对象。Execution plans reference objects by using three-part names like database.schema.object.

如果重命名数据库,计划强制就会失败,导致在执行所有后续的查询时都需要重新编译。If you rename a database, plan forcing fails, which causes recompilation in all subsequent query executions.

在任务关键型服务器上使用跟踪标志Use trace flags on mission-critical servers

全局跟踪标志 7745 和 7752 可用于使用查询存储来提高数据库的可用性。The global trace flags 7745 and 7752 can be used to improve availability of databases by using Query Store. 有关更多信息,请参见跟踪标记For more information, see Trace flags.

  • 跟踪标志 7745 会阻止以下默认行为:在可关闭 SQL ServerSQL Server 之前,查询存储将数据写入磁盘。Trace flag 7745 prevents the default behavior where Query Store writes data to disk before SQL ServerSQL Server can be shut down. 这意味着在 DATA_FLUSH_INTERVAL_SECONDS 定义的时间窗口之前,已收集但尚未保留到磁盘的查询存储数据将会丢失。This means that Query Store data that has been collected but not yet persisted to disk will be lost, up to the time window defined with DATA_FLUSH_INTERVAL_SECONDS.

  • 跟踪标志 7752 启用了查询存储的异步加载。Trace flag 7752 enables asynchronous load of Query Store. 这会使数据库变为联机状态,并且在查询存储完全恢复之前执行查询。This allows a database to become online and queries to be executed before Query Store has been fully recovered. 默认行为是同步加载查询存储。The default behavior is to do a synchronous load of Query Store. 默认行为可在恢复查询存储之前防止执行查询,但同时也可在数据集合中防止遗漏任何查询。The default behavior prevents queries from executing before Query Store has been recovered but also prevents any queries from being missed in the data collection.

    备注

    SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,此行为由引擎控制,跟踪标志 7752 不再有效。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this behavior is controlled by the engine, and trace flag 7752 has no effect.

    重要

    如果仅对 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中的实时工作负载见解使用查询存储,请尽快安装 KB 4340759 中的性能可伸缩性修补程序。If you're using Query Store for just-in-time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

另请参阅See also