查找并应用性能建议Find and apply performance recommendations

可以使用 Azure 门户查找可优化 Azure SQL 数据库或更正工作负载中发现的一些问题的性能建议。You can use the Azure portal to find performance recommendations that can optimize performance of your Azure SQL Database or to correct some issue identified in your workload. 使用 Azure 门户中的“性能建议”页可以根据建议的潜在影响查找最重要的建议。Performance recommendation page in Azure portal enables you to find the top recommendations based on their potential impact.

查看建议Viewing recommendations

若要查看和应用性能建议,需要 Azure 中相应的基于角色的访问控制权限。To view and apply performance recommendations, you need the correct role-based access control permissions in Azure. 查看建议需要“读取者”、“SQL DB 参与者”权限,执行任何操作(如创建或删除索引、取消创建索引)需要“所有者”、“SQL DB 参与者”权限。Reader, SQL DB Contributor permissions are required to view recommendations, and Owner, SQL DB Contributor permissions are required to execute any actions; create or drop indexes and cancel index creation.

在 Azure 门户中使用以下步骤查找性能建议:Use the following steps to find performance recommendations on Azure portal:

  1. 登录到 Azure 门户Sign in to the Azure portal.
  2. 转到“所有服务” > “SQL 数据库”,然后选择数据库。Go to All services > SQL databases, and select your database.
  3. 导航到“性能建议”,查看适用于所选数据库的可用建议。Navigate to Performance recommendation to view available recommendations for the selected database.

性能建议会显示在类似于下图所示的表中:Performance recommendations are shown in the table similar to the one shown on the following figure:

建议

按其对性能的潜在影响将建议分为以下类别:Recommendations are sorted by their potential impact on performance into the following categories:

影响Impact 说明Description
High 高影响建议应提供最重要的性能影响。High impact recommendations should provide the most significant performance impact.
中型Medium 中等影响建议应提高性能,但提升程度不大。Medium impact recommendations should improve performance, but not substantially.
Low 低影响建议提供的性能比没有时更好,但改进可能不明显。Low impact recommendations should provide better performance than without, but improvements might not be significant.

Note

Azure SQL 数据库至少需要监视一天的活动才能识别某些建议。Azure SQL Database needs to monitor activities at least for a day in order to identify some recommendations. Azure SQL 数据库优化一致的查询模式比优化随机的突发活动更加轻松。The Azure SQL Database can more easily optimize for consistent query patterns than it can for random spotty bursts of activity. 如果建议当前不可用,“性能建议”页会提供一条说明原因的消息。If recommendations are not currently available, the Performance recommendation page provides a message explaining why.

还可以查看历史操作的状态。You can also view the status of the historical operations. 选择一条建议或状态即可查看详细信息。Select a recommendation or status to see more information.

下面是 Azure 门户中“创建索引”建议的示例。Here is an example of "Create index" recommendation in the Azure portal.

创建索引

应用建议Applying recommendations

Azure SQL 数据库允许通过以下三个选项之一完全控制建议的启用方式:Azure SQL Database gives you full control over how recommendations are enabled using any of the following three options:

  • 一次应用一个建议。Apply individual recommendations one at a time.
  • 启用自动优化以自动应用建议。Enable the Automatic tuning to automatically apply recommendations.
  • 若要手动实施建议,请针对数据库运行建议的 T-SQL 脚本。To implement a recommendation manually, run the recommended T-SQL script against your database.

选择要查看其详细信息的建议,然后单击“查看脚本”查看具体详细信息,了解如何创建建议。Select any recommendation to view its details and then click View script to review the exact details of how the recommendation is created.

应用建议时,数据库将保持联机状态 -- 使用性能建议或自动优化不会使数据库脱机。The database remains online while the recommendation is applied -- using performance recommendation or automatic tuning never takes a database offline.

应用单个建议Apply an individual recommendation

可以逐个查看和接受建议。You can review and accept recommendations one at a time.

  1. 在“建议”页上选择某个建议。On the Recommendations page, select a recommendation.

  2. 在“详细信息”页上单击“应用”按钮。On the Details page, click Apply button.

    应用建议

所选的建议将在数据库上应用。Selected recommendation are applied on the database.

从列表中删除建议Removing recommendations from the list

如果建议的列表中包含你想要删除的项则可放弃建议:If your list of recommendations contains items that you want to remove from the list, you can discard the recommendation:

  1. 选择“建议”列表中的建议打开详细信息。Select a recommendation in the list of Recommendations to open the details.
  2. 单击“详细信息”页上的“放弃”。Click Discard on the Details page.

可以根据需要将已放弃的项重新添加到“建议”列表中: If desired, you can add discarded items back to the Recommendations list:

  1. 在“建议”页上单击“查看已放弃项”。On the Recommendations page, click View discarded.
  2. 从列表中选择一个放弃的项,查看其详细信息。Select a discarded item from the list to view its details.
  3. (可选)单击“撤消放弃”,将索引重新添加到“建议”的主列表。Optionally, click Undo Discard to add the index back to the main list of Recommendations.

Note

请注意,如果已启用 SQL 数据库自动优化,并且如果已手动放弃列表中的建议,则此类建议将永远不会自动应用。Please note that if SQL Database Automatic tuning is enabled, and if you have manually discarded a recommendation from the list, such recommendation will never be applied automatically. 放弃建议是让用户能够在要求不应用某条特定建议的情况下启用自动优化的方便方法。Discarding a recommendation is a handy way for users to have Automatic tuning enabled in cases when requiring that a specific recommendation shouldn't be applied. 可以通过选择“撤消放弃”选项将放弃的建议添加回建议列表来还原此行为。You can revert this behavior by adding discarded recommendations back to the Recommendations list by selecting the Undo Discard option.

