使用智能见解排查 Azure SQL 数据库和 Azure SQL 托管实例的性能问题Troubleshoot Azure SQL Database and Azure SQL Managed Instance performance issues with Intelligent Insights

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

本页提供有关通过智能见解资源日志检测到的 Azure SQL 数据库和 Azure SQL 托管实例性能问题的信息。This page provides information on Azure SQL Database and Azure SQL Managed Instance performance issues detected through the Intelligent Insights resource log. 可将指标和资源日志流式传输到 Azure 事件中心Azure 存储或第三方解决方案,用于自定义 DevOps 警报和报告功能。Metrics and resource logs can be streamed to Azure Event Hubs, Azure Storage, or a third-party solution for custom DevOps alerting and reporting capabilities.

可检测的数据库性能模式Detectable database performance patterns

智能见解根据查询执行等待时间、错误或超时自动检测性能问题。Intelligent Insights automatically detects performance issues based on query execution wait times, errors, or time-outs. 智能见解将检测到的性能模式输出到资源日志。Intelligent Insights outputs detected performance patterns to the resource log. 下表汇总了可检测的性能模式。Detectable performance patterns are summarized in the table below.

可检测性能模式Detectable performance patterns Azure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed Instance
达到资源限制Reaching resource limits 受监视订阅上的可用资源 (DTU)、数据库工作线程或数据库登录会话的消耗已达到其资源限制。Consumption of available resources (DTUs), database worker threads, or database login sessions available on the monitored subscription has reached its resource limits. 这会影响性能。This is affecting performance. CPU 资源的消耗达到其资源限制。Consumption of CPU resources is reaching its resource limits. 这会影响数据库性能。This is affecting the database performance.
工作负荷增大Workload increase 检测到工作负荷增大,或数据库上的工作负荷持续累积。Workload increase or continuous accumulation of workload on the database was detected. 这会影响性能。This is affecting performance. 检测到工作负荷增加。Workload increase has been detected. 这会影响数据库性能。This is affecting the database performance.
内存压力Memory pressure 已请求内存授予的辅助角色必须在统计上显着的时间内等待内存分配,否则已请求内存授予的辅助角色的累积量会增加。Workers that requested memory grants have to wait for memory allocations for statistically significant amounts of time, or an increased accumulation of workers that requested memory grants exists. 这会影响性能。This is affecting performance. 请求内存授予的工作线程会等待内存分配相当长的时间(就统计学意义来说)。Workers that have requested memory grants are waiting for memory allocations for a statistically significant amount of time. 这会影响数据库性能。This is affecting the database performance.
锁定Locking 检测到过度的数据库锁定,这影响性能。Excessive database locking was detected affecting performance. 检测到过度的数据库锁定,这影响数据库性能。Excessive database locking was detected affecting the database performance.
MAXDOP 提升Increased MAXDOP 最大并行度选项 (MAXDOP) 发生更改,影响查询执行效率。The maximum degree of parallelism option (MAXDOP) has changed affecting the query execution efficiency. 这会影响性能。This is affecting performance. 最大并行度选项 (MAXDOP) 发生更改,影响查询执行效率。The maximum degree of parallelism option (MAXDOP) has changed affecting the query execution efficiency. 这会影响性能。This is affecting performance.
Pagelatch 争用Pagelatch contention 多个线程同时尝试访问相同的内存中数据缓冲区页面,导致等待时间变长并引发 Pagelatch 争用。Multiple threads are concurrently attempting to access the same in-memory data buffer pages resulting in increased wait times and causing pagelatch contention. 这会影响性能。This is affecting performance. 多个线程同时尝试访问相同的内存中数据缓冲区页面,导致等待时间变长并引发 Pagelatch 争用。Multiple threads are concurrently attempting to access the same in-memory data buffer pages resulting in increased wait times and causing pagelatch contention. 这会影响数据库性能。This is affecting database the performance.
缺少索引Missing Index 检测到索引缺失,这影响性能。Missing index was detected affecting performance. 检测到索引缺失,这影响数据库性能。Missing index was detected affecting the database performance.
新建查询New Query 检测到新查询,这影响总体性能。New query was detected affecting the overall performance. 检测到新查询,这影响数据库的总体性能。New query was detected affecting the overall database performance.
等待时间延长统计信息Increased Wait Statistic 检测到数据库等待时间延长,这影响性能。Increased database wait times were detected affecting performance. 检测到数据库等待时间延长,这影响数据库的性能。Increased database wait times were detected affecting the database performance.
TempDB 争用TempDB Contention 多个线程尝试访问相同的 TempDB 资源,导致出现瓶颈。Multiple threads are trying to access the same TempDB resource causing a bottleneck. 这会影响性能。This is affecting performance. 多个线程尝试访问相同的 TempDB 资源,导致出现瓶颈。Multiple threads are trying to access the same TempDB resource causing a bottleneck. 这会影响数据库性能。This is affecting the database performance.
弹性池 DTU 不足Elastic pool DTU shortage 弹性池中的可用 eDTU 不足,这影响性能。Shortage of available eDTUs in the elastic pool is affecting performance. 不适用于 Azure SQL 托管实例,因为它使用 vCore 模型。Not available for Azure SQL Managed Instance as it uses the vCore model.
计划回归Plan Regression 检测到新计划,或现有计划的工作负荷发生更改。New plan, or a change in the workload of an existing plan was detected. 这会影响性能。This is affecting performance. 检测到新计划,或现有计划的工作负荷发生更改。New plan, or a change in the workload of an existing plan was detected. 这会影响数据库性能。This is affecting the database performance.
数据库范围的配置值更改Database-scoped configuration value change 检测到数据库中的配置更改,这影响数据库的性能。Configuration change on the database was detected affecting the database performance. 检测到数据库中的配置更改,这影响数据库的性能。Configuration change on the database was detected affecting the database performance.
客户端缓慢Slow client 应用程序客户端运行缓慢,无法以足够快的速度使用数据库的输出。Slow application client is unable to consume output from the database fast enough. 这会影响性能。This is affecting performance. 应用程序客户端运行缓慢,无法以足够快的速度使用数据库的输出。Slow application client is unable to consume output from the database fast enough. 这会影响数据库性能。This is affecting the database performance.
定价层降级Pricing tier downgrade 定价层降级操作减少了可用资源。Pricing tier downgrade action decreased available resources. 这会影响性能。This is affecting performance. 定价层降级操作减少了可用资源。Pricing tier downgrade action decreased available resources. 这会影响数据库性能。This is affecting the database performance.

