使用查询优化助手升级数据库Upgrading Databases by using the Query Tuning Assistant

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

从较低版本的 SQL ServerSQL Server 迁移到 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 或更高版本,且将数据库兼容性级别升级到最新可用级别时,工作负载可能会面临性能回归风险。When migrating from an older version of SQL ServerSQL Server to SQL Server 2014 (12.x)SQL Server 2014 (12.x) or newer, and upgrading the database compatibility level to the latest available, a workload may be exposed to the risk of performance regression. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 升级到任何较新版本时,出现此情况的可能性更小。This is also possible to a lesser degree when upgrading between SQL Server 2014 (12.x)SQL Server 2014 (12.x) and any newer version.

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 起,每个新版本的所有查询优化器更改均限制为最新的数据库兼容性级别,因此系统不会在升级后立即更改执行计划,而是在用户将 COMPATIBILITY_LEVEL 数据库选项更改为最新可用版本后更改。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and with every new version, all query optimizer changes are gated to the latest database compatibility level, so execution plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL database option to the latest available. 有关 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中引入的查询优化器更改的详细信息,请参阅基数估计器For more information on query optimizer changes introduced in SQL Server 2014 (12.x)SQL Server 2014 (12.x), see Cardinality Estimator. 有关兼容性级别以及它们对升级的影响的详细信息,请参阅兼容性级别和 SQL Server 升级For more information about compatibility levels and how they can affect upgrades, see Compatibility Levels and SQL Server Upgrades.

如果升级遵循以下建议工作流,那么将数据库兼容性级别提供的此限制功能与查询存储相结合,可在升级过程中拥有对查询性能很高的控制级别。This gating capability provided by the database compatibility level, in combination with Query Store gives you a great level of control over the query performance in the upgrade process if the upgrade follows the recommended workflow seen below. 有关用于升级兼容性级别的建议工作流的详细信息,请参阅更改数据库兼容性模式和使用查询存储For more information on the recommended workflow for upgrading the compatibility level, see Change the Database Compatibility Mode and Use the Query Store.

使用查询存储的数据库升级建议工作流Recommended database upgrade workflow using Query Store

SQL Server 2017 (14.x)SQL Server 2017 (14.x) 引入了自动优化,进一步提升了对升级的控制,并且能够自动执行上述建议工作流中的最后一步。This control over upgrades was further improved with SQL Server 2017 (14.x)SQL Server 2017 (14.x) where Automatic Tuning was introduced and allows automating the last step in the recommended workflow above.

SQL Server Management StudioSQL Server Management Studio v18 起,新的查询优化助手 (QTA) 功能将在升级到更新的 SQL ServerSQL Server 版本期间指导用户完成建议工作流以保持性能稳定,如查询存储使用方案的“在升级到新版 SQL Server 期间保持性能稳定”部分中所述 。Starting with SQL Server Management StudioSQL Server Management Studio v18, the new Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL ServerSQL Server versions, as documented in the section Keep performance stability during the upgrade to newer SQL Server of Query Store Usage Scenarios. 不过,QTA 不会回滚到以前已知的优质计划,如建议的工作流的最后一步所示。However, QTA does not roll back to a previously known good plan as seen in the last step of the recommended workflow. 相反,QTA 会跟踪在查询存储“回归查询” 视图中找到的任何回归,并遍历适用的优化器模型变体的可能排列,以便生成新的更优质计划。Instead, QTA will track any regressions found in the Query Store Regressed Queries view, and iterate through possible permutations of applicable optimizer model variations so that a new better plan can be produced.

重要

QTA 不会生成用户工作负载。QTA does not generate user workload. 如果在应用程序未使用的环境中运行 QTA,请确保可以通过其他方式在目标 SQL Server 数据库引擎SQL Server Database Engine 上执行代表性测试工作负载。If running QTA in an environment that is not used by your aplications, ensure that you can still execute representative test workload on the targeted SQL Server 数据库引擎SQL Server Database Engine by other means.

