Azure SQL 数据库与 Azure SQL 托管实例中的监视和性能优化Monitoring and performance tuning in Azure SQL Database and Azure SQL Managed Instance

适用于: Azure SQL 数据库 Azure SQL 托管实例

若要监视 Azure SQL 数据库和 Azure SQL 托管实例中某个数据库的性能,首先需要监视工作负荷使用的、相对于在选择特定服务层和性能级别所选的数据库性能级别的 CPU 和 IO 资源。To monitor the performance of a database in Azure SQL Database and Azure SQL Managed Instance, start by monitoring the CPU and IO resources used by your workload relative to the level of database performance you chose in selecting a particular service tier and performance level. 为了帮助实现此目的,Azure SQL 数据库和 Azure SQL 托管实例会发出资源指标,你可以在 Azure 门户中或使用以下 SQL Server 管理工具之一查看这些指标:Azure Data StudioSQL Server Management Studio (SSMS)。To accomplish this, Azure SQL Database and Azure SQL Managed Instance emit resource metrics that can be viewed in the Azure portal or by using one of these SQL Server management tools: Azure Data Studio or SQL Server Management Studio (SSMS).

Azure SQL 数据库提供多个数据库顾问来提供智能性能优化建议以及用于提高性能的自动优化选项。Azure SQL Database provides a number of Database Advisors to provide intelligent performance tuning recommendations and automatic tuning options to improve performance. 此外,Query Performance Insight 会显示有关针对单一数据库和共用数据库运行的、CPU 和 IO 占用量最高的查询的详细信息。Additionally, Query Performance Insight shows you details about the queries responsible for the most CPU and IO usage for single and pooled databases.

Azure SQL 数据库和 Azure SQL 托管实例提供基于人工智能的其他监视和优化功能,以帮助排查数据库和解决方案的性能问题并实现其最高性能。Azure SQL Database and Azure SQL Managed Instance provide advanced monitoring and tuning capabilities backed by artificial intelligence to assist you in troubleshooting and maximizing the performance of your databases and solutions. 可以选择将智能见解以及其他数据库资源日志与指标配置为流式导出到多个目标之一,使其可供使用和分析。You can choose to configure the streaming export of these Intelligent Insights and other database resource logs and metrics to one of several destinations for consumption and analysis. Azure SQL Analytics 是一种高级云监视解决方案,用于在单个视图中跨多个订阅大规模监视所有数据库的性能。Azure SQL Analytics is an advanced cloud monitoring solution for monitoring performance of all of your databases at scale and across multiple subscriptions in a single view. 有关可导出的日志和指标列表,请参阅可导出的诊断遥测数据For a list of the logs and metrics that you can export, see diagnostic telemetry for export

SQL Server 具有自己的监视和诊断功能,SQL 数据库和 SQL 托管实例可以利用这些功能,例如查询存储动态管理视图 (DMV)SQL Server has its own monitoring and diagnostic capabilities that SQL Database and SQL Managed Instance leverage, such as query store and dynamic management views (DMVs). 有关用于监视各种性能问题的脚本,请参阅使用 DMV 进行监视See Monitoring using DMVs for scripts to monitor for a variety of performance issues.

Azure 门户中的监视和优化功能Monitoring and tuning capabilities in the Azure portal

在 Azure 门户中,Azure SQL 数据库和 Azure SQL 托管实例提供对资源指标的监视。In the Azure portal, Azure SQL Database and Azure SQL Managed Instance provide monitoring of resource metrics. Azure SQL 数据库提供数据库顾问,而 Query Performance Insight 提供查询优化建和查询性能分析。Azure SQL Database provides database advisors, and Query Performance Insight provides query tuning recommendations and query performance analysis. 在 Azure 门户中,可为逻辑 SQL 服务器及其单一数据库和共用数据库启用自动优化。In the Azure portal, you can enable automatic tuning for logical SQL servers and their single and pooled databases.


使用率极低的数据库在门户中显示的使用情况可能低于实际使用情况。Databases with extremely low usage may show in the portal with less than actual usage. 由于在将双精度值转换为最接近的整数时发出遥测的方式,某些小于 0.5 的使用量将舍入为 0,这会导致发出的遥测的精度降低。Due to the way telemetry is emitted when converting a double value to the nearest integer certain usage amounts less than 0.5 will be rounded to 0 which causes a loss in granularity of the emitted telemetry. 有关详细信息,请参阅数据库和弹性池低指标舍入为零For details, see Low database and elastic pool metrics rounding to zero.

