针对单一数据库和共用数据库的数据库顾问性能建议Database Advisor performance recommendations for single and pooled databases

Azure SQL 数据库与应用程序一起自行学习和进行适应性调整。Azure SQL Database learns and adapts with your application. 对于单一数据库和共用数据库,SQL 数据库有许多数据库顾问,它们提供定制的建议,使你能够最大程度地提高性能。For single and pooled databases, SQL Database has a number of database advisors that provide customized recommendations that enable you to maximize performance. 这些数据库顾问会持续评估和分析使用情况历史记录,并根据工作负荷模式提供有助于提高性能的建议。These database advisors continuously assess and analyze the usage history and provide recommendations based on workload patterns that help improve performance.

性能概述Performance overview

性能概述提供了数据库性能摘要,有助于进行性能优化和故障排除。Performance overview provides a summary of your database performance, and helps you with performance tuning and troubleshooting.

Azure SQL 数据库性能概述

  • “建议”磁贴提供数据库的优化建议明细(如果建议超出 3 条,则将显示前 3 条)。****The Recommendations tile provides a breakdown of tuning recommendations for your database (top three recommendations are shown if there are more). 单击此磁贴可转到 性能建议选项Clicking this tile takes you to Performance recommendation options.
  • “优化活动” **** 磁贴提供正在进行的和已完成的数据库优化操作摘要,可快速查看优化活动的历史记录。The Tuning activity tile provides a summary of the ongoing and completed tuning actions for your database, giving you a quick view into the history of tuning activity. 单击此磁贴可转到数据库的完整优化历史记录视图。Clicking this tile takes you to the full tuning history view for your database.
  • “自动优化”**** 磁贴显示数据库的 自动优化配置 (已自动应用到数据库的优化选项)。The Auto-tuning tile shows the auto-tuning configuration for your database (tuning options that are automatically applied to your database). 单击此磁贴可打开自动化配置对话框。Clicking this tile opens the automation configuration dialog.
  • “数据库查询”磁贴显示数据库的查询性能摘要(DTU 整体使用情况和排名靠前的资源消耗查询)。****The Database queries tile shows the summary of the query performance for your database (overall DTU usage and top resource consuming queries). 单击此磁贴可转到 查询性能见解Clicking this tile takes you to Query Performance Insight.

性能建议选项Performance recommendation options

Azure SQL 数据库中的单一数据库和共用数据库可用的性能建议选项包括:Performance recommendation options available for single and pooled databases in Azure SQL Database are:

性能建议Performance recommendation 单一数据库和共用数据库支持Single database and pooled database support 实例数据库支持Instance database support
“创建索引”建议 - 有关如何创建索引的建议,可以提高工作负荷性能。Create index recommendations - Recommends creation of indexes that may improve performance of your workload. Yes No
“删除索引”建议 - 建议每日删除冗余和重复的索引,但不包括唯一索引和长时间(>90 天)未使用的索引。Drop index recommendations - Recommends removal of redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). 请注意,此选项与使用分区切换和索引提示的应用程序不兼容。Please note that this option is not compatible with applications using partition switching and index hints. 高级和业务关键服务层不支持删除未使用的索引。Dropping unused indexes is not supported for Premium and Business Critical service tiers. Yes No
“参数化查询”建议(预览版) - 建议在具有一个或多个正在持续被重新编译但都以相同的查询执行计划结束的查询时强制实施参数化。Parameterize queries recommendations (preview) - Recommends forced parameterization in cases when you have one or more queries that are constantly being recompiled but end up with the same query execution plan. Yes No
“修复架构问题”建议(预览版) - 当 SQL 数据库服务发现 SQL 数据库上架构相关 SQL 错误的数量发生异常时,就会出现用于修复架构的建议。Fix schema issues recommendations (preview) - Recommendations for schema correction appear when the SQL Database service notices an anomaly in the number of schema-related SQL errors that are happening on your SQL database. Microsoft 当前正在弃用“修复架构问题”建议。Microsoft is currently deprecating "Fix schema issue" recommendations. Yes No

Azure SQL 数据库性能建议

若要应用性能建议,请参阅应用建议To apply performance recommendations, see applying recommendations. 若要查看建议的状态,请参阅监视操作To view the status of recommendations, see Monitoring operations.

还可以查找有关以前应用的优化操作的完整历史记录。You can also find complete history of tuning actions that were applied in the past.

创建索引建议Create index recommendations

Azure SQL 数据库持续监视正在运行的查询,并发现可以提升性能的索引。Azure SQL Database continuously monitors the queries that are running and identifies the indexes that could improve performance. 确信缺少特定索引后,便会新建“创建索引”**** 建议。After there's enough confidence that a certain index is missing, a new Create index recommendation is created.