提示

若要持续进行数据库性能优化,请启用自动优化For continuous performance optimization of databases, enable automatic tuning. 此内置智能功能持续监视数据库、自动优化索引并应用查询执行计划更正措施。This built-in intelligence feature continuously monitors your database, automatically tunes indexes, and applies query execution plan corrections.

以下部分更详细地描述了可检测性能模式。The following section describes detectable performance patterns in more detail.

达到资源限制Reaching resource limits

发生了什么What is happening

这种可检测性能模式合并了各种性能问题,涉及达到可用资源限制、工作线程限制和会话限制。This detectable performance pattern combines performance issues that are related to reaching available resource limits, worker limits, and session limits. 检测到这种性能问题之后,诊断日志的说明字段就会指示性能问题是否与资源、工作线程或会话限制相关。After this performance issue is detected, a description field of the diagnostics log indicates whether the performance issue is related to resource, worker, or session limits.

Azure SQL 数据库上的资源通常称为 DTUvCore 资源,Azure SQL 托管实例上的资源称为 vCore 资源。Resources on Azure SQL Database are typically referred to DTU or vCore resources, and resources on Azure SQL Managed Instance are referred to as vCore resources. 如果检测到的查询性能下降是由于达到所度量资源限制而造成的,则认为出现了“达到资源限制”模式。The pattern of reaching resource limits is recognized when detected query performance degradation is caused by reaching any of the measured resource limits.

会话限制资源表示数据库允许的并发登录数量。The session limits resource denotes the number of available concurrent logins to the database. 如果连接到数据库的应用程序数量达到了数据库允许的并发登录数量,则认为出现了此性能模式。This performance pattern is recognized when applications that are connected to the databases have reached the number of available concurrent logins to the database. 如果应用程序尝试使用的会话数超过了可在数据库中使用的数目,则会影响查询性能。If applications attempt to use more sessions than are available on a database, the query performance is affected.

达到工作线程限制是一种特殊的达到资源限制的情况,因为可用工作线程数不会计入 DTU 或 vCore 用量。Reaching worker limits is a specific case of reaching resource limits because available workers aren't counted in the DTU or vCore usage. 数据库中达到工作线程限制可能会导致资源特定的等待时间延长,从而导致查询性能降低。Reaching worker limits on a database can cause the rise of resource-specific wait times, which results in query performance degradation.

故障排除Troubleshooting

诊断日志会输出影响性能和资源消耗百分比的查询的查询哈希。The diagnostics log outputs query hashes of queries that affected the performance and resource consumption percentages. 可以使用此信息作为优化数据库工作负荷的入手点。You can use this information as a starting point for optimizing your database workload. 具体而言,可以通过添加索引优化那些导致性能降低的查询。In particular, you can optimize the queries that affect the performance degradation by adding indexes. 也可以让工作负荷分配更均衡,从而优化应用程序。Or you can optimize applications with a more even workload distribution. 如果无法减少工作负载或进行优化,请考虑提高数据库订阅的定价层,以增加可用的资源量。If you're unable to reduce workloads or make optimizations, consider increasing the pricing tier of your database subscription to increase the amount of resources available.

如果已达到可用会话限制,可以通过减少数据库登录次数来优化应用程序。If you have reached the available session limits, you can optimize your applications by reducing the number of logins made to the database. 如果无法减少从应用程序到数据库的登录数,请考虑提高数据库订阅的定价层。If you're unable to reduce the number of logins from your applications to the database, consider increasing the pricing tier of your database subscription. 也可以将数据库拆分成多个数据库并进行移动,使工作负荷的分配更为均衡。Or you can split and move your database into multiple databases for a more balanced workload distribution.

有关解决会话限制的更多建议,请参阅如何处理最大登录数的限制For more suggestions on resolving session limits, see How to deal with the limits of maximum logins. 有关服务器和订阅级别限制的信息,请参阅服务器上的资源限制概述See Overview of resource limits on a server for information about limits at the server and subscription levels.

工作负荷增大Workload increase

发生了什么What is happening

此性能模式可识别由于工作负荷增大(更严重者,工作负荷堆积)而导致的问题。This performance pattern identifies issues caused by a workload increase or, in its more severe form, a workload pile-up.

这项检测是通过多个指标的组合来执行的。This detection is made through a combination of several metrics. 度量的基本指标会根据以往的工作负荷基线检测工作负荷的增大情况。The basic metric measured is detecting an increase in workload compared with the past workload baseline. 另一种形式的检测基于对活动工作线程数的大幅提升的检测,这种提升率足以影响查询性能。The other form of detection is based on measuring a large increase in active worker threads that is large enough to affect the query performance.