查询优化助手工作流The Query Tuning Assistant workflow

QTA 的起点假设将数据库从以前版本的 SQL ServerSQL Server(通过 CREATE DATABASE ...FOR ATTACHRESTORE)移动到更新版本的 SQL Server 数据库引擎SQL Server Database Engine,并且升级前的数据库兼容性级别不会立即更改。The starting point of QTA assumes that a database from a previous version of SQL ServerSQL Server is moved (through CREATE DATABASE ... FOR ATTACH or RESTORE) to a newer version of the SQL Server 数据库引擎SQL Server Database Engine, and the before-upgrade database compatibility level is not changed immediately. QTA 将引导用户完成以下步骤:QTA will guide through the following steps:

  1. 根据用户设置的工作负载持续时间(以天为单位)的建议设置来配置查询存储。Configure Query Store according to recommended settings for the workload duration (in days) set by the user. 考虑与典型业务周期匹配的工作负载持续时间。Think about the workload duration that matches your typical business cycle.
  2. 请求启动所需的工作负载,以便查询存储可以收集工作负载数据的基线(若尚未提供)。Request to start the required workload, so that Query Store can collect a baseline of workload data (if none available yet).
  3. 升级到用户所选的目标数据库兼容性级别。Upgrade to the target database compatibility level chosen by the user.
  4. 请求收集第 2 次传递的工作负载数据,用于进行比较和回归检测。Request that a second pass of workload data is collected for comparison and regression detection.
  5. 循环访问根据查询存储回归的查询视图找到的任何回归,通过收集有关适用优化器模型变体的可能排列的运行时统计信息进行试验,并测量结果 。Iterate through any regressions found based on Query Store Regressed Queries view, experiment by collecting runtime statistics on possible permutations of applicable optimizer model variations, and measure the outcome.
  6. 报告测量到的改进,并且可选择允许使用计划指南保留那些更改。Report on the measured improvements, and optionally allow those changes to be persisted using plan guides.

有关附加数据库的详细信息,请参阅数据库分离和附加For more information on attaching a database, see Database Detach and Attach.

请参阅下文,了解 QTA 实质上如何使用上文所述的查询存储,只更改升级兼容性级别建议工作流的最后几个步骤。See below how QTA essentially only changes the last steps of the recommended workflow for upgrading the compatibility level using Query Store seen above. QTA 专为选定的回归查询提供了优化选择,用于使用优化的执行计划新建改进状态;而不是提供选项,供用户选择是使用当前低效的执行计划,还是使用最后一个已知的优质执行计划。Instead of having the option to choose between the currently inefficient execution plan and the last known good execution plan, QTA presents tuning options that are specific for the selected regressed queries, to create a new improved state with tuned execution plans.

使用 QTA 的数据库升级建议工作流Recommended database upgrade workflow using QTA

QTA 优化内部搜索空间QTA Tuning internal search space

QTA 仅面向可以从查询存储中执行的 SELECT 查询。QTA targets only SELECT queries that can be executed from Query Store. 若编译参数是已知的,那么参数化查询符合条件。Parameterized queries are eligible if the compiled parameter is known. 此时,依赖于运行时构造(如临时表或表变量)的查询不符合条件。Queries that depend on runtime constructs such as temporary tables or table variables are not eligible at this time.

