Query Store 最佳实践Best Practice with the Query Store

适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

本文概述使用查询存储处理工作负载的最佳实践。This article outlines the best practices for using the 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 set of user interfaces designed for configuring Query Store as well as 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 refer to Query Store @Azure Blogs.

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

如果你在 SQL 数据库SQL Database 中运行 Query Store,则可使用 Query Performance Insight 来分析一定时段内的 DTU 消耗情况。If you run Query Store in 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 (CPU, memory, I/O, etc.), 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.

将 Query Store 与弹性池数据库配合使用Using Query Store with Elastic Pool Databases

可以毫无顾忌地在所有数据库中使用 Query Store,甚至是在密集打包的池中。You can use Query Store in all databases without concerns, in even densely packed pools. 与过多资源使用(为弹性池中的大量数据库启用了 Query Store 时可能会遇到这种情况)相关的所有问题都已得到了解决。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-propertiesquery-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 will take inside your database. 这是最重要的设置,直接影响 Query Store 的操作模式。This is the most important setting that directly affects operation mode of the Query Store.

当 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.

如果你的工作负荷会生成大量不同的查询和计划,或者你想要让查询历史记录保存较长的时间,则默认值 (100 MB) 可能不够大。The default value (100 MB) may not be sufficient if your workload generates large number of different queries and plans or if you want to keep query history for a longer period of time. 跟踪当前的空间使用情况,增大“最大大小(MB)”以防 Query Store 转换到只读模式。Keep track of current space usage and increase the Max Size (MB) to prevent Query Store from transitioning to read-only mode. 使用 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 Max Size (MB):

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

数据刷新间隔: 定义将收集的运行时统计信息保存到磁盘的频率(以秒为单位,默认为 900 秒,即 15 分钟)。Data Flush Interval: Defines frequency in seconds to persist collected runtime statistics to disk (the default is 900 seconds, which is 15 minutes). 如果工作负荷不生成大量不同的查询和计划或者你能够在数据库关闭之前撑住更长时间来保留数据,请考虑使用更高的值。Consider using higher value if your workload does not generates 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 will prevent Query Store data from being written to disk in case of a failover or shutdown command. 请查阅在任务关键型服务器上使用跟踪标志改善灾难恢复部分,了解详情。See the Use trace flags on mission critical servers to improve recovery from disaster section for more detail.

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

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

统计信息收集间隔: 定义已收集的运行时统计信息的粒度级别(默认值为 60 分钟)。Statistics Collection Interval: Defines level of granularity for the collected runtime statistic (the default is 60 minutes). 如果你需要更细的粒度或更短的时间来检测问题和解决问题,则可考虑使用更低的值,但请记住,这会直接影响 Query Store 数据的大小。Consider using lower value if you require finer granularity or less time to detect and mitigate issues but keep in mind that it will directly affect 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 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.
默认情况下,查询存储配置为将数据保留 30 天,这对于你的方案来说可能过长了。By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

避免保留你不计划使用的历史数据。Avoid keeping historical data that you do not plan to use. 这样可以减少变为只读状态的次数。This will reduce changes to read-only status. Query Store 数据的大小以及检测问题和解决问题的时间将会变得更可预测。The size of Query Store data as well as 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));  

基于大小的清除模式: 指定在 Query Store 数据大小达到限制时,是否启用自动数据清理功能。Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.

强烈建议你激活基于大小的清理功能,确保 Query Store 始终以读写模式运行并收集最新数据。It is strongly recommended to activate size-based cleanup to makes 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 the Query Store.

  • All - 捕获所有查询。All - Captures all queries. 这是默认选项。This is the default option.

  • 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.

  • None - 查询存储停止捕获新查询。None - Query Store stops capturing new queries.

以下脚本将“查询捕获模式”设置为“Auto”:The following script sets the Query Capture mode to Auto:

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

如何开始进行查询性能故障排除How to start with query performance troubleshooting

Query Store 工作流的故障排除很简单,如下图所示:Troubleshooting workflow with Query Store is simple, as shown on the following diagram:

query-store-troubleshootingquery-store-troubleshooting

按上一节的说明通过 Management StudioManagement Studio 来启用 Query Store,或者执行以下 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;  

Query Store 收集能够准确代表工作负荷的数据集需要一定的时间。It will take 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 enabled the feature.
导航到 Management StudioManagement Studio 的对象资源管理器中数据库节点下的 Query Store 子文件夹,以便打开特定方案的故障排除视图。Navigate to the Query Store sub-folder 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 时间、并行度 (DOP)、行计数、日志内存、TempDB 内存和等待时间CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads, CLR time, Degree of Parallelism (DOP), 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:

