SQL Server CPU 利用率毛刺的分析定位与解决

一.背景

1.1 问题描述

近期发现一台SQL Server的CPU利用率很不稳定，发现不定时的飙升到100%，更可怕的是在业务繁忙时，影响了业务调用，失败率明显增加，所以，减低CPU的利用率，是迫切需要解决的问题。 CPU升高的原因直观上来说，就是CPU（中央处理器）的负载过高， 中央处理器忙不过来。进一步分析的话，可以从两个角度优化，1.减少单个CPU 的处理时间；2.减少单个任务占有的CPU核数，即一个任务不要分配太多的CPU核数。 1.2 优化的方法 1.表结构的优化，例如索引是否合理、关联表字段的定义是否一致等； 2.SQL 语句的优化； 3.表数据量是否归档、缩减； 4.将数据缓存到缓存层（如，Reids），减少对DB的访问； 5.DB实例配置是否需要优化； 6.升级硬件。

二. 问题处理过程

2.1 优化前

从这个监控图可以看出，CPU最大值为100%,平均值为19%，毛刺比较明显。 2.2 定位SQL语句

通过常用的SP，很快定位到了SQL语句，是关于一张表的查询，语句简单，但是表的数据量比较大（7600W），查询出的数据有（4000W）。这张表每天的写入量<100W。

并且和研发确认，此SQL的调用也是周期性的，比如5分钟查询一次，基本符合Zabbix周期性CPU毛刺突起。 2.3 处理步骤 Step 1 .考虑到，CPU突然飙升，毛刺陡然加剧，冲刺到100%，并且问题SQL 不是最近新上线，所以，们的第一反映是 索引走偏，统计信息失效了。但是 刷新了 表统计信息 ，情况没有好转。 Step 2 . 考虑到是不是索引失效了，们坚持到业务低峰期，重建了表的索引，情况 依然没有好转。 Step 3 .考虑是不是表的数据量到了一定规模，才导致的此问题，和业务研发确认后，将历史数据归档，归档了2800W，数据量由7600W减少到4800W。数据量减少后，情况有所好转，SQL事务的排队和阻塞 明显减少。但是毛刺突起依然明显，CPU 100% 依然存在。 ……….

无语 ……… Step 4 这时想到，最大并行度 。当SQL Server 发现一条指令比较复杂（不仅仅是SQL语句复杂，查询的数据量比较大也是复杂），会决定用多少个线程并行执行，从而提高整体相应时间。如果指令复杂，甚至需要所有CPU来运行这些线程，别的用户发过来的指令会受到影响，甚至可能拿不到CPU执行。即需要调整max degree of parallelism的值。

查看问题实例 发现没有设置，即可以使用所有的CPU。修改参数，将最大并行度将至4.执行以下命令:

exec sp_configure 'max degree of parallelism',4
go
RECONFIGURE
GO

此时 毛刺消失了，问题解决了。 2.4 优化后

优化后，从监控图中可以看出，CPU的最大值降至了25%,平局值为7%。

三.定位问题SQL常用的SP

当们遇到DB性能问题或DBServer监控指标异常时，以下四个SP，可以帮忙们快速定位SQL语句。 3.1 查看当前阻塞排队的情况

/*  
---------------------------------------------------------------------------------  
uspm_Block  

功能：查看阻塞和锁,阻塞源头 

参数：无 
---------------------------------------------------------------------------------  
*/  
create PROCEDURE  [dbo].[uspm_Block]  
as  
--查找有关被阻塞的请求的信息(含用户)
SELECT  s.loginame
       ,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2 
               ELSE qs.statement_end_offset 
          END - qs.statement_start_offset)/2) 
       ,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id
       ,qs.wait_type ,qs.wait_time ,qs.wait_resource 
       ,qs.transaction_id 
FROM SYS.DM_EXEC_REQUESTS qs (nolock)
  LEFT JOIN (
             SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES (nolock) GROUP BY spid
            ) s ON qs.session_id=s.spid
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
WHERE qs.status = N'suspended'
--and s.loginame<>''
ORDER BY qs.wait_time DESC
--查找阻塞源头v3.0
SELECT SP.spid
      ,CASE WHEN ST1.text IS NULL THEN ST2.text
            ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2, 
                 (
                  CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2 
                       ELSE SR.statement_end_offset 
                  END - SR.statement_start_offset)/2
                  )
        END AS [T-sql]
      ,SP.loginame
      ,DB_NAME(SP.dbid) AS [db_name]
      ,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*
