（点击
上方公众号
，可快速关注）
来源：马非码
链接：
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'
