SRE文档

深浅模式

根据SSMS工具中的Query Store图形化功能/SSMS中直接执行T-SQL来判断是哪些SQL导致PAAS服务CPU占有率很高

如果是云上,则使用厂商自动图形界面进行排查

1. 了解 vCore 计数 ​

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

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

2. 判断高 CPU 利用率的原因 ​

2.1 常见导致CPU占有率高的原因 ​

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

2.2 常用检查方法 ​

工作负载中是否出现使用大量 CPU 的新查询，或者是否看到定期运行查询的频率提高？ 使用以下任何方法进行调查。 查找历史记录有限的查询（新查询），并查看历史记录较长的查询的执行频率。

  • 在 云厂商 门户中查看 CPU 指标和排名靠前的相关查询
  • 使用 Transact-SQL 按 CPU 使用率查询排名靠前的最近 15 个查询
  • 使用 SSMS 中的交互式查询存储工具按 CPU 时间确定排名靠前的查询

工作负载中的某些查询在每次执行时是否使用比过去更多的 CPU？ 如果是这样，查询执行计划是否更改？ 这些查询可能 存在参数敏感计划 (PSP) 问题。 使用以下任一方法进行调查。 查找具有多个查询执行计划并且 CPU 使用率发生显著变化的查询：

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

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

  • 是否有出现大量编译或重新编译的证据？ 按查询哈希查询最常编译的查询，并查看编译频率。

  • 查询是否使用过多并行？ 查询MAXDOP 数据库范围的配置 并查看 vCore 计数 在 MAXDOP 设置为 0 并且核心计数高于 8 的数据库中，通常会出现并行过多的情况。

3. 实操 ​

3.1 查看有无死锁 ​

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];
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];

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

sql
KILL ID（blocked）
KILL ID（blocked）

3.2 查看消耗CPU资源比较大的语句 ​

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

3.2.1确定当前正在运行的查询 ​

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

sql
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;
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;

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

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

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

sql
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;
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;