更严重时,由于数据库无法处理工作负载,导致工作负载不断堆积。In its more severe form, the workload might continuously pile up due to the inability of a database to handle the workload. 结果就是工作负荷不断增大,即出现工作负荷堆积情况。The result is a continuously growing workload size, which is the workload pile-up condition. 因此,工作负荷等待执行的时间也增加。Due to this condition, the time that the workload waits for execution grows. 这种情况代表最严重的数据库性能问题之一。This condition represents one of the most severe database performance issues. 可通过监视已中止工作线程数的增加情况来检测此问题。This issue is detected through monitoring the increase in the number of aborted worker threads.

故障排除Troubleshooting

诊断日志会输出执行时间已延长的查询数,以及对工作负荷增加影响最大的查询的查询哈希。The diagnostics log outputs the number of queries whose execution has increased and the query hash of the query with the largest contribution to the workload increase. 可以使用此信息作为优化工作负荷的入手点。You can use this information as a starting point for optimizing the workload. 一开始可以确定对工作负荷增加影响最大的查询,这很有用。The query identified as the largest contributor to the workload increase is especially useful as your starting point.

可以考虑将工作负荷更均衡地分配到数据库。You might consider distributing the workloads more evenly to the database. 考虑通过添加索引来优化影响性能的查询。Consider optimizing the query that is affecting the performance by adding indexes. 也可将工作负荷分配到多个数据库中。You also might distribute your workload among multiple databases. 如果此类解决方案不可行,请考虑提高数据库订阅的定价层,以增加可用的资源量。If these solutions aren't possible, consider increasing the pricing tier of your database subscription to increase the amount of resources available.

内存压力Memory pressure

发生了什么What is happening

此性能模式指示由于出现内存压力(更严重者,出现内存堆积情况),当前数据库性能与过去七天的性能基线相比已降低。This performance pattern indicates degradation in the current database performance caused by memory pressure, or in its more severe form a memory pile-up condition, compared to the past seven-day performance baseline.

内存压力表示一种性能状况,即有大量的工作线程在请求内存授予。Memory pressure denotes a performance condition in which there is a large number of worker threads requesting memory grants. 大量工作线程导致高内存利用率的状况,即数据库无法有效地将内存分配给请求内存的所有工作线程。The high volume causes a high memory utilization condition in which the database is unable to efficiently allocate memory to all workers that request it. 一方面,此问题最常见的原因之一与数据库的可用内存量有关。One of the most common reasons for this issue is related to the amount of memory available to the database on one hand. 另一方面,工作负荷增加导致工作线程数增加和内存压力增大。On the other hand, an increase in workload causes the increase in worker threads and the memory pressure.

内存压力增大时,更严重者会出现内存堆积的情况。The more severe form of memory pressure is the memory pile-up condition. 这种情况表明,请求内存授予的工作线程数超出释放内存的查询数。This condition indicates that a higher number of worker threads are requesting memory grants than there are queries releasing the memory. 请求内存授予的工作线程数量还可能会不断增加(堆积),因为数据库引擎无法足够有效地分配内存以满足需求。This number of worker threads requesting memory grants also might be continuously increasing (piling up) because the database engine is unable to allocate memory efficiently enough to meet the demand. 内存堆积的情况代表最严重的数据库性能问题之一。The memory pile-up condition represents one of the most severe database performance issues.

故障排除Troubleshooting

诊断日志会输出内存对象存储详细信息,并输出已标记为内存使用率高最大原因的分配器(即工作线程)和相关时间戳。The diagnostics log outputs the memory object store details with the clerk (that is, worker thread) marked as the highest reason for high memory usage and relevant time stamps. 可以将此信息用作故障排除的基础。You can use this information as the basis for troubleshooting.

可以优化或删除与导致内存使用率最高的分配器相关的查询。You can optimize or remove queries related to the clerks with the highest memory usage. 还可以确保不查询那些不打算使用的数据。You also can make sure that you aren't querying data that you don't plan to use. 合理的做法是始终在查询中使用 WHERE 子句。Good practice is to always use a WHERE clause in your queries. 此外,建议创建非聚集索引来搜寻数据,而不是扫描数据。In addition, we recommend that you create nonclustered indexes to seek the data rather than scan it.

还可以减少工作负荷,方法是对其进行优化,或者将其分配到多个数据库。You also can reduce the workload by optimizing or distributing it over multiple databases. 也可将工作负荷分配到多个数据库中。Or you can distribute your workload among multiple databases. 如果此类解决方案不可行,请考虑提高数据库的定价层,以增加可供数据库使用的内存资源量。If these solutions aren't possible, consider increasing the pricing tier of your database to increase the amount of memory resources available to the database.

有关其他故障排除建议,请参阅 Memory grants meditation: The mysterious SQL Server memory consumer with many names(内存授予探幽:有多个名称的神秘 SQL Server 内存消耗者)。For additional troubleshooting suggestions, see Memory grants meditation: The mysterious SQL Server memory consumer with many names.

锁定Locking

发生了什么What is happening

此性能模式表示当前数据库性能降低,相比过去七天的性能基线,在这种性能情况下检测到的数据库锁定过多。This performance pattern indicates degradation in the current database performance in which excessive database locking is detected compared to the past seven-day performance baseline.

