Query Store 使用方案Query Store Usage Scenarios

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是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 ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是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

在需要跟踪工作负荷并确保其性能可预测的很多情况下,都可以使用 Query Store。Query Store can be used in wide set of scenarios when tracking and ensuring predictable workload performance is critical. 下面是可以考虑使用 Query Store 的一些示例:Here are some examples you can consider:

  • 找出并解决使用计划选择回归的查询Pinpoint and fix queries with plan choice regressions
  • 确定和优化排名靠前的资源占用查询Identify and tune top resource consuming queries
  • A/B 测试A/B testing
  • 升级到新版 SQL ServerSQL Server 期间保持性能稳定Keep performance stability during the upgrade to newer SQL ServerSQL Server
  • 识别并改进临时工作负载Identify and improve ad hoc workloads

找出并解决使用计划选择回归的查询Pinpoint and fix queries with plan choice regressions

在常规查询执行过程中,查询优化器可以决定是否因下述重要输入变得不同而选择不同计划:数据基数已更改,索引已创建、更改或删除,统计信息已更新,等等。大多数情况下,新计划要优于以前使用的计划,或二者的效果差不多。During its regular query execution, Query Optimizer may decide to choose a different plan because important inputs became different: data cardinality has changed, indexes have been created, altered, or dropped, statistics have been updated, etc. For the most part, the new plan is better, or about the same than the plan used previously. 但有时候,新计划的效果要差很多 - 这种情况称为计划选择更改回归。However, there are cases when new plan is significantly worse - this situation is referred to as plan choice change regression. 在查询存储出现之前,这是一个很难确定和解决的问题,因为 SQL ServerSQL Server 没有针对使用过一段时间的执行计划为用户提供可供查看的内置数据的存储。Prior to Query Store, it was an issue difficult to identify and fix as SQL ServerSQL Server did not provide built-in data store, for users to look at for execution plans that were used over time.

使用查询存储,可快速执行以下操作:With the Query Store, you can quickly:

  • 确定你关注的时间段内(过去一小时、昨天、上周等)执行指标已降级的所有查询。Identify all queries which execution metrics have been degraded, in the period of time of interest (last hour, day, week, etc.). 中使用 回归查询 SQL Server Management StudioSQL Server Management Studio 加快分析速度。Use Regressed Queries in SQL Server Management StudioSQL Server Management Studio to speed up your analysis.

  • 在回归查询中,很容易找到那些有多个计划的查询,以及由于计划选择错误而降级的查询。Among the regressed queries, it's easy to find those that had multiple plans and which degraded because of the bad plan choice. 使用“回归查询”中的“计划摘要”窗格来显示回归查询的所有计划及其在某个时间段的查询性能。Use Plan Summary pane in Regressed Queries to visualize all plans for a regressed query and their query performance over time.

  • 强制实施历史记录中的旧计划(如果该计划经证明效果更好)。Force the previous plan from the history, if it proved to be better. 使用“回归查询”中的“强制计划”按钮,强制实施针对查询选择的计划。Use Force Plan button in Regressed Queries to force selected plan for the query.

查询数据存储的屏幕截图,其中显示了计划摘要。Screenshot of the Query Store showing a plan summary.

有关方案的详细说明,请参阅 Query Store:A flight data recorder for your database(查询存储:适用于数据库的飞行数据记录器)博客。For detailed description of the scenario refer to Query Store: A flight data recorder for your database blog.

确定和优化排名靠前的资源占用查询Identify and tune top resource consuming queries

虽然你的工作负荷可能会生成数千个查询,但通常情况下,使用大部分系统资源的实际上只是其中一部分查询,因此你只需要注意这部分查询。Although your workload may generate thousands of queries, typically only a handful of them actually use the most of the system resources and therefore require your attention. 通常情况下,在资源使用排名靠前的查询中,你会发现有些查询是回归性查询,有些查询则可在进一步优化后获得性能改善。Among top resource consuming queries, you will typically find queries that are either regressed or those that can be improved with additional tuning.