Azure SQL 数据库通过估计索引在一段时间内带来的性能提升,确信是否有必要创建索引。Azure SQL Database builds confidence by estimating the performance gain the index would bring through time. 根据估计的性能提升高低,将性能建议分为高、中或低三类。Depending on the estimated performance gain, recommendations are categorized as high, medium, or low.

使用建议创建的索引始终都会标记为 auto_created。Indexes that are created by using recommendations are always flagged as auto-created indexes. 可以通过查看 sys.indexes 视图,确定哪些索引是自动创建的。You can see which indexes are auto-created by looking at the sys.indexes view. 自动创建的索引不会阻止 ALTER/RENAME 命令。Auto-created indexes don't block ALTER/RENAME commands.

如果尝试删除包含自动创建的索引的列,那么命令会传递下去。If you try to drop the column that has an auto-created index over it, the command passes. 自动创建的索引会随列一起删除。The auto-created index is dropped with the command as well. 常规索引会阻止对已编制索引的列执行 ALTER/RENAME 命令。Regular indexes block the ALTER/RENAME command on columns that are indexed.

应用“创建索引”建议后,Azure SQL 数据库便会比较查询性能与基线性能。After the create index recommendation is applied, Azure SQL Database compares the performance of the queries with the baseline performance. 如果新索引提升了性能,则将建议标记为成功,并提供影响报表。If the new index improved performance, the recommendation is flagged as successful and the impact report is available. 如果索引未提升性能,则会自动还原。If the index didn't improve performance, it's automatically reverted. Azure SQL 数据库使用此过程确保建议可提升数据库性能。Azure SQL Database uses this process to ensure that recommendations improve database performance.

所有“创建索引”**** 建议都有退让策略,即如果数据库或池的资源使用率很高,则不允许应用建议。Any create index recommendation has a back-off policy that doesn't allow applying the recommendation if the resource usage of a database or pool is high. 退让策略会考虑 CPU、数据 IO、日志 IO 和可用存储。The back-off policy takes into account CPU, Data IO, Log IO, and available storage.

如果 CPU、数据 IO 或日志 IO 在之前的 30 分钟内高于 80%,则推迟“创建索引”建议。If CPU, data IO, or log IO is higher than 80% in the previous 30 minutes, the create index recommendation is postponed. 如果创建索引后可用存储低于 10%,则建议会进入错误状态。If the available storage will be below 10% after the index is created, the recommendation goes into an error state. 如果几天后自动优化仍认为索引会很有益,此过程再次启动。If, after a couple of days, automatic tuning still believes that the index would be beneficial, the process starts again.

此过程不断重复,直到没有足够的可用存储来创建索引或不再认为索引有益。This process repeats until there's enough available storage to create an index, or until the index isn't seen as beneficial anymore.

删除索引建议Drop index recommendations

除了检测缺少的索引外,Azure SQL 数据库还会持续分析现有索引的性能。Besides detecting missing indexes, Azure SQL Database continuously analyzes the performance of existing indexes. Azure SQL 数据库会建议删除未使用的索引。If an index is not used, Azure SQL Database recommends dropping it. 在两种情况下会建议删除索引:Dropping an index is recommended in two cases:

  • 索引是另一索引的副本(已编入索引且包含的列、分区架构和筛选器都相同)。The index is a duplicate of another index (same indexed and included column, partition schema, and filters).
  • 长时间(93 天)未使用索引。The index hasn't been used for a prolonged period (93 days).

删除索引建议在实现后也要进行验证。Drop index recommendations also go through the verification after implementation. 如果性能得到提升,则会生成影响力报表。If the performance improves, the impact report is available. 如果性能下降,则会还原建议。If performance degrades, the recommendation is reverted.

“参数化查询”建议(预览版)Parameterize queries recommendations (preview)

参数化查询 建议。Parameterize queries recommendations appear when you have one or more queries that are constantly being recompiled but end up with the same query execution plan. 这种状态提供了一个应用强制参数化的机会。This condition creates an opportunity to apply forced parameterization. 而强制参数化允许缓存并在将来重复使用查询计划,从而改善性能和减少资源使用。Forced parameterization, in turn, allows query plans to be cached and reused in the future, which improves performance and reduces resource usage.

对 SQL Server 发出的每个查询一开始需要进行编译,生成执行计划。Every query that's issued against SQL Server initially needs to be compiled to generate an execution plan. 每个生成的计划添加到计划缓存中。Each generated plan is added to the plan cache. 相同查询的后续执行可以重复使用该缓存中的此计划,而无需进一步编译。Subsequent executions of the same query can reuse this plan from the cache, which eliminates the need for additional compilation.

包含非参数化值的查询可能会导致性能开销,因为每次非参数化值有所不同时,需重新编译执行计划。Queries with non-parameterized values can lead to performance overhead because the execution plan is recompiled each time the non-parameterized values are different. 在许多情况下,使用不同参数值的相同查询生成相同的执行计划。In many cases, the same queries with different parameter values generate the same execution plans. 但这些计划仍然分别添加到计划缓存中。These plans, however, are still separately added to the plan cache.