在现代 RDBMS 中,锁定对于实现多线程系统至关重要,这样可以通过尽可能地运行多个同步工作线程和并行数据库事务,最大程度地提高性能。In modern RDBMS, locking is essential for implementing multithreaded systems in which performance is maximized by running multiple simultaneous workers and parallel database transactions where possible. 此上下文中的锁定是指一种内置访问机制,即只允许单个事务以独占方式访问所需的行、页、表和文件,不允许其他事务与之争用资源。Locking in this context refers to the built-in access mechanism in which only a single transaction can exclusively access the rows, pages, tables, and files that are required and not compete with another transaction for resources. 当锁定资源进行独占使用的事务用完资源后,对这些资源的锁定就会取消,允许其他事务访问所需资源。When the transaction that locked the resources for use is done with them, the lock on those resources is released, which allows other transactions to access required resources. 有关锁定的详细信息,请参阅数据库引擎中的锁定For more information on locking, see Lock in the database engine.

如果 SQL 引擎执行的事务长时间等待访问已被锁定供独占使用的资源,这段等待时间会导致工作负荷在执行起来时变慢。If transactions executed by the SQL engine are waiting for prolonged periods of time to access resources locked for use, this wait time causes the slowdown of the workload execution performance.

故障排除Troubleshooting

诊断日志会输出锁定详细信息,可将这些信息用作故障排除的基础。The diagnostics log outputs locking details that you can use as the basis for troubleshooting. 可以分析报告的阻塞查询(即造成锁定性能降低的查询)并将其删除。You can analyze the reported blocking queries, that is, the queries that introduce the locking performance degradation, and remove them. 在某些情况下,可以成功优化阻塞查询。In some cases, you might be successful in optimizing the blocking queries.

缓解问题的最简单安全方法是保持较短的事务运行时间,并减少开销最高的查询的锁占用时间。The simplest and safest way to mitigate the issue is to keep transactions short and to reduce the lock footprint of the most expensive queries. 可以将大批操作分成小批操作。You can break up a large batch of operations into smaller operations. 合理的做法是尽量提高查询效率,减少查询时的锁定时间。Good practice is to reduce the query lock footprint by making the query as efficient as possible. 减少大型扫描,因为这些扫描会增大死锁的可能性,并对数据库总体性能造成负面影响。Reduce large scans because they increase chances of deadlocks and adversely affect overall database performance. 对于识别出的导致锁定的查询,可以通过创建新索引或将列添加到现有索引来避免表扫描。For identified queries that cause locking, you can create new indexes or add columns to the existing index to avoid the table scans.

有关更多建议,请参阅:For more suggestions, see:

增加的 MAXDOPIncreased MAXDOP

发生了什么What is happening

这种可检测的性能模式表示所选查询执行计划的并行化程度超出了预期。This detectable performance pattern indicates a condition in which a chosen query execution plan was parallelized more than it should have been. 查询优化器可以通过并行执行查询来提高工作负载性能,从而尽可能提高工作效率。The query optimizer can enhance the workload performance by executing queries in parallel to speed up things where possible. 在某些情况下,处理查询的并行工作线程等待相互同步和合并结果所花费的时间,比使用较少的并行工作线程(有时甚至是一个工作线程)执行相同查询的时间还要长。In some cases, parallel workers processing a query spend more time waiting on each other to synchronize and merge results compared to executing the same query with fewer parallel workers, or even in some cases compared to a single worker thread.

专家系统会对比基线期间来分析当前的数据库性能。The expert system analyzes the current database performance compared to the baseline period. 它会确定此前运行的某个查询是否运行得比以前更慢,因为查询执行计划的并行化程度比本来应有的程度更高。It determines if a previously running query is running slower than before because the query execution plan is more parallelized than it should be.

MAXDOP 服务器配置选项用于控制并行执行同一查询时可以使用的 CPU 核心数。The MAXDOP server configuration option is used to control how many CPU cores can be used to execute the same query in parallel.

故障排除Troubleshooting

诊断日志会输出与查询相关的查询哈希,这些查询由于并行化程度超出预期,其执行持续时间延长。The diagnostics log outputs query hashes related to queries for which the duration of execution increased because they were parallelized more than they should have been. 日志还会输出 CXP 等待时间。The log also outputs CXP wait times. 此时间表示单个组织器/协调器线程(线程 0)在合并结果并继续运行之前,等待其他所有线程完成的时间。This time represents the time a single organizer/coordinator thread (thread 0) is waiting for all other threads to finish before merging the results and moving ahead. 此外,诊断日志还会输出性能不佳的查询在执行过程中等待的总时间。In addition, the diagnostics log outputs the wait times that the poor-performing queries were waiting in execution overall. 可以将此信息用作故障排除的基础。You can use this information as the basis for troubleshooting.

首先,优化或简化复杂的查询。First, optimize or simplify complex queries. 合理的做法是将长时间运行的批处理作业分解成较小的作业。Good practice is to break up long batch jobs into smaller ones. 此外,确保创建了用于支持查询的索引。In addition, ensure that you created indexes to support your queries. 对于被标记为性能不佳的查询,也可通过手动方式强制实现最大并行度 (MAXDOP)。You can also manually enforce the maximum degree of parallelism (MAXDOP) for a query that was flagged as poor performing. 若要使用 T-SQL 配置此操作,请参阅 Configure the MAXDOP server configuration option(配置 MAXDOP 服务器配置选项)。To configure this operation by using T-SQL, see Configure the MAXDOP server configuration option.

将 MAXDOP 服务器配置选项设置为零 (0)(作为默认值)表示数据库可以使用所有可用的 CPU 核心来并行化线程以执行单个查询。Setting the MAXDOP server configuration option to zero (0) as a default value denotes that database can use all available CPU cores to parallelize threads for executing a single query. 将 MAXDOP 设置为一 (1) 表示只能将一个核心用于单个查询的执行。Setting MAXDOP to one (1) denotes that only one core can be used for a single query execution. 实际上,这意味着并行功能处于关闭状态。In practical terms, this means that parallelism is turned off. 根据具体的情况、数据库的可用核心数与诊断日志信息,可将 MAXDOP 选项优化成用于并行查询执行且能够解决具体问题的核心数。Depending on the case-per-case basis, available cores to the database, and diagnostics log information, you can tune the MAXDOP option to the number of cores used for parallel query execution that might resolve the issue in your case.

