（点击上方公众号，可快速关注）
来源：马非码
链接：www.cnblogs.com/marvin/p/ASolutionForsqlServerCauseHighcpu.HTML
有同事反应服务器cpu过高，一看截图基本都是100%了，my god，这可是大问题，赶紧先看看。
让同事查看系统进程，发现是sqlServer的cpu占用比较高。首先想到的是不是报表生成的时候高，因为这块之前出现过问题，关掉服务程序，还是高。难道是客户端程序引发的？但是这么多的客户端连接，难不成每个都叫人关闭，很简单，把网络断开即可。网络断开之后，cpu立马下降。那么问题到底在哪里呢，是时候祭出我们的利器了——sqlServer Profiler。
让同事使用sqlProfiler监控了大概20分钟左右，然后保存为跟踪文件*.rtc。
我们来看看到底是哪句sql有问题：
select top 30 a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.name as Addrname,b.name 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
MgrObjTypename,l.Username username,f.name Addrname2
from eventlog a left join mgrobj b on a.MgrObjID=b.ID and a.AgentBm=b.AgentBm
left join addrnode c b.AddrID=c.ID mgrobjtype d b.MgrObjTypeID=d.ID eventdir e a.EventBm=e.Bm agentserver ag=ag.AgentBm loginUser l a.cfmoper=l.loginGuID f ag.AddrID=f.ID
where ((MgrObjID in (
select ID from MgrObj where AddrID ('','02100000',68) !important;">'02113000',68) !important;">'02113001',68) !important;">'02113002',68) !important;">'02113003',68) !important;">'02113004'
,68) !important;">'02113005',68) !important;">'02113006',68) !important;">'02113007',68) !important;">'02113008',68) !important;">'02113009',68) !important;">'02113010',68) !important;">'02113011',68) !important;">'02113012'
'02113013',68) !important;">'02113014',68) !important;">'02113015',68) !important;">'02113016',68) !important;">'02113017',68) !important;">'02113018',68) !important;">'02113019',68) !important;">'02113020'
'02113021',68) !important;">'02113022',68) !important;">'02113023',68) !important;">'02113024',68) !important;">'02113025',68) !important;">'02113026'))) or (mgrobjID'00000000-0000-0000-0000-000000000000',68) !important;">'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 andAgentBm.AgentBm
left join addrnode as c on b.AddrID=c.ID left join mgrobjtype as dMgrObjTypeIDd.ID
left join eventdir as eaEventBme.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 '00000000-0000-0000-0000-000000000000''11111111-1111-1111-1111-111111111111')
这是典型的获取数据并分页的数据，一条获取最新分页记录总数，一条获取分页记录，正是获取最新事件这里导致的cpu过高。这里的业务大概是每个客户端，每3秒执行一次数据库查找，以便显示最新的告警事件。好了，元凶找到了，怎么解决？
上网查看了下文章，得出以下结论：
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，好像都有上述提到的这些问题，那么到底哪个才是最大的元凶，我们能够怎么优化？
sqlServer的查询计划很清楚的告诉了我们到底在哪一步消耗了最大的资源。我们先来看看获取top30的记录：
排序竟然占了94%的资源。原来是它！同事马上想到，用orderno排序会不会快点。先把上述语句在sqlServer中执行一遍，清掉缓存之后，大概是2~3秒，然后排序字段改为orderno，1秒都不到，果然有用。但是orderno的顺序跟alarmTime的顺序是不完全一致的，orderno的排序无法替代alarmTime排序，那么怎么办？
我想，因为选择的是top，那么因为orderno是聚集索引，那么选择前30条记录，可以立即返回，根本无需遍历整个结果，那么如果alarmTime是个索引字段，是否可以加快排序？
先建立索引：
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呢，难道要到现场部署，当然不是。
SELECT top 10 TEXT AS 'sql Statement'
AS 'Last Execution Time'
+ total_physical_reads total_logical_writes) / execution_count [Average IO]
execution_count) 1000000.0 cpu Time (sec)]
Elapsed"Execution Count",qs.total_physical_reads,qs.total_logical_writes
"query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time DESC
我们把建索引前后cpu做个对比：
已经明显减低了。
我们再来看看count(*)这句怎么优化，因为上面的这句跟count这句差别就在于order by的排序。老规矩，用查询计划看看。
用语句select count(0) from eventlog一看，该表已经有20多w的记录，每次查询30条数据，竟然要遍历这个20多w的表两次，能不耗cpu吗。我们看看是否能够利用相关的条件来减少表扫描。很明显，我们可以为MgrObjID建立索引：
CREATE NONCLUSTERED INDEX IX_eventlog_moID ON dbo.eventlog(MgrObjID)
但是无论我怎么试，都是没有利用到索引，难道IN子句和NOT IN子句是没法利用索引一定会引起表扫描。于是上网查资料，找到桦仔的文章，这里面有解答：
sqlSERVER对筛选条件（search argument/SARG）的写法有一定的建议
对于不使用SARG运算符的表达式，索引是没有用的，sqlSERVER对它们很难使用比较优化的做法。非SARG运算符包括
NOT、、NOT EXISTS、NOT IN、NOT liKE和内部函数，例如：Convert、Upper等
但是这恰恰说明了IN是可以建立索引的啊。百思不得其解，经过一番的咨询之后，得到了解答：
不一定是利用索引就是好的,sqlserver根据你的查询的字段的重复值的占比，决定是表扫描还是索引扫描
有道理，但是我查看了下，重复值并不高，怎么会有问题呢。
关键是，你select的字段，这个地方使用索引那么性能更差，你select字段 ID,addrID,agentbm,mgrobjtypeID,name都不在索引里。
真是一语惊醒梦中人，缺的是包含索引！！！关于包含索引的重要性我在这篇文章《我是如何在sqlServer中处理每天四亿三千万记录的》已经提到过了，没想到在这里又重新栽了个跟头。实践，真的是太重要了！
好吧，立马建立相关索引：
IF NOT EXISTS(SELECT'IX_eventlog_moID')
IX_eventlog_moID dbo.eventlog(MgrObjID) INCLUDE(EventBm,AgentBM)
我们再来看看查询计划：
看到没有，已经没有eventlog表的表扫描了。我们再来比较前后的cpu：
很明显，这个count的优化，对查询top的语句依然的生效的。目前为止，这两个查询用上去之后，再也没有cpu过高的现象了。
通过服务端的推送，有事件告警或者解除过来才查询数据库。
优化上述查询语句，比如count(*)可以用count(0)替代
优化语句，先查询出所有的MgrObjID，然后在做连接
为管理对象、地点表等增加索引
添加了索引之后，事件表的插入就会慢，能够再怎么优化呢？可以分区建立索引，每天不忙的时候，把新的记录移入到建好索引的分区
当然，这些优化的手段是后续的事情了，我要做的事情基本完了。
服务器cpu过高，首先查看系统进程，确定引发cpu过高的进程
通过sqlServer Profiler能够轻易监控到哪些SQL语句执行时间过长，消耗最多的cpu
通过SQL语句是可以查看每条SQL语句消耗的cpu是多少
导致cpu高的都是进行大量计算的语句：包括内存排序、表扫描、编译计划等。
如果使用top刷选前面几条语句，则尽量为Order By子句建立索引，这样可以减少对所有的刷选结果进行排序
使用Count查询记录数时，尽量通过为where字句的相关字段建立索引以减少表扫描。如果多个表进行join *** 作，则把相关的表连接字段建立在包含索引中
通过服务端通知的方式，减少SQL语句的查询
通过表分区，尽量降低因为添加索引而导致表插入较慢的影响
------------------ 推荐 ------------------
范品社推出了十几款程序员、电影、美剧和物理题材的极客T恤。单件 ￥59.9、两件减￥12、四件减￥28，详见网店商品页介绍。
淘口令：复制以下红色内容，然后打开手淘即可购买
范品社，使用￥极客T恤￥抢先预览（长按复制整段文案，打开手机淘宝即可进入活动内容）
总结以上是内存溢出为你收集整理的程序猿是如何解决 SQLServer 占 CPU 100%全部内容，希望文章能够帮你解决程序猿是如何解决 SQLServer 占 CPU 100%所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）