Azure SQL 数据库中的高 CPU 利用率诊断和故障排除

  • 项目

适用于：Azure SQL 数据库

Azure SQL 数据库提供了内置工具，用于确定高 CPU 使用率的原因并优化工作负载性能。 可以使用这些工具在高 CPU 使用率出现时，或在事件完成后以被动方式对该问题进行故障排除。 还可以启用自动优化，以便随着时间推移主动降低数据库的 CPU 使用率。 本文说明如何在 Azure SQL 数据库中使用内置工具对高 CPU 使用率问题进行诊断和故障排除，并说明何时添加 CPU 资源。

了解 vCore 计数

诊断高 CPU 事件时，了解可供数据库使用的虚拟核心 (vCore) 数会十分有用。 vCore 等效于逻辑 CPU。 vCore 数有助于了解数据库可用的 CPU 资源。

在 Azure 门户中确定 vCore 计数

如果将基于 vCore 的服务层级与预配计算层一起使用，则可以在 Azure 门户中快速确定数据库的 vCore 计数。 在这种情况下，其“概述”页面上为数据库列出的定价层会包含 vCore 计数 。 例如，某个数据库的定价层可能是“常规用途：标准系列 (Gen5)，16 个 vCore”。

对于无服务器计算层中的数据库，vCore 计数始终等于数据库的最大 vCore 设置。 VCore 计数会显示在“概述”页面上为数据库列出的定价层中 。 例如，某个数据库的定价层可能是“常规用途：无服务器，标准系列 (Gen5)，16 个 vCore”。

如果在基于 DTU 的购买模型中使用数据库，则需要使用 Transact-SQL 查询数据库的 vCore 计数。

使用 Transact-SQL 确定 vCore 计数

可以使用 Transact-SQL 确定任何数据库的当前 vCore 计数。 可以使用 SQL Server Management Studio (SSMS)、Azure Data Studio 或 Azure 门户查询编辑器对 Azure SQL 数据库运行 Transact-SQL 查询。

连接到数据库并运行以下查询：

SELECT 
    COUNT(*) as vCores
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE';
GO

确定高 CPU 利用率的原因

可以使用 Azure 门户、SSMS 中的查询存储交互式工具以及 SSMS 和 Azure Data Studio 中的 Transact-SQL 查询来测量和分析 CPU 利用率。

Azure 门户和查询存储会显示已完成查询的执行统计信息，例如 CPU 指标。 如遇到可能由一个或多个正在进行的长时间运行查询所导致的当前高 CPU 利用率事件，请使用 Transact-SQL 确定当前正在运行的查询。

新的和异常的高 CPU 利用率的常见原因包括：

  • 工作负载中使用大量 CPU 的新查询。
  • 定期运行查询的频率提高。
  • 查询计划回归（包括由于参数敏感计划 (PSP) 问题导致的回归），从而导致一个或多个查询占用较多 CPU。
  • 查询计划的编译或重新编译显著增加。
  • 其中的查询使用过多并行的数据库。

若要了解导致高 CPU 利用率事件的原因，请确定对数据库出现高 CPU 利用率的时间，以及当时使用 CPU 的排名靠前查询。

检查：

注意

Azure SQL 数据库需要计算资源来实现核心服务功能，例如高可用性和灾难恢复、数据库备份和还原、监视、查询存储、自动优化等。在 vCore 计数较低的数据库上或密集弹性池中的数据库上，使用这些计算资源可能特别值得注意。 在 Azure SQL Database 中的资源管理中了解详细信息。

使用 Azure 门户可跟踪各种 CPU 指标，包括数据库在一段时间内使用的可用 CPU 百分比。 Azure 门户将 CPU 指标与来自数据库查询存储的信息相结合，这使你可确定数据库中在给定时间使用 CPU 的查询。

按照以下步骤得到 CPU 百分比指标。

  1. 在 Azure 门户中导航到数据库。
  2. 在左侧菜单的“智能性能”下，选择“Query Performance Insight” .。

Query Performance Insight 的默认视图显示 24 小时的数据。 CPU 使用率显示为用于数据库的总可用 CPU 百分比。