Pagelatch 争用Pagelatch contention

发生了什么What is happening

此性能模式表示由于 Pagelatch 争用,当前数据库工作负荷的性能与过去七天的工作负荷基线相比有所降级。This performance pattern indicates the current database workload performance degradation due to pagelatch contention compared to the past seven-day workload baseline.

闩锁是用于启用多线程处理的轻量同步机制。Latches are lightweight synchronization mechanisms used to enable multithreading. 该机制可保证内存中结构(包括索引、数据页以及其他内部结构)的一致性。They guarantee consistency of in-memory structures that include indices, data pages, and other internal structures.

有多种类型的闩锁可用。There are many types of latches available. 为了简单起见,可以使用缓冲区闩锁来保护缓冲池中的内存中页。For simplicity purposes, buffer latches are used to protect in-memory pages in the buffer pool. IO 闩锁用于保护尚未加载到缓冲池中的页。IO latches are used to protect pages not yet loaded into the buffer pool. 每当在缓冲池的页中写入或读取数据时,工作线程需要首先获取该页的缓冲区闩锁。Whenever data is written to or read from a page in the buffer pool, a worker thread needs to acquire a buffer latch for the page first. 每当工作线程尝试访问尚未在内存中缓冲池中提供的页时,就会发出 IO 请求,以便从存储中加载所需的信息。Whenever a worker thread attempts to access a page that isn't already available in the in-memory buffer pool, an IO request is made to load the required information from the storage. 这种事件序列指示更严重形式的性能降低。This sequence of events indicates a more severe form of performance degradation.

当多个线程同时尝试获取同一内存中结构上的闩锁,从而造成查询执行的等待时间延长时,就会发生页闩锁争用。Contention on the page latches occurs when multiple threads concurrently attempt to acquire latches on the same in-memory structure, which introduces an increased wait time to query execution. 如果在需要从存储访问数据时发生 Pagelatch IO 争用,这段等待时间甚至会更长,In the case of pagelatch IO contention, when data needs to be accessed from storage, this wait time is even larger. 并可能会显著影响工作负荷性能。It can affect workload performance considerably. Pagelatch 争用是线程相互等待,在多个 CPU 系统上争用资源的最常见情景。Pagelatch contention is the most common scenario of threads waiting on each other and competing for resources on multiple CPU systems.

故障排除Troubleshooting

诊断日志输出 Pagelatch 争用详细信息。The diagnostics log outputs pagelatch contention details. 可以将此信息用作故障排除的基础。You can use this information as the basis for troubleshooting.

由于 Pagelatch 是一种内部控制机制,因此会自动确定何时使用它们。Because a pagelatch is an internal control mechanism, it automatically determines when to use them. 应用程序决策(包括架构设计)可以影响因闩锁的确定性行为而导致的 Pagelatch 行为。Application decisions, including schema design, can affect pagelatch behavior due to the deterministic behavior of latches.

处理闩锁争用的一个方法是将有序索引键替换为无序键,以便在索引范围内均匀分配插入内容。One method for handling latch contention is to replace a sequential index key with a nonsequential key to evenly distribute inserts across an index range. 通常情况下,索引中的前导列可按比例分配工作负荷。Typically, a leading column in the index distributes the workload proportionally. 可考虑的另一种方法是表分区。Another method to consider is table partitioning. 在分区表中创建一个包含计算列的哈希分区方案,是缓解过度闩锁争用的常用方法。Creating a hash partitioning scheme with a computed column on a partitioned table is a common approach for mitigating excessive latch contention. 如果发生 Pagelatch IO 争用,可以引入索引来缓解此性能问题。In the case of pagelatch IO contention, introducing indexes helps to mitigate this performance issue.

有关详细信息,请参阅 Diagnose and resolve latch contention on SQL Server(诊断和解决 SQL Server 上的闩锁争用)(PDF 下载)。For more information, see Diagnose and resolve latch contention on SQL Server (PDF download).

缺少索引Missing index

发生了什么What is happening

此性能模式表示由于缺少索引,当前数据库工作负荷的性能与过去七天的基线相比有所降级。This performance pattern indicates the current database workload performance degradation compared to the past seven-day baseline due to a missing index.

索引用于加速查询性能。An index is used to speed up the performance of queries. 使用索引可以减少需要访问或扫描的数据集页数,因此能够快速访问表数据。It provides quick access to table data by reducing the number of dataset pages that need to be visited or scanned.

可以通过此项检测来识别导致性能降低的特定查询。创建索引对性能有利。Specific queries that caused performance degradation are identified through this detection for which creating indexes would be beneficial to the performance.

故障排除Troubleshooting

诊断日志会输出查询的查询哈希,这些查询已确定会影响工作负荷性能。The diagnostics log outputs query hashes for the queries that were identified to affect the workload performance. 可以为这些查询生成索引。You can build indexes for these queries. 还可以优化查询,或者删除不需要的那些查询。You also can optimize or remove these queries if they aren't required. 在性能方面,合理的做法是避免查询不使用的数据。A good performance practice is to avoid querying data that you don't use.

提示

你是否知道,内置智能可以自动管理数据库的最佳性能索引?Did you know that built-in intelligence can automatically manage the best-performing indexes for your databases?

