Azure SQL 数据库中的监视和性能优化Monitoring and performance tuning in Azure SQL Database

若要监视 Azure SQL 数据库中某个数据库的性能,首先需要监视工作负荷使用的、相对于在选择特定服务层和性能级别所选的数据库性能级别的 CPU 和 IO 资源。To monitor the performance of a database in Azure SQL Database, 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 Data StudioSQL Server Management Studio (SSMS)。To accomplish this, Azure SQL Database emits resource metrics that can be viewed in the Azure portal or by using one of these SQL management tools: Azure Data Studio or SQL Server Management Studio (SSMS).

对于单一数据库和共用数据库,Azure SQL 数据库提供多个数据库顾问来提供智能性能优化建议以及用于提高性能的自动优化选项。For single and pooled databases, 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 Database provides additional monitoring and tuning capabilities backed by artificial intelligence to assist you in troubleshooting and maximizing the performance of your databases and solutions. 可以选择将智能见解和其他 SQL 数据库的这些资源日志与指标配置为流式导出到多个目标之一,使其可供使用和分析。You can choose to configure the streaming export of these Intelligent Insights and other SQL Database resource logs and metrics to one of several destinations for consumption and analysis. Azure SQL Analytics 是一种高级云监视解决方案,用于在单个视图中跨多个订阅大规模监视所有 Azure SQL 数据库的性能。Azure SQL Analytics is an advanced cloud monitoring solution for monitoring performance of all of your Azure SQL 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 具有自身的监视和诊断功能,以及 SQL Server 查询存储动态管理视图 (DMV)Finally, SQL has its own monitoring and diagnostic capabilities with SQL Server 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 数据库针对所有部署类型提供资源指标监视功能。In the Azure portal, Azure SQL Database provide monitoring of resource metrics for all deployment types. 此外,对于单一数据库和共用数据库,数据库顾问和 Query Performance Insight 会提供查询优化建议和查询性能分析。Additional, for single and pooled databases, database advisors and Query Performance Insight provide query tuning recommendations and query performance analysis. 最后,在 Azure 门户中,可为逻辑服务器及其单一数据库和共用数据库启用自动优化。Finally, in the Azure portal, you can enable automatic for logical servers and their single and pooled databases.

SQL 数据库资源监视SQL Database resource monitoring

可以在 Azure 门户上的“指标”视图中快速监视以下资源指标: You can quickly monitor the following resource metrics in the Azure portal in the Metrics view:

  • DTU 使用率DTU usage

    检查数据库或弹性池是否长时间接近 100% 的 DTU 使用率。Check to see if a database or elastic pool is reaching 100 percent of DTU usage for an extended period of time. 较高的 DTU 使用率表示工作负荷可能需要更多的 CPU 或 IO 资源。High DTU usage indicates that your workload might need more CPU or IO resources. 它还可能指示查询需要优化。It might also indicate queries that need to be optimized.

  • CPU 使用率CPU usage

    检查数据库、弹性池或托管实例是否长时间接近 100% 的 CPU 使用率。Check to see if a database, elastic pool, or managed instance is reaching 100 percent of CPU usage for an extended period of time. 较高的 CPU 使用率表示工作负荷可能需要更多的 CPU 或 IO 资源。High CPU indicates that your workload might need more CPU or IO resources. 它还可能指示查询需要优化。It might also indicate queries that need to be optimized.

  • IO 使用率IO usage

    检查数据库、弹性池或托管实例是否即将达到底层存储的 IO 限制。Check to see if a database, elastic pool, or managed instance is reaching the IO limits of the underlying storage. 较高的 IO 使用率表示工作负荷可能需要更多的 CPU 或 IO 资源。High IO usage indicates that your workload might need more CPU or IO resources. 它还可能指示查询需要优化。It might also indicate queries that need to be optimized.

    资源指标

数据库顾问Database advisors

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. 还可以启用自动优化,使 SQL 数据库能够自动实施这些优化建议。You can also enable automatic tuning so that SQL Database can automatically implement these tuning recommendations.

Query Performance InsightQuery Performance Insight

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.

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

Azure SQL 数据库智能见解使用内置智能通过人工智能持续监视数据库使用情况,并检测导致性能不佳的干扰性事件。Azure SQL Database Intelligent Insights uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. 智能见解可根据查询执行等待时间、错误或超时自动检测 Azure SQL 数据库中数据库的性能问题。Intelligent Insights automatically detects performance issues with databases in Azure SQL Database 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 3rd 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.

使用 SQL 数据库引擎中的扩展事件Use extended events in the SQL database engine

此外,可以使用 SQL 中的扩展事件进行其他高级监视和故障排除。Additionally, you can use extended events in SQL for additional 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 数据库中的扩展事件的信息,请参阅 SQL 数据库中的扩展事件For information about using extended events in SQL Database, see Extended events in SQL Database.

后续步骤Next steps