开始浏览时,最方便的方式是打开 中的“资源使用排名靠前的查询”。 Management StudioManagement StudioThe easiest way to start exploration is to open Top Resource Consuming Queries in Management StudioManagement Studio. 用户界面分成三个窗格:一个直方图,代表资源使用排名靠前的查询(左);一个针对所选查询的计划摘要(右);一个针对所选计划的可视化查询计划(底部)。User interface is separated into three panes: A histogram representing top resource consuming queries (left), a plan summary for selected query (right) and visual query plan for selected plan (bottom). 单击“配置”按钮即可控制要分析的查询个数,以及要设置的时间间隔。 Click the Configure button to control how many queries you want to analyze and the time interval of interest. 此外,还可以在不同的资源消耗维度(持续时间、CPU、内存、IO、执行数)和基线(平均、最小、最大、总计、标准偏差)之间进行选择。Additionally, you can choose between different resource consumption dimensions (duration, CPU, memory, IO, number of executions) and the baseline (Average, Min, Max, Total, Standard Deviation).

查询数据存储的屏幕截图,其中显示用户可以识别和优化资源消耗较大的查询。Screenshot of the Query Store showing that you can identify and tune top resource consuming queries.

查看右侧的计划摘要,以便分析执行历史记录并了解各种不同的计划及其运行时统计信息。Look at the plan summary on the right to analyze the execution history and learn about the different plans and their runtime statistics. 使用底部窗格检查各种不同的计划,或者用肉眼对这些并排呈现的计划进行比较(使用“比较”按钮)。Use the bottom pane to examine the different plans or to compare them visually, rendered side by side (use the Compare button).

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

  1. 如果所执行的查询具有多个计划,而最后一个计划明显不如前面的计划,则可通过计划强制机制来确保 SQL ServerSQL Server 在今后执行查询时使用最佳计划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 ensure SQL ServerSQL Server will use the optimal plan for future executions

  2. 查看优化器是否建议了 XML 计划中缺失的索引。Check if the optimizer is suggesting any missing indexes in XML plan. 如果答案为是,则请创建该缺失的索引,并在创建完索引后使用 Query Store 来评估查询性能。If yes, create the missing index and use the Query Store to evaluate query performance after the index creation

  3. 确保查询使用的基础表的统计信息是最新的。Make sure that the statistics are up-to-date for the underlying tables used by the query.

  4. 确保查询所使用的索引已进行碎片整理。Make sure that indexes used by the query are defragmented.

  5. 考虑重新编写成本高的查询。Consider rewriting expensive query. 例如,可以充分利用查询参数化,减少动态 SQL 的使用。For example, take advantages of query parameterization and reduce usage of dynamic SQL. 在读取数据时实施最佳逻辑(在数据库端而非应用程序端应用数据筛选)。Implement optimal logic when read the data (apply data filtering on database side, not on application side).

A/B 测试A/B testing

在计划引入应用程序更改之前和之后,使用 Query Store 来比较工作负荷性能。Use Query Store to compare workload performance before and after the application change you plan to introduce. 在下表包含的多个示例中,你可以使用 Query Store 来评估环境或应用程序更改对工作负荷性能的影响:The following list contains several examples where you can use Query Store to assess impact of the environment or application change to the workload performance:

  • 推出新应用程序版本。Rolling out new application version.

  • 向服务器添加新硬件。Adding new hardware to the server.

  • 在消耗大量资源的查询引用的表上创建缺失的索引。Creating missing indexes on tables referenced by expensive queries.

  • 应用筛选策略以确保行级别安全性。Applying filtering policy for row-level security. 有关详细信息,请参阅 Optimizing Row Level Security with Query Store(使用查询存储优化行级别安全性)。For more information, see Optimizing Row Level Security with Query Store.

  • 将临时系统版本控制添加到由 OLTP 应用程序频繁修改的表。Adding temporal system-versioning to tables that are frequently modified by your OLTP applications.

任何此类方案都可应用以下工作流:In any of these scenarios apply the following workflow:

  1. 在进行计划的更改之前,使用 Query Store 运行工作负荷,以便生成性能基线。Run your workload with the Query Store before the planned change to generate performance baseline.

  2. 在控制的时间点应用应用程序更改。Apply application change at the controlled moment in time.

  3. 继续运行工作负荷,直至生成更改后的系统性能图。Continue running the workload long enough to generate performance image of the system after the change

  4. 对 #1 和 #3 的结果进行比较。Compare results from #1 and #3.

    1. 打开“数据库总体使用情况”以确定对整个数据库的影响。Open Overall Database Consumption to determine impact to the entire database.

    2. 打开“资源使用排名靠前的查询”(或使用 Transact-SQLTransact-SQL 运行你自己的分析),以便分析所做的更改对最重要查询的影响。Open Top Resource Consuming Queries (or run your own analysis using Transact-SQLTransact-SQL) to analyze impact of the change to the most important queries.

  5. 决定是保留所做的更改,还是在无法接受新性能的情况下进行回退。Decide whether to keep the change or perform roll back in case when new performance is unacceptable.