若要持续进行性能优化,建议启用自动优化For continuous performance optimization, we recommend that you enable automatic tuning. 此独特的内置智能功能持续监视数据库,并为数据库自动优化和创建索引。This unique built-in intelligence feature continuously monitors your database and automatically tunes and creates indexes for your databases.

新建查询New query

发生了什么What is happening

这种性能模式表示检测到一个性能不佳并影响工作负荷性能(与七天性能基线相比)的新查询。This performance pattern indicates that a new query is detected that is performing poorly and affecting the workload performance compared to the seven-day performance baseline.

有时,编写性能良好的查询很有难度。Writing a good-performing query sometimes can be a challenging task. 有关编写查询的详细信息,请参阅 Writing SQL queries(编写 SQL 查询)。For more information on writing queries, see Writing SQL queries. 若要优化现有的查询性能,请参阅查询优化To optimize existing query performance, see Query tuning.

故障排除Troubleshooting

诊断日志会输出最多两个 CPU 消耗量最大的新查询的信息,包括其查询哈希。The diagnostics log outputs information up to two new most CPU-consuming queries, including their query hashes. 由于检测到的查询影响工作负荷性能,可以优化查询。Because the detected query affects the workload performance, you can optimize your query. 最好是只检索需要使用的数据。Good practice is to retrieve only data you need to use. 另外,建议使用带 WHERE 子句的查询,We also recommend using queries with a WHERE clause. 并简化复杂的查询,将其分解成更小的查询。We also recommend that you simplify complex queries and break them up into smaller queries. 将大批查询分解成小批查询也是好办法。Another good practice is to break down large batch queries into smaller batch queries. 为新查询引入索引通常是缓解此性能问题的好办法。Introducing indexes for new queries is typically a good practice to mitigate this performance issue.

在 Azure SQL 数据库中,请考虑使用查询性能见解In Azure SQL Database, consider using Query Performance Insight.

等待时间延长统计信息Increased wait statistic

发生了什么What is happening

这种可检测的性能模式表示确定的性能不佳的查询导致工作负荷的性能相比过去七天的工作负荷基线有所降级。This detectable performance pattern indicates a workload performance degradation in which poor-performing queries are identified compared to the past seven-day workload baseline.

在这种情况下,系统无法将性能不佳的查询归到任何其他标准的可检测性能类别下,但确实检测到导致性能回归的等待统计信息。In this case, the system can't classify the poor-performing queries under any other standard detectable performance categories, but it detected the wait statistic responsible for the regression. 因此,系统会将其视为具有等待时间延长统计信息的查询,并会在其中公开导致性能回归的等待时间统计信息。Therefore, it considers them as queries with increased wait statistics, where the wait statistic responsible for the regression is also exposed.

故障排除Troubleshooting

诊断日志会输出等待时间延长详情、受影响查询的查询哈希的信息。The diagnostics log outputs information on increased wait time details and query hashes of the affected queries.

由于系统无法成功识别查询性能不佳的根本原因,在手动故障排除时,诊断信息是很好的入手点。Because the system couldn't successfully identify the root cause for the poor-performing queries, the diagnostics information is a good starting point for manual troubleshooting. 可以优化这些查询的性能。You can optimize the performance of these queries. 合理的做法是只提取需使用的数据,简化复杂的查询,将其分解成较小的查询。A good practice is to fetch only data you need to use and to simplify and break down complex queries into smaller ones.

有关优化查询性能的详细信息,请参阅查询优化For more information on optimizing query performance, see Query tuning.

TempDB 争用TempDB contention

发生了什么What is happening