启用自动优化Enable automatic tuning

可以将 Azure SQL 数据库设置为自动实施建议。You can set the Azure SQL Database to implement recommendations automatically. 建议变为可用时,会自动应用。As recommendations become available, they are automatically applied. 与该服务所管理的所有建议一样,如果存在负面的性能影响,则会还原建议。As with all recommendations managed by the service, if the performance impact is negative, the recommendation is reverted.

  1. 在“建议”页上单击“自动化”:On the Recommendations page, click Automate:

    索引顾问设置

  2. 选择要自动执行的操作:Select actions to automate:

    建议的索引

Note

请注意,DROP_INDEX 选项当前与使用分区切换和索引提示的应用程序不兼容。Please note that DROP_INDEX option is currently not compatible with applications using partition switching and index hints.

选择所需配置后,请单击“应用”。Once you have selected your desired configuration, click Apply.

通过 T-SQL 手动应用建议Manually apply recommendations through T-SQL

选择任意建议,然后单击“查看脚本”。Select any recommendation and then click View script. 针对数据库运行此脚本以手动应用建议。Run this script against your database to manually apply the recommendation.

不通过该服务监视和验证手动执行的索引的性能影响,因此建议在创建后监视这些索引以验证它们是否提供性能提升,并在必要时调整或删除它们。Indexes that are manually executed are not monitored and validated for performance impact by the service so it is suggested that you monitor these indexes after creation to verify they provide performance gains and adjust or delete them if necessary. 有关创建索引的详细信息,请参阅 CREATE INDEX (Transact-SQL)For details about creating indexes, see CREATE INDEX (Transact-SQL). 此外,手动应用的建议在系统自动撤消它们之前,In addition, manually applied recommendations will remain active and shown in the list of recommendations for 24-48 hrs. 将在 24-48 小时内保持活动状态并显示在建议列表中。before the system automatically withdraws them. 如果你想要更快地删除建议,可以手动放弃它。If you would like to remove a recommendation sooner, you can manually discard it.

取消建议Canceling recommendations

可以取消处于“待定”、“正在验证”或“成功”状态的建议。Recommendations that are in a Pending, Validating, or Success status can be canceled. 不能取消状态为“正在执行”的建议。Recommendations with a status of Executing cannot be canceled.

  1. 在“优化历史记录”区域中选择建议,打开“建议详细信息”页。Select a recommendation in the Tuning History area to open the recommendations details page.
  2. 单击“取消”可中止应用建议的过程。Click Cancel to abort the process of applying the recommendation.

监视操作Monitoring operations

可能不会立刻应用建议。Applying a recommendation might not happen instantaneously. 该门户提供了有关建议状态的详细信息。The portal provides details regarding the status of recommendation. 以下是索引可能处于的状态:The following are possible states that an index can be in:

状态Status 说明Description
挂起Pending 已经接收应用建议命令并计划执行。Apply recommendation command has been received and is scheduled for execution.
执行Executing 正在应用建议。The recommendation is being applied.
正在验证Validating 已成功应用建议,服务正在权衡优势。Recommendation was successfully applied and the service is measuring the benefits.
SuccessSuccess 已成功应用建议,并且已权衡优势。Recommendation was successfully applied and benefits have been measured.
错误Error 在应用建议的过程期间发生了错误。An error occurred during the process of applying the recommendation. 这可能是暂时性问题,或可能是表的架构更改所致,并且脚本不再有效。This can be a transient issue, or possibly a schema change to the table and the script is no longer valid.
还原Reverting 已应用建议,但该建议被认为是非性能的且正在被自动还原。The recommendation was applied, but has been deemed non-performant and is being automatically reverted.
已还原Reverted 已还原建议。The recommendation was reverted.

单击列表中的进程内建议即可查看详细信息:Click an in-process recommendation from the list to see more information:

建议的索引

正在还原建议Reverting a recommendation

如果使用了性能建议来应用建议(即没有手动运行 T-SQL 脚本),那么性能建议会在发现性能影响为负面时自动还原该更改。If you used the performance recommendations to apply the recommendation (meaning you did not manually run the T-SQL script), it automatically reverts the change if it finds the performance impact to be negative. 如果因故只想要还原建议,可执行以下操作:If for any reason you simply want to revert a recommendation, you can do the following:

  1. 在“优化历史记录”区域中选择已成功应用的建议。Select a successfully applied recommendation in the Tuning history area.
  2. 单击“建议详细信息”页上的“还原”。Click Revert on the recommendation details page.

建议的索引

监视索引建议的性能影响Monitoring performance impact of index recommendations

成功实施建议后(当前仅限索引操作和参数化查询建议),可单击“建议详细信息”页上的“查询见解”打开查询性能见解,并查看排位靠前的查询的性能影响。After recommendations are successfully implemented (currently, index operations and parameterize queries recommendations only), you can click Query Insights on the recommendation details page to open Query Performance Insights and see the performance impact of your top queries.

监视性能影响

摘要Summary

Azure SQL 数据库提供可提高 SQL 数据库性能的建议。Azure SQL Database provides recommendations for improving SQL database performance. 通过提供 T-SQL 脚本,可以获得帮助来优化数据库并最终提升查询性能。By providing T-SQL scripts, you get assistance in optimizing your database and ultimately improving query performance.

后续步骤Next steps

监视建议并继续应用它们以优化性能。Monitor your recommendations and continue to apply them to refine performance. 数据库工作负荷是动态的,并且不断地更改。Database workloads are dynamic and change continuously. Azure SQL 数据库将继续监视和提供可能提高数据库性能的建议。Azure SQL Database continues to monitor and provide recommendations that can potentially improve your database's performance.

其他资源Additional resources