FROM SYS.SYSPROCESSES SP (nolock) 
  LEFT JOIN SYS.DM_EXEC_REQUESTS SR (nolock) ON SP.spid=SR.session_id
  LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC (nolock) ON SP.spid=SC.session_id
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1
WHERE SP.spid IN
      (
         SELECT BLOCKED FROM SYS.SYSPROCESSES (nolock) WHERE BLOCKED<>0
      )
  AND SP.BLOCKED=0
GO

3.2 查看 当前最消耗CPU的SQL

/*
---------------------------------------------------------------------------------
uspm_perf_topcpu

功能：取当前N个最耗CPU的SQL

参数：@topCount   --N的具体数量,默认取前20条

示例：
     1.取前10条
      exec uspm_perf_topcpu 
     2.取前20条最耗CPU的SQL
      exec uspm_perf_topcpu @topCount=20 
---------------------------------------------------------------------------------
*/
CREATE PROCEDURE  [dbo].[uspm_perf_topcpu]
(@topCount int=10)
as 
set nocount on
declare @cmd varchar(1000)
select @cmd='
        SELECT TOP '+ CAST(@topCount AS VARCHAR)+'  SUBSTRING(text, (statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                                WHEN -1 THEN DATALENGTH(text)
                                ELSE statement_end_offset
                                END - statement_start_offset)/2) + 1
                            ) AS query_text
                            ,b.hostname
                            ,b.loginame
                            ,a.*
                            ,qr.text
                            ,qt.query_plan
            FROM sys.dm_exec_requests a (nolock)
               INNER JOIN sys.sysprocesses b (nolock) on a.session_id=b.spid
               CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as qr
               CROSS APPLY sys.dm_exec_query_plan(a.plan_handle)as qt
            ORDER BY a.total_elapsed_time DESC'

exec(@cmd)
GO

3.3 查看执行时间最长的SQL

/*
---------------------------------------------------------------------------------
uspm_perf_topduration 

功能：取N个执行时间最长的SQL

参数：@topCount   --N的具体数量,默认取前50条

示例：
      --1.取前50条
      exec uspm_perf_topduration 
      --2.取前10条执行时间最长的SQL
      exec uspm_perf_topduration @topCount=10
---------------------------------------------------------------------------------
*/
CREATE PROCEDURE  [dbo].[uspm_perf_topduration] 
(@topCount int=50)
as 
set nocount on
declare @cmd varchar(600)
select @cmd='
            select  
                highest_cpu_queries.plan_handle,  
                highest_cpu_queries.total_worker_time, 
                q.dbid, 
                q.objectid, 
                q.number, 
                q.encrypted, 
                q.[text] 
            from  
                (select top '+ cast(@topCount as varchar)+'  
                    qs.plan_handle,  
                    qs.total_worker_time 
                from  
                    sys.dm_exec_query_stats qs  (nolock)
                order by qs.total_worker_time desc) as highest_cpu_queries 
                cross apply sys.dm_exec_sql_text(plan_handle) as q 
            order by highest_cpu_queries.total_worker_time desc'
exec(@cmd)
GO

3.4 当前SQL执行概览

/*  
---------------------------------------------------------------------------------  
uspm_perf_cpudetail  

功能：查看CPU的任务数量,使用率，CPU瓶颈  

参数：无参  
---------------------------------------------------------------------------------  
*/  
CREATE PROCEDURE  [dbo].[uspm_perf_cpudetail]  
as   
set nocount on  
--1.Cpu_Task  
SELECT '查看cpu任务'  
SELECT scheduler_id, current_tasks_count, runnable_tasks_count  
FROM sys.dm_os_schedulers (nolock)  
WHERE scheduler_id < 255  