这种可检测的性能模式表示这样一种数据库性能状况:尝试访问 tempDB 资源的线程存在瓶颈。This detectable performance pattern indicates a database performance condition in which a bottleneck of threads trying to access tempDB resources exists. (这种状况与 IO 不相关。)此性能问题的典型情景是数百个并发查询都在创建、使用然后删除小型 tempDB 表。(This condition isn't IO related.) The typical scenario for this performance issue is hundreds of concurrent queries that all create, use, and then drop small tempDB tables. 系统已检测到使用相同 tempDB 表的并发请求数出现了统计学意义上的大幅增长,影响了数据库的性能(与过去七天的性能基线相比)。The system detected that the number of concurrent queries using the same tempDB tables increased with sufficient statistical significance to affect database performance compared to the past seven-day performance baseline.

故障排除Troubleshooting

诊断日志输出 tempDB 争用详细信息。The diagnostics log outputs tempDB contention details. 可以将此信息用作故障排除的入手点。You can use the information as the starting point for troubleshooting. 可以通过两项操作来减轻此类争用并提高整个工作负荷的吞吐量:一是停止使用临时表,There are two things you can pursue to alleviate this kind of contention and increase the throughput of the overall workload: You can stop using the temporary tables. 二是使用内存优化表。You also can use memory-optimized tables.

有关详细信息,请参阅内存优化表简介For more information, see Introduction to memory-optimized tables.

弹性池 DTU 不足Elastic pool DTU shortage

发生了什么What is happening

此可检测的性能模式表示当前数据库工作负荷的性能与过去七天的基线相比有所降级。This detectable performance pattern indicates a degradation in the current database workload performance compared to the past seven-day baseline. 这是由于在订阅的弹性池中缺少可用的 DTU。It's due to the shortage of available DTUs in the elastic pool of your subscription.

Azure 弹性池资源用作在多个数据库之间共享的可用资源,以实现缩放。Azure elastic pool resources are used as a pool of available resources shared between multiple databases for scaling purposes. 如果弹性池中的可用 eDTU 资源不够大,无法支持池中的所有数据库,则系统就会检测到“弹性池 DTU 不足”性能问题。When available eDTU resources in your elastic pool aren't sufficiently large to support all the databases in the pool, an elastic pool DTU shortage performance issue is detected by the system.

故障排除Troubleshooting

诊断日志会输出有关弹性池的信息,列出 DTU 消耗量最大的数据库,并提供消耗量最大的数据库所使用的池 DTU 的百分比。The diagnostics log outputs information on the elastic pool, lists the top DTU-consuming databases, and provides a percentage of the pool's DTU used by the top-consuming database.

由于性能状况与使用弹性池中同一池 eDTU 的多个数据库相关,因此故障排除步骤侧重于 DTU 消耗量最大的数据库。Because this performance condition is related to multiple databases using the same pool of eDTUs in the elastic pool, the troubleshooting steps focus on the top DTU-consuming databases. 可以减少消耗量最大的数据库的工作负荷,包括优化这些数据库中消耗量最大的查询。You can reduce the workload on the top-consuming databases, which includes optimization of the top-consuming queries on those databases. 还可以确保不查询那些不使用的数据。You also can ensure that you aren't querying data that you don't use. 另一种做法是使用 DTU 消耗量最大的数据库来优化应用程序,在多个数据库之间重新分配工作负荷。Another approach is to optimize applications by using the top DTU-consuming databases and redistribute the workload among multiple databases.

如果无法减少和优化 DTU 消耗量最大的数据库中的当前工作负荷,可以考虑提高弹性池定价层。If reduction and optimization of the current workload on your top DTU-consuming databases aren't possible, consider increasing your elastic pool pricing tier. 提高定价层可以增加弹性池中的可用 DTU。Such increase results in the increase of the available DTUs in the elastic pool.

计划回归Plan regression

发生了什么What is happening

这种可检测的性能模式意味着数据库使用的不是最优的查询执行计划。This detectable performance pattern denotes a condition in which the database utilizes a suboptimal query execution plan. 欠佳的计划通常会导致查询执行时间延长,从而导致当前查询和其他查询的等待时间变长。The suboptimal plan typically causes increased query execution, which leads to longer wait times for the current and other queries.

数据库引擎确定查询执行开销最低的查询执行计划。The database engine determines the query execution plan with the least cost to a query execution. 由于查询和工作负载的类型会发生变化,有时现有的计划不再有效,或者数据库引擎未能做出合理的评估。As the type of queries and workloads change, sometimes the existing plans are no longer efficient, or perhaps the database engine didn't make a good assessment. 作为一种纠正措施,可以手动强制查询执行计划。As a matter of correction, query execution plans can be manually forced.

这种可检测的性能模式合并了三种不同的计划回归情况:新计划回归、旧计划回归和现有计划更改的工作负荷。This detectable performance pattern combines three different cases of plan regression: new plan regression, old plan regression, and existing plans changed workload. 诊断日志的“详细信息”属性中提供了出现的计划回归的特定类型。The particular type of plan regression that occurred is provided in the details property in the diagnostics log.

新计划回归状况表示一种状态,即数据库引擎开始执行不如旧计划有效的新查询执行计划。The new plan regression condition refers to a state in which the database engine starts executing a new query execution plan that isn't as efficient as the old plan. 旧计划回归状况表示一种状态,即数据库引擎弃用更有效的新计划,改用不如新计划有效的旧计划。The old plan regression condition refers to the state when the database engine switches from using a new, more efficient plan to the old plan, which isn't as efficient as the new plan. 现有计划更改的工作负荷回归表示这样一种状态:不断交替使用旧计划和新计划,天平逐渐倾向于性能不佳的计划。The existing plans changed workload regression refers to the state in which the old and the new plans continuously alternate, with the balance going more toward the poor-performing plan.

有关计划回归的详细信息,请参阅 What is plan regression in SQL Server?(SQL Server 中的计划回归是什么?)。For more information on plan regressions, see What is plan regression in SQL Server?.

故障排除Troubleshooting

诊断日志输出查询哈希、正确计划 ID、错误计划 ID 和查询 ID。The diagnostics log outputs the query hashes, good plan ID, bad plan ID, and query IDs. 可以将此信息用作故障排除的基础。You can use this information as the basis for troubleshooting.

可以使用提供的查询哈希来分析哪个计划对于可识别的特定查询而言性能更好。You can analyze which plan is better performing for your specific queries that you can identify with the query hashes provided. 确定哪个计划更适合自己的查询后,可以手动强制该计划。After you determine which plan works better for your queries, you can manually force it.

有关详细信息,请参阅 Learn how SQL Server prevents plan regressions(了解 SQL Server 如何阻止计划回归)。For more information, see Learn how SQL Server prevents plan regressions.

提示

是否知道,内置智能功能可以自动管理数据库的最佳查询执行计划?Did you know that the built-in intelligence feature can automatically manage the best-performing query execution plans for your databases?

若要持续进行性能优化,建议启用自动优化For continuous performance optimization, we recommend that you enable automatic tuning. 此内置智能功能持续监视数据库,并自动优化和创建数据库的最佳性能查询执行计划。This built-in intelligence feature continuously monitors your database and automatically tunes and creates best-performing query execution plans for your databases.

数据库范围的配置值更改Database-scoped configuration value change

发生了什么What is happening

这种可检测的性能模式表示数据库范围的配置发生更改,导致检测到性能回归(与过去七天的数据库工作负荷行为相比)。This detectable performance pattern indicates a condition in which a change in the database-scoped configuration causes performance regression that is detected compared to the past seven-day database workload behavior. 此模式意味着,最近对数据库范围的配置所做的更改似乎对数据库性能不利。This pattern denotes that a recent change made to the database-scoped configuration doesn't seem to be beneficial to your database performance.

可以针对每个数据库设置数据库范围的配置更改。Database-scoped configuration changes can be set for each individual database. 根据具体的情况使用此配置可以优化数据库的个体性能。This configuration is used on a case-by-case basis to optimize the individual performance of your database. 可以为每个单独的数据库配置以下选项:MAXDOP、LEGACY_CARDINALITY_ESTIMATION、PARAMETER_SNIFFING、QUERY_OPTIMIZER_HOTFIXES 和 CLEAR PROCEDURE_CACHE。The following options can be configured for each individual database: MAXDOP, LEGACY_CARDINALITY_ESTIMATION, PARAMETER_SNIFFING, QUERY_OPTIMIZER_HOTFIXES, and CLEAR PROCEDURE_CACHE.

故障排除Troubleshooting

诊断日志会输出最近进行的、导致性能相比过去七天的工作负荷行为有所降级的数据库范围配置更改。The diagnostics log outputs database-scoped configuration changes that were made recently that caused performance degradation compared to the previous seven-day workload behavior. 可以将配置更改还原为以前的值。You can revert the configuration changes to the previous values. 也可对值逐个进行优化,直到达到所需性能级别。You also can tune value by value until the desired performance level is reached. 可以从性能令人满意的类似数据库中复制数据库范围配置值。You can copy database-scope configuration values from a similar database with satisfactory performance. 如果无法排查性能问题,请还原为默认值,并尝试从此基线开始进行微调。If you're unable to troubleshoot the performance, revert to the default values and attempt to fine-tune starting from this baseline.

有关优化数据库范围配置的详细信息以及更改配置时使用的 T-SQL 语法,请参阅 Alter database-scoped configuration (Transact-SQL)(更改数据库范围的配置 (Transact SQL))。For more information on optimizing database-scoped configuration and T-SQL syntax on changing the configuration, see Alter database-scoped configuration (Transact-SQL).

客户端缓慢Slow client

发生了什么What is happening

这种可检测的性能模式表示,使用数据库的客户端使用数据库输出的速度赶不上数据库发送结果的速度。This detectable performance pattern indicates a condition in which the client using the database can't consume the output from the database as fast as the database sends the results. 由于数据库不会在缓冲区中存储所执行查询的结果,因此它的速度会变慢并等待客户端使用传输的查询输出,然后再继续。Because the database isn't storing results of the executed queries in a buffer, it slows down and waits for the client to consume the transmitted query outputs before proceeding. 此状况也可能与网络相关,即该网络无法以足够快的速度将输出从数据库传输到使用方客户端。This condition also might be related to a network that isn't sufficiently fast enough to transmit outputs from the database to the consuming client.

仅当检测到性能回归(与过去七天的数据库工作负荷行为相比)时,才会产生这种状况。This condition is generated only if a performance regression is detected compared to the past seven-day database workload behavior. 仅当性能与过去的性能行为相比,在统计学上有显著的降级时,才会检测到这种性能问题。This performance issue is detected only if a statistically significant performance degradation occurs compared to previous performance behavior.

故障排除Troubleshooting

这种可检测的性能模式指示客户端的情况。This detectable performance pattern indicates a client-side condition. 必须在客户端应用程序或客户端网络上进行故障排除。Troubleshooting is required at the client-side application or client-side network. 诊断日志会输出在过去两小时内让客户端等待最长时间来使用的查询哈希和等待时间。The diagnostics log outputs the query hashes and wait times that seem to be waiting the most for the client to consume them within the past two hours. 可以将此信息用作故障排除的基础。You can use this information as the basis for troubleshooting.

可以优化应用程序在使用这些查询时的性能。You can optimize performance of your application for consumption of these queries. 还可以考虑可能的网络延迟问题。You also can consider possible network latency issues. 由于性能降低问题的检出是基于最近七天性能基线的更改,因此可以调查此性能回归事件是否因最近的应用程序或网络状况变化而导致。Because the performance degradation issue was based on change in the last seven-day performance baseline, you can investigate whether recent application or network condition changes caused this performance regression event.

定价层降级Pricing tier downgrade

发生了什么What is happening

这种可检测的性能模式表示数据库订阅的定价层已降级。This detectable performance pattern indicates a condition in which the pricing tier of your database subscription was downgraded. 由于可供数据库使用的资源 (DTU) 减少,系统检测到当前数据库性能相比过去七天的基线有所下降。Because of reduction of resources (DTUs) available to the database, the system detected a drop in the current database performance compared to the past seven-day baseline.

此外,可能会出现数据库订阅的定价层降级后,又在短时间内升级到更高层的情况。In addition, there could be a condition in which the pricing tier of your database subscription was downgraded and then upgraded to a higher tier within a short period of time. 检测到这种暂时性的性能降低时,会将检测结果作为定价层降级和升级输出到诊断日志的 details 节中。Detection of this temporary performance degradation is outputted in the details section of the diagnostics log as a pricing tier downgrade and upgrade.

故障排除Troubleshooting

如果降低了定价层,因而减少了可用的 DTU 数,但你对性能感到满意,则不需采取任何措施。If you reduced your pricing tier, and therefore the DTUs available, and you're satisfied with the performance, there's nothing you need to do. 如果降低定价层后对数据库的性能不满意,请减少数据库工作负载,或考虑将定价层提升到更高的级别。If you reduced your pricing tier and you're unsatisfied with your database performance, reduce your database workloads or consider increasing the pricing tier to a higher level.

后续步骤Next steps