重新编译执行计划的过程会占用数据库资源、增加查询持续时间并使计划缓存溢出。The process of recompiling execution plans uses database resources, increases the query duration time, and overflows the plan cache. 而这些事件导致系统从缓存中逐出计划。These events, in turn, cause plans to be evicted from the cache. 可以通过对数据库设置强制参数化选项来更改此 SQL Server 行为。This SQL Server behavior can be altered by setting the forced parameterization option on the database.

为了帮助估计此建议的影响,系统提供实际 CPU 使用率和预计 CPU 使用率(就像已应用建议一样)之间的比较。To help you estimate the impact of this recommendation, you are provided with a comparison between the actual CPU usage and the projected CPU usage (as if the recommendation were applied). 此建议可帮助节省 CPU。This recommendation can help you gain CPU savings. 它还可帮助减少查询持续时间和计划缓存的开销,这意味着更多的计划可保留在缓存中并重复使用。It can also help you decrease query duration and overhead for the plan cache, which means that more of the plans can stay in the cache and be reused. 可以通过选择“应用”**** 命令来快速轻松地应用此建议。You can apply this recommendation quickly by selecting the Apply command.

应用此建议后,它会在几分钟之内对数据库启用强制参数化。After you apply this recommendation, it enables forced parameterization within minutes on your database. 并启动监视进程(大约持续 24 小时)。It starts the monitoring process, which lasts for approximately 24 hours. 经过这段时间后,即可看到验证报告。After this period, you can see the validation report. 该报告显示应用此建议之前和之后的 24 小时内数据库的 CPU 使用率。This report shows the CPU usage of your database 24 hours before and after the recommendation has been applied. SQL 数据库顾问具有安全机制,在检测到性能衰退时可以自动还原已应用的建议。SQL Database Advisor has a safety mechanism that automatically reverts the applied recommendation if performance regression has been detected.

修复架构问题建议(预览)Fix schema issues recommendations (preview)

Important

Azure 当前正在弃用“修复架构问题”建议。Azure is currently deprecating "Fix schema issue" recommendations. 建议使用智能见解来监视数据库性能问题,包括“修复架构问题”建议以前涉及的架构问题。We recommend that you use Intelligent Insights to monitor your database performance issues, including schema issues that the "Fix schema issue" recommendations previously covered.

当 Azure SQL 数据库服务发现 SQL 数据库上架构相关 SQL 错误的数量发生异常时,就会出现“修复架构问题”建议****。Fix schema issues recommendations appear when the Azure SQL Database service notices an anomaly in the number of schema-related SQL errors that are happening on your SQL database. 此建议通常在数据库在一个小时内遭遇到多个架构相关的错误(无效的列名、无效的对象名称等)时出现。This recommendation typically appears when your database encounters multiple schema-related errors (invalid column name, invalid object name, and so on) within an hour.

“架构问题”是 SQL Server 中的一类语法错误。"Schema issues" are a class of syntax errors in SQL Server. 此类错误于 SQL 查询的定义与数据库架构的定义不一致时发生。They occur when the definition of the SQL query and the definition of the database schema aren't aligned. 例如,目标表中可能缺少查询所需的某个列,反之亦然。For example, one of the columns that's expected by the query might be missing in the target table or vice-versa.

当 Azure SQL 数据库服务发现 SQL 数据库上架构相关 SQL 错误的数量发生异常时,就会出现“修复架构问题”建议。The "Fix schema issue" recommendation appears when the Azure SQL Database service notices an anomaly in the number of schema-related SQL errors that are happening on your SQL database. 下表显示与架构问题相关的错误:The following table shows the errors that are related to schema issues:

SQL 错误代码SQL error code MessageMessage
201201 过程或函数“ ”需要参数“ ”,但未提供该参数。Procedure or function '' expects parameter '', which was not supplied.
207207 列名称“*”无效。Invalid column name '*'.
208208 对象名“*”无效。Invalid object name '*'.
213213 列名称或所提供值的数目与表定义不匹配。Column name or number of supplied values does not match table definition.
28122812 找不到存储过程“*”。Could not find stored procedure '*'.
81448144 为过程或函数 * 指定了过多的参数。Procedure or function * has too many arguments specified.

自定义应用程序Custom applications

开发人员可以考虑使用针对 Azure SQL 数据库的性能建议来开发自定义应用程序。Developers might consider developing custom applications using performance recommendations for Azure SQL Database. 可以通过 Get-AzSqlDatabaseRecommendedAction API 访问门户中列出的针对数据库的所有建议。All recommendations listed in the portal for a database can be accessed through Get-AzSqlDatabaseRecommendedAction API.

后续步骤Next steps