---2.CUP_USING  
SELECT '查看cpu使用情况'  
declare @ts_now bigint  
 --select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info (nolock)  
 select @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) from sys.dm_os_sys_info (nolock)  

 select top 50 record_id,  
  dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,   
  SQLProcessUtilization as [CPU使用率,不能始终处于高位],  
  SystemIdle,  
  100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization  
 from (  
  select   
   record.value('(./Record/@id)[1]', 'int') as record_id,  
   record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,  
   record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,  
   timestamp  
  from (  
   select timestamp, convert(xml, record) as record   
   from sys.dm_os_ring_buffers  (nolock)  
   where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'  
   and record like '%<SystemHealth>%') as x  
  ) as y   
 order by record_id desc  


--3.CPU_NECK  
SELECT '查看CPU瓶颈'  
select cast([signal_wait_time_ms] as decimal(30,2))/[wait_time_ms] as [百分比] ,*   
from sys.dm_os_wait_stats (nolock)   
where [wait_time_ms]<>0    
and cast([signal_wait_time_ms] as decimal(30,2))>([wait_time_ms]*0.25 )  
order by 1 desc  

SELECT '查看百分比是否>10%,如果大于10%，考虑降低并行度'  
select cast([signal_wait_time_ms] as decimal(30,2))/[wait_time_ms] as [百分比],*   
from sys.dm_os_wait_stats (nolock)  
where [wait_time_ms]<>0 AND  wait_type='CXPACKET'
GO

原文创作：东山絮柳仔

原文链接：https://www.cnblogs.com/xuliuzai/p/15019204.html

文章列表

• 通过Python收集MySQL MHA 部署及运行状态信息的功能实现
• 通过Python将监控数据由influxdb写入到MySQL
• 通过Python实现生成excel并邮件发送的功能
• 通过Python实现对SQL Server 数据文件大小的监控告警
• 通过 Telegraf + InfluxDB + Grafana 快速搭建监控体系的详细步骤
• 迁移Report Server DataBase时遇到的坑
• 谨慎 mongodb 关于数字操作可能导致类型及精度变化
• 详解MongoDB中的多表关联查询$lookup
• 瞧一瞧！这儿实现了MongoDB的增量备份与还原含部署代码
• 时序数据库InfluxDB的基本语法
• 数据库服务器资源使用情况周报
• 数据库如何应对保障大促活动
• 我10亿级ES数据迁到MongoDB节省90%成本!转载
• 应用部署架构演进转载 -
• 学习ProxySQL参考到几个网址
• 名言小抄五
• 关于SQL Server 镜像数据库快照的创建及使用
• 关于SQL Server 数据库归档的一些思考和改进
• 关于MongoDB时间格式转换和时间段聚合统计的用法总结
• 以实现MongoDB副本集状态的监控为例，看Telegraf系统中Exec输入插件如何编写部署
• 一个磁盘I/O故障导致的AlwaysOn FailOver 过程梳理和分析
• shell 操作钉钉机器人实现告警提醒
• python 学习笔记 四
• python 学习 三
• kapacitor的安装及部分常用命令
• TiDB 架构及设计实现
• TiDB 学习笔记一运维管理
• SQL Server 查看当前会话状态sp_WhoIsActive 转载 -
• SQL Server DB迁移工作List
• SQL Server CPU 利用率毛刺的分析定位与解决
• SQL Server Alwayson架构下 服务器 各虚拟IP漂移监控告警的功能实现 1服务器视角
• Python发送的邮件设置收件人隐藏与显示
• MySQL索引设计需要考虑哪些因素？
• MySQL日志收集之Filebeat和Logstsh的一键安装配置ELK架构
• MySQL数据归档小工具推荐及优化mysql_archiver
• MySQL数据库规范 设计规范+开发规范+操作规范
• MySQL数据库Inception工具学习与测试 笔记
• MySQL常用命令汇总偏向运维管理
• MySQL在线DDL工具 ghost
• MySQL 学习笔记四
• MySQL 学习笔记三
• MySQL alter table时执行innobackupex全备再谈Seconds_Behind_Master
• MySQL MHA 运行状态监控
• MongoDB数据库的设计规范
• MongoDB实例重启失败探究大事务Redo导致
• MongoDB 那些事一文以蔽之
• MongoDB 中的加减乘除 - 运算
• MongoDB 中数据的替换方法实现 类Replace函数功能
• Linux常用命令总结二
• K8S基础学习