程序猿是如何解决 SQLServer 占 CPU 100%

（点击 上方公众号 ，可快速关注）

来源：马非码

链接： www.cnblogs.com/marvin/p/ASolutionForSQLServerCauseHighCPU.html

遇到的问题

有同事反应服务器CPU过高，一看截图基本都是100%了，my god，这可是大问题，赶紧先看看。

让同事查看系统进程，发现是SQLServer的CPU占用比较高。首先想到的是不是报表生成的时候高，因为这块之前出现过问题，关掉服务程序，还是高。难道是客户端程序引发的？但是这么多的客户端连接，难不成每个都叫人关闭，很简单，把网络断开即可。网络断开之后，CPU立马下降。那么问题到底在哪里呢，是时候祭出我们的利器了——SQLServer Profiler。

使用SQLServer Profiler监控数据库

让同事使用SQLProfiler监控了大概20分钟左右，然后保存为跟踪文件*.rtc。

我们来看看到底是哪句SQL有问题：

SQL1：查找最新的30条告警事件

select top 30 a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.Name as AddrName,b.Name as MgrObjName,a.Ch,a. Value ,a.Content,a.Level

,ag.Name as AgentServerName,a.EventBm,a.MgrObjId,a.Id,a.Cfmoper,a.Cfm,a.Cfmtime,a.State,a.IgnoreStartTime,a.IgnoreEndTime

,a.OpUserId,d.Name as MgrObjTypeName,l.UserName as userName,f.Name as AddrName2

from eventlog as a left join mgrobj as b on a.MgrObjId = b.Id and a.AgentBm = b.AgentBm

left join addrnode as c on b.AddrId = c.Id left join mgrobjtype as d on b.MgrObjTypeId = d.Id

left join eventdir as e on a.EventBm = e.Bm left join agentserver as ag on a.AgentBm = ag.AgentBm

left join loginUser as l on a.cfmoper = l.loginGuid left join addrnode as f on ag.AddrId = f.Id

where ((MgrObjId in (

select Id from MgrObj

where AddrId in ( '' , '02100000' , '02113000' , '02113001' , '02113002' , '02113003' , '02113004'

, '02113005' , '02113006' , '02113007' , '02113008' , '02113009' , '02113010' , '02113011' , '02113012'

, '02113013' , '02113014' , '02113015' , '02113016' , '02113017' , '02113018' , '02113019' , '02113020'

, '02113021' , '02113022' , '02113023' , '02113024' , '02113025' , '02113026' )))

or (mgrobjid in ( '00000000-0000-0000-0000-000000000000' , '00000000-0000-0000-0000-000000000000'

, '00000000-0000-0000-0000-000000000000' , '11111111-1111-1111-1111-111111111111'

, '11111111-1111-1111-1111-111111111111' ))

)

order by alarmtime DESC

SQL2：获取当前的总报警记录数

select count ( * ) from eventlog as a left join mgrobj as b on a . MgrObjId = b . Id and a . AgentBm = b . AgentBm

left join addrnode as c on b . AddrId = c . Id left join mgrobjtype as d on b . MgrObjTypeId = d . Id

left join eventdir as e on a . EventBm = e . Bm

where MgrObjId in (

select Id from MgrObj where AddrId in

( '' , '02100000' , '02100001' , '02100002' , '02100003' , '02100004' , '02100005' , '02100006' , '02100007'

, '02100008' , '02100009' , '02100010' , '02100011' , '02100012' , '02100013' , '02100014' , '02100015'

, '02100016' , '02100017' , '02100018' , '02100019' , '02101000' , '02101001' , '02101002' , '02101003'

, '02101004' , '02101005' , '02101006' , '02101007' , '02101008' , '02101009' , '02101010' , '02101011' , '02101012'

, '02101013' , '02101014' , '02101015' , '02101016' , '02101017' , '02101018' , '02101019' , '02101020' , '02101021'

, '02101022' , '02101023' , '02101024' , '02101025' , '022000' , '022001' , '022101' , '022102' , '0755' , '0755002' )

)

and mgrobjid not in (

'00000000-0000-0000-0000-000000000000' , '00000000-0000-0000-0000-000000000000' , '00000000-0000-0000-0000-000000000000'

, '11111111-1111-1111-1111-111111111111' , '11111111-1111-1111-1111-111111111111' )

这是典型的获取数据并分页的数据，一条获取最新分页记录总数，一条获取分页记录，正是获取最新事件这里导致的CPU过高。这里的业务大概是每个客户端，每3秒执行一次数据库查找，以便显示最新的告警事件。好了，元凶找到了，怎么解决？

有哪些SQL语句会导致CPU过高？

上网查看了下文章，得出以下结论：

1.编译和重编译

编译是 Sql Server 为指令生成执行计划的过程。Sql Server 要分析指令要做的事情，分析它所要访问的表格结构，也就是生成执行计划的过程。这个过程主要是在做各种计算，所以CPU 使用比较集中的地方。

执行计划生成后会被缓存在 内存中，以便重用。但是不是所有的都可以 被重用。在很多时候，由于数据量发生了变化，或者数据结构发生了变化，同样一句话执行，就要重编译。

2.排序（sort） 和 聚合计算（aggregation）

在查询的时候，经常会做 order by、distinct 这样的操作，也会做 avg、sum、max、min 这样的聚合计算，在数据已经被加载到内存后，就要使用CPU把这些计算做完。所以这些操作的语句CPU 使用量会多一些。

3.表格连接（Join）操作

当语句需要两张表做连接的时候，SQLServer 常常会选择 Nested Loop 或 Hash 算法。算法的完成要运行 CPU，所以 join 有时候也会带来 CPU 使用比较集中的地方。

4.Count(*) 语句执行的过于频繁

特别是对大表 Count() ，因为 Count() 后面如果没有条件，或者条件用不上索引，都会引起 全表扫描的，也会引起 CPU 的大量运算

大致的原因，我们都知道了，但是具体到我们上述的两个SQL，好像都有上述提到的这些问题，那么到底哪个才是最大的元凶，我们能够怎么优化？

查看SQL的查询计划

SQLServer的查询计划很清楚的告诉了我们到底在哪一步消耗了最大的资源。 我们先来看看获取top30的记录：

排序竟然占了94%的资源。原来是它！同事马上想到，用orderno排序会不会快点。先把上述语句在SQLServer中执行一遍，清掉缓存之后，大概是2~3秒，然后排序字段改为orderno，1秒都不到，果然有用。但是orderno的顺序跟alarmTime的顺序是不完全一致的，orderno的排序无法替代alarmTime排序，那么怎么办？

我想，因为选择的是top，那么因为orderno是聚集索引，那么选择前30条记录，可以立即返回，根本无需遍历整个结果，那么如果alarmTime是个索引字段，是否可以加快排序？

选择top记录时，尽量为order子句的字段建立索引

先建立索引：

IF NOT EXISTS ( SELECT * FROM sysindexes WHERE id = OBJECT_ID( 'eventlog' ) AND name= 'IX_eventlog_alarmTime' )

CREATE NONCLUSTERED INDEX IX_eventlog_alarmTime ON dbo.eventlog(AlarmTime)

在查看执行计划：

看到没有，刚才查询耗时的Sort已经消失不见了，那么怎么验证它能够有效的降低我们的CPU呢，难道要到现场部署，当然不是。

查看SQL语句CPU高的语句

SELECT