Azure SQL 数据库和 Azure SQL 托管实例资源监视Azure SQL Database and Azure SQL Managed Instance resource monitoring

可以在 Azure 门户上的“指标”视图中快速监视各种资源指标。You can quickly monitor a variety of resource metrics in the Azure portal in the Metrics view. 通过这些指标,你可以查看数据库是否即将用完 100% 的处理器、内存或 IO 资源。These metrics enable you to see if a database is reaching 100% of processor, memory, or IO resources. DTU 或处理器百分比较高以及 IO 百分比较高表明工作负载可能需要更多的 CPU 或 IO 资源。High DTU or processor percentage, as well as high IO percentage, indicates that your workload might need more CPU or IO resources. 它还可能指示查询需要优化。It might also indicate queries that need to be optimized.


Azure SQL 数据库中的数据库顾问Database advisors in Azure SQL Database

Azure SQL 数据库包含针对单一数据库和共用数据库提供性能优化建议的数据库顾问Azure SQL Database includes database advisors that provide performance tuning recommendations for single and pooled databases. 这些建议会在 Azure 门户中提供,也可以使用 PowerShell 来查看。These recommendations are available in the Azure portal as well as by using PowerShell. 还可以启用自动优化,使 Azure SQL 数据库能够自动实施这些优化建议。You can also enable automatic tuning so that Azure SQL Database can automatically implement these tuning recommendations.

Azure SQL 数据库中的 Query Performance InsightQuery Performance Insight in Azure SQL Database

Query Performance Insight 在 Azure 门户中显示针对单一数据库和共用数据库运行的、资源消耗量最高且运行时间最长的查询的性能。Query Performance Insight shows the performance in the Azure portal of top consuming and longest running queries for single and pooled databases.

数据库和弹性池低指标舍入为零Low database and elastic pool metrics rounding to zero

从 2020 年 9 月开始,使用率极低的数据库在门户中显示的使用情况可能低于实际使用情况。Starting in September 2020, databases with extremely low usage may show in the portal with less than actual usage. 由于在将双精度值转换为最接近的整数时发出遥测的方式,某些小于 0.5 的使用量将舍入为 0,这会导致发出的遥测的精度降低。Due to the way telemetry is emitted when converting a double value to the nearest integer certain usage amounts less than 0.5 will be rounded to 0, which causes a loss in granularity of the emitted telemetry.

例如:假设一个 1 分钟时段,其中包含以下 4 个数据点:0.1、0.1、0.1、0.1,这些低值向下舍入为 0、0、0、0,并显示平均值为 0。For example: Consider a 1-minute window with the following four data points: 0.1, 0.1, 0.1, 0.1, these low values are rounded down to 0, 0, 0, 0 and present an average of 0. 如果其中有数据点大于 0.5,例如:0.1、0.1、0.9、0.1,它们舍入为 0、0、1、0,并显示平均值为 0.25。If any of the data points are greater than 0.5, for example: 0.1, 0.1, 0.9, 0.1, they are rounded to 0, 0, 1, 0 and show an avg of 0.25.

受影响的数据库指标:Affected database metrics:

  • cpu_percentcpu_percent
  • log_write_percentlog_write_percent
  • workers_percentworkers_percent
  • sessions_percentsessions_percent
  • physical_data_read_percentphysical_data_read_percent
  • dtu_consumption_percent2dtu_consumption_percent2
  • xtp_storage_percentxtp_storage_percent

受影响的弹性池指标:Affected elastic pool metrics:

  • cpu_percentcpu_percent
  • physical_data_read_percentphysical_data_read_percent
  • log_write_percentlog_write_percent
  • memory_usage_percentmemory_usage_percent
  • data_storage_percentdata_storage_percent
  • peak_worker_percentpeak_worker_percent
  • peak_session_percentpeak_session_percent
  • xtp_storage_percentxtp_storage_percent
  • allocated_data_storage_percentallocated_data_storage_percent

生成性能问题的智能评估Generate intelligent assessments of performance issues