SSMS 视图SSMS view 应用场景Scenario
回归查询Regressed Queries 查明哪些查询的执行度量值最近进行了回归(即变得更糟)。Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse).
使用此视图将应用程序中观察到的性能问题与需要进行修复或改进的实际查询关联起来。Use this view to correlate observed performance problems in your application with the actual queries that needs 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 which 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 impacting 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 may be impacting 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 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 sub-optimal 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 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-planquery-store-force-plan

备注

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

|形状Shape|含义Meaning|
|-------------------|-------------| |CircleCircle|查询完成(常规执行成功完成)Query Completed (Regular Execution successfully finished)| |SquareSquare|取消(客户端发起的执行中止)Cancelled (Client initiated aborted execution)| |TriangleTriangle|失败(由异常引起的执行中止)Failed (Exception aborted execution)| 此外,形状大小反映指定时间间隔内的查询执行计数,执行数量较大时形状也会随之增大。Also, the size of the shape reflects query execution count within the specified time interval, increasing in size with a higher number of executions.

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

    query-store-show-planquery-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 may 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 advantages of query parameterization or to implement more optimal logic.

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

Query Store 可通过无提示方式更改操作模式。Query Store can silently change operations mode. 你应该定期监视 Query Store 的状态以确保 Query Store 正常运行,并采取相应措施,避免那些本来可以避免的因素造成故障。You should regularly monitor the state of the Query Store to ensure that the 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 operations mode occurred automatically. 最常见的更改是 Query Store 以无提示方式切换到只读模式。The most common change is for the Query Store to silently switch to read-only mode. 在极罕见的情况下,Query Store 最终可能会因内部错误而处于 ERROR 状态。In extremely rarely 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. 通常情况下,你会发现,Query Store 转换为只读模式是因为超出了大小配额。Typically you will 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 the executing the following statement that explicitly changes 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. 如果你可以确保 Query Store 大小始终小于最大允许值,则会极大地降低转换为只读模式的几率。If you ensure that Query Store size is always below the maximally allowed value that will dramatically reduce a chance of transitioning to read-only mode. 根据配置查询存储部分所述激活基于大小的策略,使查询存储在大小接近极限时自动清除数据。Activate size-based policy as described in the Configure Query Store section, so that the Query Store automatically cleans data when the size approaches the limit.

  • 为了确保最新的数据能够得到保留,可将基于时间的策略配置为定期删除过时信息。In order to make sure that most recent data is retained, configure time-based policy to remove stale information regularly.

  • 最后,你应该考虑将“查询捕获模式”设置为“Auto”,因为这样通常可以筛选掉与工作负荷不太相关的查询。Finally, you should consider setting Query Capture Mode to Auto as it filters out queries that are usually less relevant for your workload.

错误状态Error State

若要恢复 Query Store,可尝试显式设置读写模式,然后再次检查实际状态。To recover Query Store try explicitly setting the read-write mode and check 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 corruption of the Query Store data is persisted on the disk.

对于 SQL 2017 及更高版本,可通过在受影响的数据库内执行 sp_query_store_consistency_check 存储过程来恢复查询存储。For SQL 2017 and later, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure within the affected database. 对于 SQL 2016,需要从查询存储中清除数据,如下所示。For 2016, you will need to clear the data from the Query Store as shown below.

如果没有效果,可在请求读写模式之前尝试清除查询存储。If that did not help, you can try to clear Query Store before requesting 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 capture mode

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

查询捕获模式Query Capture Mode 应用场景Scenario
AllAll 对工作负荷进行彻底的分析,分析所有查询形状及其执行频率和其他统计信息。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.
AutoAuto 注重相关的可操作查询,即那些定期执行的查询或资源消耗很大的查询。Focus your attention on relevant and actionable queries; those queries that execute regularly or that have significant resource consumption.
NoneNone 你已经捕获了需要在运行时监视的查询集,因此需消除其他查询可能会带来的干扰。You have already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries may introduce.

“无”适用于测试和基准测试环境。None is suitable for testing and bench-marking 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 as you might miss the opportunity to track and optimize important new queries. 避免使用“无”,除非你的特定方案需要使用它。Avoid using None unless you have a specific scenario that requires it.

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