QTA 面向 基数估计器 (CE) 版本更改带来的查询回归已知可能的模式。QTA targets known possible patterns of query regressions due to changes in Cardinality Estimator (CE) versions. 例如,将数据库从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和数据库兼容性级别 110 升级到 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 和数据库兼容性级别 140 时,一些查询可能发生回归,因为它们是专为适用于 SQL Server 2012 (11.x)SQL Server 2012 (11.x) (CE 70) 中存在的 CE 版本而设计的。For example, when upgrading a database from SQL Server 2012 (11.x)SQL Server 2012 (11.x) and database compatibility level 110, to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and database compatibility level 140, some queries may regress because they were designed specifically to work with the CE version that existed in SQL Server 2012 (11.x)SQL Server 2012 (11.x) (CE 70). 这并不意味着从 CE 140 还原为 CE 70 是唯一选择。This does not mean that reverting from CE 140 to CE 70 is the only option. 只要较新版本中的某项特定更改引入了回归,就有可能提示查询只使用上一个 CE 版本中对特定查询更有效的相关部分,同时依然利用 CE 较新版本的所有其他改进。If only a specific change in the newer version is introducing the regression, then it is possible to hint that query to use just the relevant part of the previous CE version that was working better for the specific query, while still leveraging all other improvements of newer CE versions. 还能使工作负载中未回归的其他查询获益于较新版 CE 的改进。And also allow other queries in the workload that have not regressed to benefit from newer CE improvements.

由 QTA 搜索到的 CE 模式如下:The CE patterns searched by QTA are the following:

  • 独立性与相关性:如果独立性假设为特定查询提供的评估更好,为了说明相关性,查询提示 USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') 会使 SQL ServerSQL Server 生成执行计划,方法是在对筛选器的 AND 谓词进行估算时使用最小选择性。Independence vs. Correlation: If independence assumption provides better estimations for the specific query, then the query hint USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') causes SQL ServerSQL Server to generate an execution plan by using minimum selectivity when estimating AND predicates for filters to account for correlation. 有关详细信息,请参阅 USE HINT 查询提示CE 的版本For more information, see USE HINT query hints and Versions of the CE.
  • 简单包含与基础包含:如果不同的联接包含为特定查询提供了更好的估计,则查询提示 USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') 会使 SQL ServerSQL Server 通过使用“简单包含”假设(而不是默认的“基础包含”假设)来生成执行计划。Simple Containment vs. Base Containment: If a different join containment provides better estimations for the specific query, then the query hint USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') causes SQL ServerSQL Server to generate an execution plan by using the Simple Containment assumption instead of the default Base Containment assumption. 有关详细信息,请参阅 USE HINT 查询提示CE 的版本For more information, see USE HINT query hints and Versions of the CE.
  • 多语句表值函数 (MSTVF) 固定基数猜测 - 100 行与1 行:如果使用 TVF 默认固定估值 100 行与使用 TVF 固定估值 1 行(对应于 SQL Server 2008 R2SQL Server 2008 R2 及早期版本的查询优化器 CE 模型下的默认值)相比,并不能带来更有效的计划,则使用查询提示 QUERYTRACEON 9488 生成执行计划。Multi-statement table-valued function (MSTVF) fixed cardinality guess of 100 rows vs. 1 row: If the default fixed estimation for TVFs of 100 rows does not result in a more efficient plan than using the fixed estimation for TVFs of 1 row (corresponding to the default under the query optimizer CE model of SQL Server 2008 R2SQL Server 2008 R2 and earlier versions), then the query hint QUERYTRACEON 9488 is used to generate an execution plan. 有关 MSTVF 的详细信息,请参阅创建用户定义函数(数据库引擎)For more information on MSTVFs, see Create User-defined Functions (Database Engine).

备注

万不得已时,如果狭窄范围的提示不能为符合条件的查询模式带来足够好的结果,也可考虑使用查询提示 USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') 生成执行计划,从而充分利用 CE 70。As a last resort, if the narrow scoped hints are not yielding good enough results for the eligible query patterns, then full use of CE 70 is also considered, by using the query hint USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') to generate an execution plan.

重要

任何提示都会强制执行可能在将来的 SQL ServerSQL Server 更新中解决的特定行为。Any hint forces certain behaviors that may be addressed in future SQL ServerSQL Server updates. 我们建议仅在没有其他选择且打算每次新升级都重新访问提示的代码时,才应用提示。We recommend you only apply hints when no other option exists, and plan to revisit hinted code with every new upgrade. 强制行为可能导致工作负载无法受益于更新版 SQL ServerSQL Server 中引入的增强功能。By forcing behaviors, you may be precluding your workload from benefiting of enhancements introduced in newer versions of SQL ServerSQL Server.

