SQL Server CPU 利用率毛刺的分析定位與解決

東山絮柳仔發表於2021-08-05
SQLServer

一.背景

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

 

相關文章