下图显示了如何在创建缺失索引的情况下进行 Query Store 分析(步骤 4)。The following illustration shows Query Store analysis (step 4) in case of missing index creation. 打开“资源使用排名靠前的查询”/“计划摘要”窗格,此时将显示会受索引创建操作影响的查询的该视图:Open Top Resource Consuming Queries / Plan summary pane to get this view for the query that should be impacted by the index creation:

屏幕截图显示缺少索引创建的查询数据存储分析(步骤 4)。Screenshot showing the Query Store analysis (step 4) in case of missing index creation.

此外,你还可以在索引创建前后对计划进行比较,只需将这些计划并排呈现即可。Additionally, you can compare plans before and after index creation by rendering them side by side. (“在单独的窗口中比较选定查询的计划”工具栏选项,此选项已在工具栏中使用红色正方形进行标记。)("Compare the plans for the selected query in a separate window" toolbar option, which is marked with red square on the toolbar.)

屏幕截图显示查询数据存储,以及在单独窗口工具栏选项中显示的“比较所选查询的计划”。Screenshot showing the Query Store and the Compare the plans for the selected query in a separate window toolbar option.

在创建索引之前的计划(plan_id = 1,见上)提示索引缺失,你可以通过检查发现 Clustered Index Scan 是查询中成本最高的运算符(红色矩形)。Plan before index creation (plan_id = 1, above) has missing index hint and you can inspect that Clustered Index Scan was the most expensive operator in the query (red rectangle).

在创建缺失索引之后的计划(plan_id = 15,见下)现在可以使用 Index Seek (Nonclustered) 来减少查询的总体成本并改进其性能(绿色矩形)。Plan after missing index creation (plan_id = 15, below) now has Index Seek (Nonclustered) which reduces the overall cost of the query and improves it performance (green rectangle).

根据分析,查询性能获得了提升,因此你会保留索引。Based on analysis you would likely keep the index as query performance has been improved.

升级到新版 SQL ServerSQL Server 期间保持性能稳定Keep performance stability during the upgrade to newer SQL ServerSQL Server

SQL Server 2014 (12.x)SQL Server 2014 (12.x)之前,用户在升级到最新的平台版本时要冒性能下降的风险。Prior to SQL Server 2014 (12.x)SQL Server 2014 (12.x), users were exposed to the risk of performance regression during the upgrade to the latest platform version. 之所以会出现这种情况,是因为最新版查询优化器会在新版本安装之后即时启用。The reason for that was the fact that latest version of Query Optimizer became active immediately once new bits are installed.

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 起,所有查询优化器更改都会绑定到最新的数据库兼容性级别,因此计划不会在升级后立即更改,而是在用户将 COMPATIBILITY_LEVEL 数据库更改为最新版本后更改。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) all Query Optimizer changes are tied to the latest database compatibility level, so plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL to the latest one. 利用此功能和 Query Store,你可以在升级过程中对查询性能进行精确的控制。This capability, in combination with Query Store gives you a great level of control over the query performance in the upgrade process. 建议的升级工作流如下图所示:Recommended upgrade workflow is shown in the following picture:

