SQLServer CPU瓶颈问题的判定和解决

author: 天铭

发现问题

告警

数据库出现无法登陆的告警

定位原因

监控

活跃连接堆积

实例CPU持续99%+

实例总连接数超过规格

活跃链接堆积是结果，能堆积到500+可想对业务的影响已经非常严重了
连接数超过的原因跟业务上的限制策略有关

现场

实例正常连接已经无法建立，只能利用DAC协助诊断
使用DAC

实例等待

select lastwaittype,COUNT(*) from sys.sysprocesses
where spid>50
and lastwaittype!='MISCELLANEOUS'
group by lastwaittype

    WITH [Waits] AS
        (SELECT
            [wait_type],
            [wait_time_ms] / 1000.0 AS [WaitS],
            ([wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
            [signal_wait_time_ms] / 1000.0 AS [SignalS],
            [waiting_tasks_count] AS [WaitCount],
            100.0 * [wait_time_ms] / SUM ( [wait_time_ms]) OVER() AS [Percentage],
            ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC ) AS [RowNum]
        FROM sys.dm_os_wait_stats
        WHERE [wait_type] NOT IN (
            N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
            N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
            N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
            N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
            N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
            N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
            N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
            N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
            N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
            N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
            N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
        )
    SELECT
        [W1]. [wait_type] AS [WaitType],
        CAST ([W1]. [WaitS] AS DECIMAL( 14, 2 )) AS [Wait_S],
        CAST ([W1]. [ResourceS] AS DECIMAL( 14, 2 )) AS [Resource_S],
        CAST ([W1]. [SignalS] AS DECIMAL( 14, 2 )) AS [Signal_S],
        [W1]. [WaitCount] AS [WaitCount],
        CAST ([W1]. [Percentage] AS DECIMAL( 4, 2 )) AS [Percentage],
        CAST (([W1]. [WaitS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
        CAST (([W1]. [ResourceS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
        CAST (([W1]. [SignalS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
    FROM [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2]
        ON [W2].[RowNum] <= [W1].[RowNum]
    GROUP BY [W1]. [RowNum], [W1].[wait_type] , [W1] .[WaitS],
        [W1]. [ResourceS], [W1].[SignalS] , [W1] .[WaitCount], [W1].[Percentage]
    HAVING SUM ([W2] .[Percentage]) - [W1].[Percentage] < 95 ;
    GO

CPU 开销大的SQL

诊断报告

实例在无法连接前的一个诊断报告也和我们的检查结果一致

实例CPU使用率

等待信息

活跃连接都在等CPU调度，spid已经复用到1.6K+

处理方式

临时

为了让实例快速恢复，首先要做的是适当放大调整CPU affinity mask，并且让用户应用做适当降级不要再次压垮实例

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 1 TO 2,6,9 TO 10,14 TO 16,19 TO 23

长期

长期需要优化SQL逐步从根本上解决问题，当然也有的时候SQL的执行计划已经很好，只是业务的并发和RT达不到用户要求，这就需要考虑升级或做业务调整
这个CASE通过类似几个SQL优化达到了不错的效果

set statistics profile on
set statistics io on
set statistics time on

select top 50  from  where ='' order by id desc

set statistics profile off
set statistics io off
set statistics time off

执行计划的Bookmark可以进一步优化

优化建议

注意一般的情况下都要加online参数避免锁表时间过长，尤其是这个CASE中1kw+的大表；但也要清楚相应代价，具体可以看下这篇SQLServer 在线添加索引

处理结果

优化后的 SQL开销

set statistics profile on
set statistics io on
set statistics time on

select top 50  from  where ='' order by id desc

set statistics profile off
set statistics io off
set statistics time off

优化后的SQL执行计划

逻辑读从5k降到6，CPU从31降到0 ms，且从执行计划来看已经最优

实例整体优化后CPU开销变化

CPU开销明显已经下降