为数据库升级启动查询优化助手Starting Query Tuning Assistant for database upgrades

QTA 是一种基于会话的功能,它将会话状态存储在首次创建会话的用户数据库的 msqta 架构中。QTA is a session-based feature that stores session state in the msqta schema of the user database where a session is created for the first time. 可在一段时间内在单个数据库上创建多个优化会话,但是任何给定的数据库只能存在一个活动会话。Multiple tuning sessions can be created on a single database over time, but only one active session can exist for any given database.

创建数据库升级会话Creating a database upgrade session

  1. SQL Server Management StudioSQL Server Management Studio 中打开对象资源管理器并连接到 数据库引擎Database EngineIn SQL Server Management StudioSQL Server Management Studio open the Object Explorer and connect to 数据库引擎Database Engine.

  2. 对于计划升级数据库兼容性级别的数据库,右键单击数据库名称,依次选择“任务”和“数据库升级”,再单击“新建数据库升级会话” 。For the database that is intended to upgrade the database compatibility level, right-click the database name, select Tasks, select Database Upgrade, and click on New Database Upgrade Session.

  3. 在 QTA 向导窗口中,配置会话需要两个步骤:In the QTA Wizard window, two steps are required to configure a session:

    1. 在“设置” 窗口中,将查询存储配置为捕获相当于要分析和优化的一个完整业务周期的工作负载数据。In the Setup window, configure Query Store to capture the equivalent of one full business cycle of workload data to analyze and tune.
      • 以天为单位输入预期的工作负载持续时间(最短为 1 天)。Enter the expected workload duration in days (minimum is 1 day). 这将用于提出推荐的查询存储设置,以暂时允许收集完整基线。This will be used to propose recommended Query Store settings to tentatively allow the entire baseline to be collected. 要确保能够分析在更改数据库兼容性级别后找到的任何回归查询,捕获良好的基线至关重要。Capturing a good baseline is important to ensure any regressed queries found after changing the database compatibility level are able to be analyzed.
      • 完成 QTA 工作流之后,设置用户数据库应处于的预期目标数据库兼容性级别。Set the intended target database compatibility level that the user database should be at, after the QTA workflow has completed. 完成后,单击“下一步” 。Once complete, click Next.

    新建数据库升级会话设置窗口New database upgrade session setup window

    1. 在“设置”窗口中,有两列显示目标数据库中查询存储的“当前”状态以及“推荐”设置 。In the Settings window, two columns show the Current state of Query Store in the targeted database, as well as the Recommended settings.
      • 默认选择“推荐”设置,但单击“当前”列的单选按钮会接受当前设置,还可以微调当前的查询存储配置。The Recommended settings are selected by default, but clicking the radio button over the Current column accepts current settings, and also allows fine-tuning the current Query Store configuration.
      • 建议的“过时查询阈值”设置的数值是预期工作负载持续时间(以天为单位)的两倍 。The proposed Stale Query Threshold setting is twice the number of expected workload duration in days. 这是因为查询存储需要保存有关基线工作负载和数据库升级后的工作负载的信息。This is because Query Store will need to hold information on the baseline workload and the post-database upgrade workload. 完成后,单击“下一步” 。Once complete, click Next.

    新建数据库升级设置窗口New database upgrade settings window

    重要

    建议的“最大大小”是适合于短时间工作负载的任意值 。The proposed Max Size is an arbitrary value that may be suited for a short timed workload.
    但是,请记住,对于非常密集的工作负载(即可能生成许多不同的计划时),该值可能不足以保存关于基线和数据库升级后的工作负载的信息。However, keep in mind that it may be insufficient to hold information on the baseline and post-database upgrade workloads for very intensive workloads, namely when many different plans may be generated.
    如果预料到会出现这种情况,请输入一个合适的较大值。If you antecipate this will be the case, enter a higher value that is appropriate.

  4. “优化”窗口结束会话配置,并引导完成打开并继续处理会话的后续步骤 。The Tuning window concludes the session configuration, and instructs on next steps to open and proceed with the session. 完成后,单击“完成” 。Once complete, click Finish.

    新建数据库升级优化窗口New database upgrade tuning window