将 Query Store 配置为只包含最相关的数据,使之在持续运行的时候对你的常规工作负荷的影响最小,方便你进行故障排除。Configure the Query Store to contain only the relevant data and it will run continuously providing 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 auto-cleanup.
筛选掉不相关的查询。Filter out non-relevant queries. 将“查询捕获模式”配置为“Auto”。Configure Query Capture Mode to Auto.
达到最大大小时,删除不太相关的查询。Delete less relevant queries when maximum size is reached. 激活基于大小的清理策略。Activate size-based cleanup policy.

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

根据最佳做法,仅在绝对必要时(例如,临时分析时),才使用非参数化查询。Using non-parameterized queries when that is not absolutely necessary (for example in case of ad-hoc analysis) is not a best practice. 不能重复使用缓存的计划,因为这会强制查询优化器在碰到每个唯一的查询文本时都进行查询编译。Cached plans cannot be reused which forces Query Optimizer to compile queries for every unique query text. 有关详细信息,请参阅强制参数化使用指南For more information, see Guidelines for Using Forced Parameterization.
此外,Query Store 可能会很快超过大小配额,因为可能会存在大量不同的查询文本,结果会出现大量不同的执行计划,而查询形状是类似的。Also, Query Store can rapidly exceed the size quota because of potentially a large number of different query texts and consequently a large number of different execution plans with similar shape.
结果就是,工作负荷性能无法优化,Query Store 可能会切换为只读模式,或者可能会不断删除数据以应对传入的查询。As a result, performance of your workload will be sub-optimal and Query Store might switch to read-only mode or might be constantly deleting the data trying to keep up with the incoming queries.

请考虑下列选项:Consider following options:

  • 适用时,将查询参数化(例如,在存储过程或 sp_executesql 中包装查询)。Parameterize queries where applicable, 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 is not large.

    • 请参阅计划指南,仅对选定查询强制执行参数化操作。Use plan guide to force parameterization only for the selected query.

    • 如果工作负载中不同查询计划的数量不多(即唯一 query_hash 数量与 sys.query_store_query 中条目总数的比 1 小得多时),将强制参数化配置为使用参数化数据库选项命令。Configure forced parameterization as using the Parameterization database option command, if there are a small number of different query plans in your workload: 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.

  • 将“查询捕获模式”设置为“AUTO”即可自动筛选掉资源消耗小的即席查询 。Set the Query Capture Mode to AUTO to automatically filter out ad-hoc queries with small resource consumption.

维护查询的包含对象时,避免使用 DROP 和 CREATE 模式Avoid a DROP and CREATE pattern when maintaining containing objects for the queries

Query Store 会将查询条目与包含对象(存储过程、函数和触发器)相关联。Query Store associates query entry with a containing object (stored procedure, function, and trigger). 重新创建包含对象时,将会针对同一查询文本生成新的查询条目。When you recreate a containing object, a new query entry will be generated for the same query text. 这会阻止你跟踪该查询在一定时段内的性能统计信息,并会使用计划强制机制。This will prevent you from tracking performance statistics for that query over time and use plan forcing mechanism. 若要避免这种问题,请尽可能使用 ALTER <object> 过程来更改包含对象定义。To avoid this, use the ALTER <object> process to change a containing object definition whenever it is 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. 但与计划提示和计划指南一样,强制实施某项计划并不能确保在今后的执行过程中会用到它。However, as with plan hints and plan guides, forcing a plan is not 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 will start 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 full list of reasons, refer to 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 if you have queries with Forced Plans

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

如果重命名数据库,计划强制就会失败,导致在执行所有后续的查询时都需要重新编译。If you rename a database, plan forcing will fail which will cause 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 using Query Store. 有关详细信息,请参阅跟踪标志For more information, refer to Trace Flags.

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

  • 跟踪标志 7752 启用了查询存储的异步加载。Trace flag 7752 enables asynchronous load of Query Store. 这会使数据库变为联机状态,并且在查询存储完全恢复之前执行查询。This allows a database to become online and queries to be executed before the 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 the Query Store has been recovered but also prevents any queries from being missed in the data collection.

重要

如果仅对 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中正在运行的工作负载见解使用查询存储,请尽快安装 KB 4340759 中的性能可伸缩性修补程序。If you are 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

查询存储目录视图 (Transact-SQL) Query Store Catalog Views (Transact-SQL)
查询存储存储过程 (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
通过内存中 OLTP 使用查询存储 Using the Query Store with In-Memory OLTP
相关视图、函数和过程 Monitoring Performance By Using the Query Store
查询处理体系结构指南Query Processing Architecture Guide