显示所建议的升级工作流的示意图。Diagram showing the recommended upgrade workflow.

  1. 升级 SQL ServerSQL Server 而不更改数据库兼容性级别。Upgrade SQL ServerSQL Server without changing the database compatibility level. 它不会公开最新的查询优化器更改,但仍会提供包括查询存储在内的新版 SQL ServerSQL Server 功能。It doesn't expose the latest Query Optimizer changes but still provides newer SQL ServerSQL Server features including Query Store.

  2. 启用查询存储。Enable Query Store. 有关本主题的详细信息,请参阅使查询存储适应工作负荷For more information on this topic, see Keep Query Store adjusted to your workload.

  3. 允许查询存储捕获查询和计划,并建立包含源/以前的数据库兼容性级别的性能基线。Allow Query Store to capture queries and plans, and establishes a performance baseline with the source/previous database compatibility level. 在此步骤停留足够长的时间,确保捕获所有计划并获取稳定的基线。Stay at this step long enough to capture all plans and get a stable baseline. 这可以是生产工作负荷常用业务周期的持续时间。This can be the duration of a usual business cycle for a production workload.

  4. 转到最新兼容性级别:向工作负荷显示最新的查询优化器更改,让其创建可能的新计划。Move to latest database compatibility level: get your workload exposed to the latest Query Optimizer changes and let it potentially create new plans.

  5. 使用查询存储进行分析并解决回归问题:大多数情况下,新查询优化器更改会生成更好的计划。Use Query Store for analysis and regression fixes: for the most part, the new Query Optimizer changes should produce better plans. 不过,查询存储可以让你轻松识别计划选择回归并使用计划强制机制对其进行修复。However, Query Store will provide an easy way to identify plan choice regressions and fix them using a plan forcing mechanism. SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,使用自动计划更正功能时,此步骤可自动进行。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), when using the Automatic Plan Correction feature, this step becomes automatic.

    a.a. 对于出现回归的情况,请在查询存储中强制执行之前已知的有效计划。For cases where there are regressions, force the previously known good plan in the Query Store.

    b.b. 如果存在未能强制执行的查询计划,或者如果性能仍不足,请考虑将数据库兼容级别还原到之前的设置,然后寻求 Microsoft 客户支持。If there are query plans that fail to force, or if performance is still insufficient, consider reverting the database compatibility level to the prior setting, and then engaging Microsoft Customer Support.

提示

使用 SQL Server Management StudioSQL Server Management Studio 升级数据库任务,升级数据库的数据库兼容性级别Use SQL Server Management StudioSQL Server Management Studio Upgrade Database task to upgrade the database compatibility level of the database. 有关详细信息,请参阅使用查询优化助手升级数据库See Upgrading Databases by using the Query Tuning Assistant for details.

识别并改进临时工作负载Identify and improve ad hoc workloads

某些工作负载没有可通过优化来提高应用程序整体性能的主查询。Some workloads do not have dominant queries that you can tune to improve overall application performance. 通常情况下,这些工作负荷的特点是有相对较大的不同查询,每个查询都会消耗一部分系统资源。Those workloads are typically characterized with relatively large number of different queries each of them consuming portion of system resources. 这些查询在性质上很独特,执行次数很少(通常仅执行一次,因此才称为即席查询),因此其运行时消耗并不重要。Being unique, those queries are executed very rarely (usually only once, thus name ad hoc), so their runtime consumption is not critical. 另一方面,由于应用程序总是在生成全新的查询,因此大部分系统资源都消耗在没有进行优化的查询编译上。On the other hand, given that application is generating net new queries all the time, significant portion of system resources is spent on query compilation, which is not optimal. 这对于 Query Store 来说并不是一种理想的情形,因为大量的查询和计划会占据你所保留的空间,这意味着 Query Store 可能很快就会进入只读模式。This is not ideal situation for Query Store either given that large number of queries and plans flood the space you have reserved which means that Query Store will likely end up in the read-only mode very quickly. 如果你激活了“基于大小的清除策略”(强烈建议使用它来让 Query Store 始终处于启动和运行状态),则大部分时间会由后台进程清理 Query Store 结构,这也会消耗大量系统资源。If you activated Size Based Cleanup Policy (highly recommended to keep Query Store always up and running), then background process will be cleaning Query Store structures most of the time also taking significant system resources.

你可以通过“资源使用排名靠前的查询”视图,率先了解工作负荷的即席性质:Top Resource Consuming Queries view gives you first indication of the ad hoc nature of your workload:

屏幕截图显示“资源消耗较大的查询”视图,其中显示了大多数资源消耗较大的查询只执行了一次。Screenshot of the Top Resource Consuming Queries view showing that the majority of top resources consuming queries is only executed once.

可以通过“执行计数”度量值来分析排名靠前的查询是否为即席查询(这需要使用 QUERY_CAPTURE_MODE = ALL 运行 Query Store)。Use Execution Count metric to analyze whether your top queries are ad hoc (this requires you to run Query Store with QUERY_CAPTURE_MODE = ALL). 从上图可以看出,90% 的“资源使用排名靠前的查询”仅执行一次。From the diagram above, you can see that 90% of your Top Resource Consuming Queries are executed only once.