该期间运行的排名靠前的五个查询会显示在 CPU 使用率图上方的垂直条中。 在图表上选择一段时间，或使用“自定义”菜单浏览特定时间段。 此外，还可以增加显示的查询数。

Screenshot shows Query Performance Insight in the Azure portal.

选择展示高 CPU 使用率的每个查询 ID，以打开相应查询的详细信息。 详细信息包括查询文本以及查询的性能历史记录。 检查查询的 CPU 使用率最近是否增加。

记下查询 ID，以在下一部分使用查询存储进一步调查查询计划。

查看在 Azure 门户中确定的排名靠前查询的查询计划

按照以下步骤在 SSMS 的交互式查询存储工具中使用查询 ID 检查查询在一段时间内的执行计划。

  1. 打开 SSMS。
  2. 在对象资源管理器中连接到 Azure SQL 数据库。
  3. 在对象资源管理器中展开数据库节点。
  4. 站到“查询存储”文件夹。
  5. 打开“跟踪的查询”窗格。
  6. 在屏幕左上方的“跟踪查询”框中输入查询 ID，然后按 Enter。
  7. 如有必要，请选择“配置”以调整时间间隔，以匹配出现高 CPU 利用率的时间。

该页面会显示最近 24 小时内查询的执行计划和相关指标。

使用 Transact-SQL 确定当前正在运行的查询

Transact-SQL 允许确定当前正在运行的查询以及迄今为止使用的 CPU 时间。 还可使用 Transact-SQL 查询数据库中的最近 CPU 使用率、按 CPU 使用率排名靠前的查询以及最常编译的查询。

可以使用 SQL Server Management Studio (SSMS)、Azure Data Studio 或 Azure 门户查询编辑器查询 CPU 指标。 使用 SSMS 或 Azure Data Studio 时，请打开一个新的查询窗口，将其连接到数据库（不是 master 数据库）。

通过执行以下查询，使用 CPU 使用率和执行计划查找当前正在运行的查询。 CPU 时间以毫秒为单位返回。

