记一次使用T-SQL对Azure SQL 数据库/托管实例中的高 CPU 利用率诊断实例

目录

（一）前言

（二）了解 vCore 计数

（三）判断高 CPU 利用率的原因

1. 常见导致CPU占有率高的原因

2. 常用检查方法(重点介绍使用T-SQL的方式)

（四）实际操作

1. 查看有无死锁

 2. 查看消耗CPU资源比较大的语句

（1）确定当前正在运行的查询

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

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

---

（一）前言

在Azure SQL数据库/托管实例中，我们通常会使用Azure Portal上的Performance相关选项卡/SSMS工具中的Query Store图形化功能/SSMS中直接执行T-SQL来判断是哪些SQL导致PAAS服务CPU占有率很高。本例是基于我们公司最近双11提前预售活动时遇到的一次性能问题改编，我将使用实际生产库作为环境运行语句，供大家参考和探讨。

（二）了解 vCore 计数

诊断高 CPU 事件时，了解可供数据库使用的虚拟核心 (vCore) 数会十分有用。 vCore 等效于逻辑 CPU。 vCore 数有助于了解数据库可用的 CPU 资源。使用 Transact-SQL 确定 vCore 计数的语句如下：

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

 

备注

对于使用 Gen4 硬件的数据库，sys.dm_os_schedulers 中的可见联机计划程序数可能是在创建数据库时指定的 vCore 数的两倍，会显示在 Azure 门户中。

（三）判断高 CPU 利用率的原因

1. 常见导致CPU占有率高的原因

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

2. 常用检查方法(重点介绍使用T-SQL的方式)

• 工作负载中是否出现使用大量 CPU 的新查询，或者是否看到定期运行查询的频率提高？ 使用以下任何方法进行调查。 查找历史记录有限的查询（新查询），并查看历史记录较长的查询的执行频率。
  • 在 Azure 门户中查看 CPU 指标和排名靠前的相关查询
  • 使用 Transact-SQL 按 CPU 使用率查询排名靠前的最近 15 个查询
    。
  • 使用 SSMS 中的交互式查询存储工具按 CPU 时间确定排名靠前的查询
• 工作负载中的某些查询在每次执行时是否使用比过去更多的 CPU？ 如果是这样，查询执行计划是否更改？ 这些查询可能
  存在参数敏感计划 (PSP) 问题。 使用以下任一方法进行调查。 查找具有多个查询执行计划并且 CPU 使用率发生显著变化的查询：
  • 使用 Transact-SQL 按 CPU 使用率查询排名靠前的最近 15 个查询
    。
  • 使用 SSMS 中的交互式查询存储工具按 CPU 时间确定排名靠前的查询
• 是否有出现大量编译或重新编译的证据？ 
  按查询哈希查询最常编译的查询，并查看编译频率。
• 查询是否使用过多并行？ 查询MAXDOP 数据库范围的配置 
  并查看 vCore 计数
  。 在 MAXDOP 设置为 0 并且核心计数高于 8 的数据库中，通常会出现并行过多的情况。

（四）实际操作

1. 查看有无死锁

首先用一段T-SQL来判断下是否数据库里存在大量死锁，如果存在大量死锁后可以从得出的结果里大致判断是什么语句以及什么表或视图牵扯到死锁。

SELECT   spid,        
         blocked,         
         DB_NAME(sp.dbid) AS DBName,        
         program_name,         
         waitresource,         
         lastwaittype,         
         sp.loginame,        
         sp.hostname,         
         a.[Text] AS [TextData],        
         SUBSTRING(A.text, sp.stmt_start / 2,         
         (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end          
		 END - sp.stmt_start) / 2) AS [current_cmd]
FROM  sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A 
WHERE spid > 50
and sp.blocked <> 0 --这里blocked字段表示是否处于死锁阻塞，如果值不为0则说明死锁，反之则不死锁
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];

如果在这段T-SQL中我们将WHERE中的条件sp.blocked<>0改为=0的话，则结果如下，我们可以看到很多处于非死锁状态下的进程的明细(比如来自什么数据库，数据库连接登录名是什么，来访主机资源名称是什么以及实际执行的SQL语句又是什么等重要信息) 

 

如若找到死锁的记录条，则使用kill命令去杀掉blocked字段里的ID值即可，格式如下：

KILL ID（blocked）

假设blocked里的值为8488:

kill 8488;

 

 

 2. 查看消耗CPU资源比较大的语句

在上一步骤中假设没有死锁的会话或语句，则我们重点考虑如何拉出执行效率比较低的语句。

（1）确定当前正在运行的查询

通过执行以下查询，使用 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;

 

 

 

此查询返回执行计划的两个副本。 列 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() 返回数据，其中包括“正在进行”的执行统计信息，例如当前正在运行的查询到目前为止返回的实际行数。

（2）查看过去一小时的 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; 

 

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

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

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

 

前提：在SSMS中要切换到业务所在的数据库！！！！！

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;

 

 

 

 

 此查询为 query_hash 在整个查询存储历史记录中的每个执行计划变体返回一行。 结果会按总 CPU 时间进行排序。同时,sampled_query_text可以直接看出是具体哪句执行语句命令消耗资源较高。