此外,还可以通过运行 Transact-SQLTransact-SQL 脚本来获取系统中查询文本、查询和计划的总数,并可通过比较 query_hash 和 plan_hash 来确定其差异:Alternatively, you can run Transact-SQLTransact-SQL script to get total number of query texts, queries, and plans in the system and determine how different they are by comparing the query_hash and plan_hash:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

在工作负荷包含即席查询的情况下,你可能会获得这种结果:This is one potential result you can get in case of workload with ad hoc queries:

屏幕截图显示在工作负荷包含即席查询的情况下可能得到的结果。Screenshot of the potential result you can get in case of workload with ad hoc queries.

查询结果显示,尽管查询存储中查询和计划的数量很大,其 query_hash 和 query_plan_hash 并没有什么不同。Query result shows that despite the large number of queries and plans in the Query Store their query_hash and query_plan_hash are actually not different. 唯一查询文本和唯一 query hash 的比率远远大于 1,这表明工作负荷适合进行参数化,因为这些查询之间的唯一差异就是作为查询文本一部分提供的文本常数(参数)。A ratio between unique query texts and unique query hashes, which is much bigger than 1, is an indication that workload is a good candidate for parameterization, as the only difference between the queries is literal constant (parameter) provided as part of the query text.

通常,这种情况发生的条件是你的应用程序生成了查询(而不是调用存储过程或参数化查询),或者该应用程序依赖于会默认生成查询的对象关系映射框架。Usually, this situation happens if your application generates queries (instead of invoking stored procedures or parameterized queries) or if it relies on object-relational mapping frameworks that generate queries by default.

如果你可以控制应用程序代码,则可以考虑重新编写数据访问层,以便利用存储过程或参数化查询。If you are in control of the application code, you may consider rewriting of the data access layer to utilize stored procedures or parameterized queries. 不过,也可以在不更改应用程序的情况下显著改善这种状况,方法是针对整个数据库强制实施查询参数化(所有查询)或者使用同一 query_hash 针对单个查询模板来进行。However, this situation can be also significantly improved without application changes by forcing query parameterization for the entire database (all queries) or for the individual query templates with the same query_hash.

使用单个查询模板进行操作时,需要创建计划指南:Approach with individual query templates requires plan guide creation:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

使用计划指南的解决方案操作起来更精确,但需要完成更多的工作。Solution with plan guides is more precise but it requires more work.

如果所有查询(或大部分查询)都可以进行自动参数化,则针对整个数据库更改 FORCED PARAMETERIZATION 可能是一个更好的选项:If all your queries (or the majority of them) are candidates for auto-parameterization, then changing FORCED PARAMETERIZATION for the entire database may be a better option:

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

备注

有关本主题的详细信息,请参阅强制参数化使用指南For more information on this topic, see Guidelines for Using Forced Parameterization.

应用任何此类步骤之后,即可通过“资源使用排名靠前的查询”从另一个角度来了解你的工作负荷。 After you apply any of these steps, Top Resource Consuming Queries will show you different picture of your workload.

屏幕截图显示“资源消耗较大的查询”视图,其中以另一种方式显示工作负荷。Screenshot of the Top Resource Consuming Queries view showing a different picture of your workload.

某些情况下,你的应用程序可能会生成大量不同的查询,而这些查询并不适合进行自动参数化。In some cases, your application may generate lots of different queries which are not good candidates for auto-parameterization. 在这种情况下,你会看到系统中存在大量查询,但唯一查询和唯一 query_hash 之间的比率可能接近 1。In that case, you see large number of queries in the system but the ratio between unique queries and unique query_hash is likely close to 1.

在这种情况下,建议启用“针对即席工作负荷进行优化”服务器选项,防止将缓存内存浪费在不大可能再次执行的查询上。In that case, you may want to enable the Optimize for Ad hoc Workloads server option to prevent wasting cache memory on queries that won't likely be executed again. 若要防止在 Query Store 中捕获这些查询,可将 QUERY_CAPTURE_MODE 设置为 AUTOTo prevent capture of those queries in the Query Store, set QUERY_CAPTURE_MODE to AUTO.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

另请参阅See Also

相关视图、函数和过程 Monitoring Performance By Using the Query Store
查询存储最佳实践 Best Practice with the Query Store
使用查询优化助手升级数据库Upgrading Databases by using the Query Tuning Assistant