SQL 数据库性能建议Performance recommendations for SQL Database

Azure SQL 数据库与应用程序一起自行学习和进行适应性调整。Azure SQL Database learns and adapts with your application. 它提供可最大限度提高 SQL 数据库性能的自定义建议。It provides customized recommendations that enable you to maximize the performance of your SQL databases. SQL 数据库持续评估并分析 SQL 数据库的使用情况历史记录。SQL Database continuously assesses and analyzes the usage history of your SQL databases. 提供的建议以数据库唯一工作负载模式为依据,有助于提升性能。The recommendations that are provided are based on database-unique workload patterns and help improve performance.

Tip

自动优化是自动优化一些最常见的数据库性能问题的建议方法。Automatic tuning is the recommended method to automatically tune some of the most common database performance issues. 查询性能见解是针对基本 Azure SQL 数据库性能监视需求的建议方法。Query Performance Insights is the recommended method for basic Azure SQL Database performance monitoring needs.

创建索引建议Create index recommendations

SQL 数据库持续监视正在运行的查询,并发现可以提升性能的索引。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 视图,确定哪些是 auto_created 索引。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. SQL 数据库使用此过程确保建议可提升数据库性能。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

除了检测缺少的索引外,SQL 数据库还会持续分析现有索引的性能。Besides detecting missing indexes, 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

参数化查询 建议。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.

当 SQL 数据库服务发现 SQL 数据库上架构相关 SQL 错误的数量发生异常时,就会出现“修复架构问题”建议。Fix schema issues recommendations appear when the 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 消息Message
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

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