Azure SQL 数据库的智能见解和 Azure SQL 托管实例使用内置智能通过人工智能持续监视数据库使用情况,并检测导致性能不佳的干扰性事件。Intelligent Insights for Azure SQL Database and Azure SQL Managed Instance uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. Intelligent Insights 可根据查询执行等待时间、错误或超时自动检测数据库的性能问题。Intelligent Insights automatically detects performance issues with databases based on query execution wait times, errors, or time-outs. 检测到问题后,将执行详细的分析,以生成资源日志(称为 SQLInsights)和问题的智能评估Once detected, a detailed analysis is performed that generates a resource log (called SQLInsights) with an intelligent assessment of the issues. 此评估包含对数据库性能问题的根本原因分析,以及为性能改进而提供的可行性建议。This assessment consists of a root cause analysis of the database performance issue and, where possible, recommendations for performance improvements.

Intelligent Insights 是 Azure 内置智能的一项独特功能,提供以下功能价值:Intelligent Insights is a unique capability of Azure built-in intelligence that provides the following value:

  • 主动监视Proactive monitoring
  • 定制的性能见解Tailored performance insights
  • 数据库性能下降的早期检测Early detection of database performance degradation
  • 已检测出问题的根本原因分析Root cause analysis of issues detected
  • 性能改进建议Performance improvement recommendations
  • 数以十万计的数据库上的横向扩展功能Scale out capability on hundreds of thousands of databases
  • 对 DevOps 资源和总拥有成本的积极影响Positive impact to DevOps resources and the total cost of ownership

启用指标和资源日志的流式导出Enable the streaming export of metrics and resource logs

可以启用并配置将诊断遥测数据(包括智能见解资源日志)流式导出到多个目标之一。You can enable and configure the streaming export of diagnostic telemetry to one of several destinations, including the Intelligent Insights resource log.

可以配置诊断设置,以将单一数据库、共用数据库、弹性池、托管实例和实例数据库的指标和资源日志类别流式传输到以下 Azure 资源之一。You configure diagnostic settings to stream categories of metrics and resource logs for single databases, pooled databases, elastic pools, managed instances, and instance databases to one of the following Azure resources.

Azure Monitor 中的 Log Analytics 工作区Log Analytics workspace in Azure Monitor

可将指标和资源日志流式传输到 Azure Monitor 中的 Log Analytics 工作区You can stream metrics and resource logs to a Log Analytics workspace in Azure Monitor.

Azure 事件中心Azure Event Hubs

可将指标和资源日志流式传输到 Azure 事件中心You can stream metrics and resource logs to Azure Event Hubs. 将诊断遥测数据流式传输到事件中心以提供以下功能:Streaming diagnostic telemetry to event hubs to provide the following functionality:

  • 将日志流式传输到第三方日志记录和遥测系统Stream logs to third-party logging and telemetry systems

    将所有指标和资源日志流式传输到单个事件中心,以通过管道将日志数据传送到第三方 SIEM 或日志分析工具。Stream all of your metrics and resource logs to a single event hub to pipe log data to a third-party SIEM or log analytics tool.

  • 生成自定义遥测和日志记录平台Build a custom telemetry and logging platform

    可利用事件中心高度可缩放的发布-订阅功能,灵活地将指标和资源日志引入到自定义遥测平台。The highly scalable publish-subscribe nature of event hubs allows you to flexibly ingest metrics and resource logs into a custom telemetry platform.

Azure 存储Azure Storage

将指标和资源日志流式传输到 Azure 存储Stream metrics and resource logs to Azure Storage. 使用 Azure 存储存档大量诊断遥测数据,并且成本只是前两种流式传输选项的一小部分。Use Azure storage to archive vast amounts of diagnostic telemetry for a fraction of the cost of the previous two streaming options.

使用扩展事件Use extended events

此外,可以使用 SQL Server 中的扩展事件进行其他高级监视和故障排除。Additionally, you can use extended events in SQL Server for advanced monitoring and troubleshooting. 扩展事件体系结构使用户能够收集必要的数据量,以排除故障或识别性能问题。The extended events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem. 有关使用 Azure SQL 数据库中的扩展事件的信息,请参阅 Azure SQL 数据库中的扩展事件For information about using extended events in Azure SQL Database, see Extended events in Azure SQL Database.

后续步骤Next steps