备注

一种可能的替代方案是将数据库备份从生产服务器(其中,数据库已完成建议的数据库兼容性升级工作流)还原到测试服务器。A possible alternative scenario starts by restoring a database backup from the production server where a database has already gone through the recommended database compatibility upgrade workflow, to a test server.

执行数据库升级工作流Executing the database upgrade workflow

  1. 对于计划升级数据库兼容性级别的数据库,右键单击数据库名称,依次选择“任务”和“数据库升级”,再单击“监视会话” 。For the database that is intended to upgrade the database compatibility level, right-click the database name, select Tasks, select Database Upgrade, and click on Monitor Sessions.

  2. “会话管理”页列出范围内数据库的当前和过去的会话 。The session management page lists current and past sessions for the database in scope. 选择所需会话,然后单击“详细信息” 。Select the desired session, and click on Details.

    备注

    如果当前会话不存在,请单击“刷新”按钮 。If the current session is not present, click the Refresh button.

    列表包含以下信息:The list contains the following information:

    • 会话 IDSession ID
    • 会话名称:系统生成的名称由数据库名称以及创建会话的日期和时间组成。Session Name: System-generated name comprised of the database name, date and time of session creation.
    • 状态:会话状态(活动或关闭)。Status: Status of the session (Active or Closed).
    • 说明:系统生成的说明,包括用户所选的目标数据库兼容性级别以及业务周期工作负载天数。Description: System-generated comprised of the user selected target database compatibility level and number of days for business cycle workload.
    • 开始时间:创建会话的日期和时间。Time Started: Date and time of when the session was created.

    QTA 会话管理页QTA Session Management page

    备注

    删除会话:删除为所选会话存储的任何数据。Delete Session deletes any data stored for the selected session.
    然而,删除已关闭的会话不会删除之前部署的任何计划指南 。However, deleting a closed session does not delete any previously deployed plan guides.
    如果删除已部署计划指南的会话,则无法使用 QTA 进行回滚。If you delete a session that had deployed plan guides, then you cannot use QTA to rollback.
    改为使用 sys.plan_guides 系统表搜索计划指南,然后使用 sp_control_plan_guide 手动删除。Instead, search for plan guides using the sys.plan_guides system table, and delete manually using sp_control_plan_guide.

  3. 新会话的入口点是“数据收集”步骤 。The entry point for a new session is the Data Collection step.

    备注

    通过“会话”按钮可回到“会话管理”页面,而活动会话保持不变 。The Sessions button returns to the session management page, leaving the active session as-is.

    此步骤有以下三个子步骤:This step has three substeps:

    1. 基线数据收集:要求用户运行代表性工作负载周期,以便查询可以收集基线。Baseline Data Collection requests the user to run the representative workload cycle, so that Query Store can collect a baseline. 完成此工作负载后,选中“工作负载运行已完成”并单击“下一步” 。Once that workload has completed, check the Done with workload run and click Next.

      备注

      运行工作负载时,可关闭 QTA 窗口。The QTA window can be closed while the workload runs. 稍后回到仍处于活动状态的会话会从停止的那一步继续。Returning to the session that remains in active state at a later time will resume from the same step where it was left off.

      QTA 步骤 2 的子步骤 1QTA Step 2 Substep 1

    2. 升级数据库:会提示需要将数据库兼容性级别升级到期望目标的权限。Upgrade Database will prompt for permission to upgrade the database compatibility level to the desired target. 若要继续进行下一个子步骤,请单击“是” 。To proceed to the next substep, click Yes.

      QTA 步骤 2 的子步骤 2 - 升级数据库兼容性级别QTA Step 2 Substep 2 - Upgrade database compatibility level

      下一页确认已成功升级数据库兼容性级别。The following page confirms that the database compatibility level was successfully upgraded.

      QTA 步骤 2 的子步骤 2QTA Step 2 Substep 2

    3. “观测到的数据收集” 要求用户重新运行代表性工作负载周期,以便查询存储可收集用于搜索优化机会的比较基线。Observed Data Collection requests the user to run the representative workload cycle again, so that Query Store can collect a comparative baseline that will be used to search for optimization opportunities. 执行工作负载时,使用“刷新”按钮持续更新回归的查询列表(若找到任何回归的查询) 。As the workload executes, use the Refresh button to keep updating the list of regressed queries, if any were found. 更改“要显示的查询数”值,以限制显示的查询数量 。Change the Queries to show value to limit the number of queries displayed. 列表顺序受“指标”(持续时间或 CpuTime)和“聚合”(默认使用平均值) 。The order of the list is affected by the Metric (Duration or CpuTime) and the Aggregation (Average is default). 还需选择要显示的查询数量 。Also select how many Queries to show. 完成此工作负载后,选中“工作负载运行已完成”并单击“下一步” 。Once that workload has completed, check the Done with workload run and click Next.

      QTA 步骤 2 的子步骤 3QTA Step 2 Substep 3

      列表包含以下信息:The list contains the following information:

      • 查询 IDQuery ID
      • 查询文本:可通过单击“...”按钮展开的 Transact-SQLTransact-SQL 语句 。Query Text: Transact-SQLTransact-SQL statement that can be expanded by clicking the ... button.
      • 运行次数:显示为整个工作负载收集执行该查询的次数。Runs: Displays the number of executions of that query for the entire workload collection.
      • 基线指标:升级数据库兼容性之前,为收集基线数据选定的单位为毫秒的指标(持续时间或 CpuTime)。Baseline Metric: The selected metric (Duration or CpuTime) in ms for the baseline data collection before the database compatibility upgrade.
      • 观测到的指标:升级数据库兼容性之后,为收集数据选定的单位为毫秒的指标(持续时间或 CpuTime)。Observed Metric: The selected metric (Duration or CpuTime) in ms for the data collection after the database compatibility upgrade.
      • 百分比变化:数据库兼容性升级状态前后所选指标的百分比变化。% Change: Percent change for the selected metric between the before and after database compatibility upgrade state. 负数表示查询的测量的回归数量。A negative number represents the amount of measured regression for the query.
      • 可优化:True 或 False,具体取决于查询是否有资格进行试验 。Tunable: True or False depending on whether the query is eligible for experimentation.
  4. 查看分析可选择要试验的查询并查找优化机会。View Analysis allows selection of which queries to experiment and find optimization opportunities. “要显示的查询数量”值成为要试验的符合条件的查询的范围 。The Queries to show value becomes the scope of eligible queries to experiment on. 选中所需查询后,单击“下一步”开始试验 。Once the desired queries are checked, click Next to start experimentation.

    备注

    不能选择“可优化”为“False”查询进行试验。Queries with Tunable = False cannot be selected for experimentation.

    重要

    系统显示一个提示,告知在 QTA 进行到试验阶段后,不可能回到“查看分析”页面。A prompt advises that once QTA moves to the experimentation phase, returning to the View Analysis page will not be possible.
    如果在前进到试验阶段之前未选择所有符合条件的查询,需在稍后创建新会话并重复工作流。If you don't select all eligible queries before moving to the experimentation phase, you need to create a new session at a later time, and repeat the workflow. 这需要将数据库兼容性级别重置回之前的值。This requires reset of database compatibility level to the previous value.

    QTA 步骤 3QTA Step 3

  5. 查看结果:可选择要将建议优化部署为计划指南的查询。View Findings allows selection of which queries to deploy the proposed optimization as a plan guide.

    列表包含以下信息:The list contains the following information:

    • 查询 IDQuery ID
    • 查询文本:可通过单击“...”按钮展开的 Transact-SQLTransact-SQL 语句 。Query Text: Transact-SQLTransact-SQL statement that can be expanded by clicking the ... button.
    • 状态:显示查询的当前试验状态。Status: Displays the current experimentation state for the query.
    • 基线指标:为步骤 2 子步骤 3 中执行的查询选定的单位为毫秒的指标(持续时间或 CpuTime),表示升级数据库兼容性后的回归查询 。Baseline Metric: The selected metric (Duration or CpuTime) in ms for the query as executed in Step 2 Substep 3, representing the regressed query after the database compatibility upgrade.
    • 观测到的指标:为对试验后的查询进行很好的建议优化选定的单位为毫秒的指标(持续时间或 CpuTime),已进行足够好的建议优化。Observed Metric: The selected metric (Duration or CpuTime) in ms for the query after experimentation, for a good enough proposed optimization.
    • 百分比变化:试验状态前后所选指标的百分比变化,表示执行建议的优化后测得的查询的改进量。% Change: Percent change for the selected metric between the before and after experimentation state, representing the amount of measured improvement for the query with the proposed optimization.
    • 查询选项:链接到改进查询执行指标的建议提示。Query Option: Link to the proposed hint that improves query execution metric.
    • 可部署:True 或 False,具体取决于是否能将建议的查询优化部署为计划指南 。Can Deploy: True or False depending on whether the proposed query optimization can be deployed as a plan guide.

    QTA 步骤 4QTA Step 4

  6. 验证:显示之前为此会话选定的查询的部署状态。Verification shows the deployment status of previously selected queries for this session. 此页的列表不同于上一页,“可以部署”列更改为了“可以回滚” 。The list in this page differs from the previous page by changing the Can Deploy column to Can Rollback. 此列可以为 True 或 False,具体取决于是否可以回滚部署的查询优化以及是否可以删除它的计划指南 。This column can be True or False depending on whether the deployed query optimization can be rolled back and its plan guide removed.

    QTA 步骤 5QTA Step 5

    如果之后需要对建议优化进行回滚,请选择相关查询,并单击“回滚” 。If at a later date there is a need to roll back on a proposed optimization, then select the relevant query and click Rollback. 删除此查询计划指南并更新列表,以删除已回滚的查询。That query plan guide is removed and the list updated to remove the rolled back query. 请注意,在下图中,已删除查询 8。Note in the picture below that query 8 was removed.

    QTA 步骤 5 - 回滚QTA Step 5 - Rollback

    备注

    删除已关闭的会话不会删除之前部署的任何计划指南 。Deleting a closed session does not delete any previously deployed plan guides.
    如果删除已部署计划指南的会话,则无法使用 QTA 进行回滚。If you delete a session that had deployed plan guides, then you cannot use QTA to rollback.
    改为使用 sys.plan_guides 系统表搜索计划指南,然后使用 sp_control_plan_guide 手动删除。Instead, search for plan guides using the sys.plan_guides system table, and delete manually using sp_control_plan_guide.

权限Permissions

需要的成员资格为 db_owner 角色。Requires membership of db_owner role.

另请参阅See Also

兼容性级别和 SQL Server 升级 Compatibility Levels and SQL Server Upgrades
性能监视和优化工具 Performance Monitoring and Tuning Tools
相关视图、函数和过程 Monitoring Performance By Using the Query Store
更改数据库兼容性模式和使用 Query Store Change the Database Compatibility Mode and Use the Query Store
跟踪标志 Trace flags
USE HINT 查询提示 USE HINT query hints
基数估计器 Cardinality Estimator
自动优化Automatic Tuning