SELECT
    req.session_id,
    req.status,
    req.start_time,
    req.cpu_time AS 'cpu_time_ms',
    req.logical_reads,
    req.dop,
    s.login_name,
    s.host_name,
    s.program_name,
    object_name(st.objectid,st.dbid) 'ObjectName',
    REPLACE (REPLACE (SUBSTRING (st.text,(req.statement_start_offset/2) + 1,
        ((CASE req.statement_end_offset    WHEN -1    THEN DATALENGTH(st.text) 
        ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1),
        CHAR(10), ' '), CHAR(13), ' ') AS statement_text,
    qp.query_plan,
    qsx.query_plan as query_plan_with_in_flight_statistics
FROM sys.dm_exec_requests as req  
JOIN sys.dm_exec_sessions as s on req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as st
OUTER APPLY sys.dm_exec_query_plan(req.plan_handle) as qp
OUTER APPLY sys.dm_exec_query_statistics_xml(req.session_id) as qsx
ORDER BY req.cpu_time desc;
GO

此查询返回执行计划的两个副本。 列 query_plan 包含来自 sys.dm_exec_query_plan 的执行计划。 此版本的查询计划仅包含行计数的估计值，不包含任何执行统计信息。

如果列 query_plan_with_in_flight_statistics 返回执行计划，则此计划会提供详细信息。 query_plan_with_in_flight_statistics 列从 sys.dm_exec_query_statistics_xml 返回数据，其中包括“正在进行”的执行统计信息，例如当前正在运行的查询到目前为止返回的实际行数。

查看过去一小时的 CPU 使用率指标

针对 sys.dm_db_resource_stats 的以下查询会返回过去大约一小时的平均 CPU 使用率（按 15 秒间隔）。

SELECT
    end_time,
    avg_cpu_percent,
    avg_instance_cpu_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC; 
GO

不仅关注 avg_cpu_percent 列十分重要。 avg_instance_cpu_percent 列包括用户和内部工作负载使用的 CPU。 如果 avg_instance_cpu_percent 接近 100%，CPU 资源会饱和。 在这种情况下，如果应用吞吐量不足或查询延迟较高，应对高 CPU 使用率问题进行故障排除。

在 Azure SQL Database 中的资源管理中了解详细信息。

请查看 sys.dm_db_resource_stats 中的示例以了解更多查询。

按 CPU 使用率查询排名靠前的最近 15 个查询

查询存储会跟踪查询的执行统计信息，包括 CPU 使用率。 下面的查询返回过去 2 小时内运行的排名靠前的 15 个查询（按 CPU 使用率排序）。 CPU 时间以毫秒为单位返回。

WITH AggregatedCPU AS 
    (SELECT
        q.query_hash, 
        SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms, 
        SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_ms, 
        MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms, 
        MAX(max_logical_io_reads) max_logical_reads, 
        COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, 
        COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, 
        SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS aborted_execution_count, 
        SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS regular_execution_count, 
        SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS exception_execution_count, 
        SUM(count_executions) AS total_executions, 
        MIN(qt.query_sql_text) AS sampled_query_text
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
    JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
    WHERE 
            rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception') AND 
        rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
     GROUP BY q.query_hash), 
OrderedCPU AS 
    (SELECT *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN
    FROM AggregatedCPU)
SELECT *
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_ms DESC;
GO

此查询按查询的哈希值进行分组。 如果在 number_of_distinct_query_ids 列中发现较高值，请调查频繁运行的查询是否未正确进行参数化。 非参数化查询可能会在每次执行时进行编译，这会占用大量 CPU，并影响查询存储性能。

若要详细了解单个查询，请记下查询哈希，并使用它确定给定查询哈希的 CPU 使用率和查询计划。

按查询哈希查询最常编译的查询

编译查询计划是占用大量 CPU 的过程。 Azure SQL 数据库好将计划缓存在内存中以重复使用。 如果未进行参数化，或者如果 RECOMPILE 提示强制重新编译，则可能会经常编译某些查询。

查询存储会跟踪编译查询的次数。 运行以下查询，以在查询存储中确定按编译计数排名靠前的 20 个查询，以及每分钟的平均编译数：

SELECT TOP (20)
    query_hash,
    MIN(initial_compile_start_time) as initial_compile_start_time,
    MAX(last_compile_start_time) as last_compile_start_time,
    CASE WHEN DATEDIFF(mi,MIN(initial_compile_start_time), MAX(last_compile_start_time)) > 0
        THEN 1.* SUM(count_compiles) / DATEDIFF(mi,MIN(initial_compile_start_time), 
            MAX(last_compile_start_time)) 
        ELSE 0 
        END as avg_compiles_minute,
    SUM(count_compiles) as count_compiles
FROM sys.query_store_query AS q
GROUP BY query_hash
ORDER BY count_compiles DESC;
GO

若要详细了解单个查询，请记下查询哈希，并使用它确定给定查询哈希的 CPU 使用率和查询计划。

确定给定查询哈希的 CPU 使用率和查询计划

运行以下查询，以获得给定 query_hash 的单个查询 ID、查询文本和查询执行计划。 CPU 时间以毫秒为单位返回。

将 @query_hash 变量的值替换为工作负载的有效 query_hash。

declare @query_hash binary(8);

SET @query_hash = 0x6557BE7936AA2E91;

with query_ids as (
    SELECT
        q.query_hash,
        q.query_id,
        p.query_plan_hash,
        SUM(qrs.count_executions) * AVG(qrs.avg_cpu_time)/1000. as total_cpu_time_ms,
        SUM(qrs.count_executions) AS sum_executions,
        AVG(qrs.avg_cpu_time)/1000. AS avg_cpu_time_ms
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p on q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats qrs on p.plan_id = qrs.plan_id
    WHERE q.query_hash = @query_hash
    GROUP BY q.query_id, q.query_hash, p.query_plan_hash)
SELECT qid.*,
    qt.query_sql_text,
    p.count_compiles,
    TRY_CAST(p.query_plan as XML) as query_plan
FROM query_ids as qid
JOIN sys.query_store_query AS q ON qid.query_id=q.query_id
JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON qid.query_id=p.query_id and qid.query_plan_hash=p.query_plan_hash
ORDER BY total_cpu_time_ms DESC;
GO

此查询为 query_hash 在整个查询存储历史记录中的每个执行计划变体返回一行。 结果会按总 CPU 时间进行排序。

使用交互式查询存储工具跟踪历史 CPU 利用率

如果希望使用图形工具，请按照以下步骤使用 SSMS 中的交互式查询存储工具。

  1. 打开 SSMS，然后连接到对象资源管理器中的数据库。
  2. 在对象资源管理器中展开数据库节点
  3. 站到“查询存储”文件夹。
  4. 打开“总体资源消耗”窗格。

上个月中数据库的总 CPU 时间（以毫秒为单位）会显示在该窗格左下部分。 在默认视图中，CPU 时间按天聚合。

Screenshot shows the Overall Resource Consumption view of Query Store in SSMS.

选择窗格右上方的“配置”以选择不同的时间段。 还可以更改聚合单位。 例如，可以选择查看特定日期范围的数据，并按小时聚合数据。

使用交互式查询存储工具按 CPU 时间确定排名靠前的查询

在图表中选择进行深化的条形，并查看在特定时间段内运行的查询。 “资源使用排名靠前的查询”窗格会打开。 或者，可以直接在对象资源管理器中从数据库下的“查询存储”节点打开“资源使用排名靠前的查询”。

Screenshot shows the Top Resource Consuming Queries pane for Query Store in S S M S.

在默认视图中，“资源使用排名靠前的查询”窗格会按“持续时间(毫秒)”显示查询 。 持续时间有时可能低于 CPU 时间：使用并行的查询使用的 CPU 时间可能比其总体持续时间多得多。 如果等待时间很长，则持续时间也可能高于 CPU 时间。 若要按 CPU 时间查看查询，请选择窗格左上方的“指标”下拉菜单，然后选择“CPU 时间(毫秒)” 。

左上象限中的每个条形表示一个查询。 选择一个条形可查看该查询的详细信息。 屏幕的右上象限显示该查询在查询存储中有多少执行计划，并根据其执行时间以及所选指标的使用量来映射这些计划。 选择每个“计划 ID”可控制在屏幕下半部分显示的查询执行计划。

注意

有关解释查询存储视图以及在“排名靠前的资源使用者”视图中显示的形状的指南，请参阅查询存储最佳做法

降低 CPU 使用率

故障排除的一部分应包括详细了解上一部分中标识的查询。 可以通过优化索引、修改应用程序模式、优化查询以及调整数据库的 CPU 相关设置，来降低 CPU 使用率。

请考虑此部分中的以下策略。

通过自动索引优化降低 CPU 使用率

有效索引优化可降低许多查询的 CPU 使用率。 优化索引可减少查询的逻辑和物理读取，这通常会使查询只需执行较少的工作。

Azure SQL 数据库为主要副本上的工作负载提供自动索引管理。 自动索引管理使用机器学习监视工作负载，并为数据库优化基于行存储磁盘的非聚集索引。

在 Azure 门户中查看性能建议，包括索引建议。 可以手动应用这些建议，也可以启用 CREATE INDEX 自动优化选项以创建和验证数据库中新索引的性能。

通过自动计划更正（强制计划）降低 CPU 使用率

发生高 CPU 使用率事件的另一个常见原因是执行计划选择回归。 Azure SQL 数据库提供强制计划自动优化选项，用于确定主要副本上工作负载中的查询执行计划的回归。 启用此自动优化功能后，Azure SQL 数据库会测试强制实施查询执行计划是否会使具有执行计划回归的查询具有更高的可靠性能。

如果数据库是在 2020 年 3 月之后创建的，则自动启用强制计划自动优化选项。 如果数据库是在此时间之前创建，则可能希望启用强制计划自动优化选项。

手动优化索引

使用确定高 CPU 利用率的原因中所述的方法来确定 CPU 消耗排名靠前的查询的查询计划。 这些执行计划会帮助你识别并添加非聚集索引以加快查询速度。

数据库中每个基于磁盘的非聚集索引都需要存储空间，必须由 SQL 引擎进行维护。 请在可能时修改现有索引，而不是添加新索引，并确保新索引可成功降低 CPU 使用率。 有关非聚集索引的概述，请参阅非聚集索引设计准则。

对于某些工作负载，列存储索引可能是降低频繁读取查询的 CPU 的最佳选择。 请参阅列存储索引 - 设计指南，获取有关列存储索引可能适用的方案的高级建议。

优化应用程序、查询和数据库设置

在检查排名靠前的查询时，可能会发现应用程序特征反模式，如“琐碎”行为、从分片受益的工作负载以及不理想的数据库访问设计。 对于进行大量读取的工作负载，请考虑使用只读副本卸载只读的查询工作负载，并考虑将应用程序层缓存作为长期策略来横向扩展频繁读取的数据。

此外，还可以选择手动优化工作负载中确定的 CPU 使用量排名靠前查询。 手动优化选项包括重写 Transact-SQL 语句、在查询存储中强制执行计划和应用查询提示。

如果发现查询有时使用的执行计划对于性能不是最佳的，请参阅存在参数敏感计划 (PSP) 问题的查询中的解决方案

如果发现具有大量计划的非参数化查询，请考虑参数化这些查询，从而确保完整声明参数数据类型（包括长度和精度）。 可通过以下方式完成：修改查询、创建计划指南以强制参数化特定查询，或是在数据库级别启用强制参数化。

如果发现编译率较高的查询，请确定导致频繁编译的原因。 频繁编译的最常见原因是 RECOMPILE 提示。 请尽可能确定添加 RECOMPILE 提示的时间以及旨在解决的问题。 调查是否可以实现备用性能优化解决方案，以便为经常运行的查询提供一致的性能，而无需 RECOMPILE 提示。

通过优化最大并行度来降低 CPU 使用率

最大并行度 (MAXDOP) 设置可控制数据库引擎中的查询内并行度。 MAXDOP 值较高通常会使每个查询的并行线程数更多，查询执行速度更快。

在某些情况下，同时运行的大量并行查询可能会减慢工作负载速度，并导致高 CPU 使用率。 在具有大量 vCore 并且 MAXDOP 设置为较高数字或零的数据库，十分可能出现过多并行。 MAXDOP 设置为零时，数据库引擎会将并行线程使用的计划程序数设置为逻辑核心总数或 64（以较小者为准）。

可以使用 Transact-SQL 确定数据库的最大并行度设置。 使用 SSMS 或 Azure Data Studio 连接到数据库，然后运行以下查询：

SELECT 
    name, 
    value, 
    value_for_secondary, 
    is_value_default 
FROM sys.database_scoped_configurations
WHERE name=N'MAXDOP';
GO

请考虑通过在数据库级别尝试对 MAXDOP 配置进行细小更改，或使用查询提示修改单个问题查询以使用非默认 MAXDOP。 有关详细信息，请参阅配置最大并行度中的示例。

何时添加 CPU 资源

可能会发现工作负载的查询和索引已正确优化，或者性能优化需要在短期内由于内部过程或其他原因而无法进行的更改。 添加更多 CPU 资源对于这些数据库可能十分有益。 可以最短的停机时间缩放数据库资源。

可以通过使用 vCore 购买模型为 Azure SQL 数据库配置 vCore 计数或硬件配置，将更多 CPU 资源添加到数据库。

在基于 DTU 的购买模型下，可以提升服务层，增加数据库事务单位数 (DTU)。 DTU 表示对 CPU、内存、读取和写入的混合度量。 vCore 购买模型的一个好处是，它允许更精细地控制使用中的硬件和 vCore 数。 可以将 Azure SQL 数据库从基于 DTU 的模型迁移到基于 vCore 的模型，以在购买模型之间进行转换。

相关内容

在以下文章中详细了解 Azure SQL 数据库监视和性能优化：