SQLServer CPU瓶颈问题的判定和解决-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

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

简介: title: SQLServer · CASE分析 · CPU瓶颈问题的判定和解决 author: 天铭 发现问题 告警 数据库出现无法登陆的告警 定位原因 监控 活跃连接堆积 实例CPU持续99%+ 实例总连接数超过规格活跃链接堆积是结果,能堆积到500+可想对业务的影响已经

title: SQLServer · CASE分析 · CPU瓶颈问题的判定和解决

author: 天铭

发现问题

告警

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

定位原因

监控

活跃连接堆积
1

实例CPU持续99%+
2

实例总连接数超过规格
3
活跃链接堆积是结果,能堆积到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
5

诊断报告

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

实例CPU使用率
_CPU_

等待信息
_

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

处理方式

临时

为了让实例快速恢复,首先要做的是适当放大调整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

_SQL_

_SQL_

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

优化建议
6

7

注意一般的情况下都要加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_

优化后的SQL执行计划
_SQL_
逻辑读从5k降到6,CPU从31降到0 ms,且从执行计划来看已经最优

实例整体优化后CPU开销变化
8
CPU开销明显已经下降

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接