？有同事反映服务器CPU太高。看了截图，基本100%了。我的天，这可是个大问题，先来看看吧。请同事查看系统进程，发现SQLServer的CPU占用率比较高。首先想到的是生成报告的时候是不是偏高，因为之前这块出过问题，关闭了服务程序，还是偏高。是客户端程序引起的吗？但是有这么多的客户端连接，很难要求有人关闭每一个。很简单，断开网络即可。网络断开后，CPU立即掉线。那么问题出在哪里呢？是时候拿出我们的武器了——SQLServerProfiler。使用SQLServerProfiler监控数据库让同事用SQLProfiler监控了大概20分钟，然后保存为trace文件*.rtc。看看哪个SQL有问题：SQL1:找到***的30个报警事件selecttop30a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.NameasAddrName,b.NameasMgrObjName,a.Ch,a.Value,a.Content,a.Level,ag.NameasAgentServerName,a.EventBm,a.MgrObjId,a.Id,a.Cfmoper,a.Cfm,a.Cfmtime,a.State,a.IgnoreStartTime,a..IgnoreEndTime,a.OpUserId,d.NameasMgrObjTypeName,l.UserNameasuserName,f.NameasAddrName2fromeventlogasaleftjoinmgrobjasbona.MgrObjId=b.Idanda.AgentBm=b.AgentBmleftjoinaddrnodeasconb.AddrId=c.Idleftjoinmgrobjtypeasdonb.MgrObjTypeId=d.Idleftjoineventdiraseona.EventBm=e.Bmleftjoinagentserverasagona.AgentBm=ag.AgentBmleftjoinloginUseraslona.cfmoper=l.loginGuidleftjoinaddrnodeasfonag.AddrId=f.Idwhere((MgrObjIdin(selectIdfromMgrObjwhereAddrIdin('','02100000','02113000','02113001','02113001,30'02','02113006','02113007','02113008','02113009','02113010','02113011','02113012','02113013','02113014','02113015','0211'3,'02'1,13002113019','02113020','02113021','02113022','02113023','02113024','02113025','02113026')))或(mgrobjidin('00000000-0000-0000-0000-000000000000','00000000-00000000000000000000000,0-0000-0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000来Currentnumberofalarmsbyalarm2alarm))orderSCbyalarm*time)fromeventlogasaleftjoinmgrobjasbona.MgrObjId=b.Idanda.AgentBm=b.AgentBmleftjoinaddrnodeasconb.AddrId=c.Idleftjoinmgrobjtypeasdonb.MgrObjTypeId=d.Idleftjoineventdiraseona.EventBm=e.BmwhereMgrObjIdin(selectIdfromMgrObjwhereAddrIdin('','02100000','02100001','02100002','02100003','02100004','02100005','02100006','02100007','02100008','02100009','02100010'02'1010,01010,'02100006','01000130','021'0','02100016','02100017','02100018','02100019','02101000','02101001','02101002','02101003','02101004','05'1010,'021010101',07'02101008','02101009','02101010','02101011','02101012','02101013','02101014','02101015','02101016','02101017','02101018','02101019','02101020','02101021','02101022','02101023','02101024','02101025','022000','022001','0220120,1','0755'，'0755002'）和mgrobjidnotin（'00000000-0000-000000000000000000000000000000000000000000000000000000000000000000000000000000000000来-111111111111')这是一个典型的数据获取和分页数据，一个获取分页记录总数，一个获取分页记录，正是获取这里的***事件导致的CPU过高。这里的业务是每个client每3秒执行一次数据库查询，以便显示***告警事件。好了，罪魁祸首找到了，如何解决？哪些SQL语句会导致CPU过高？我在网上查了下面几篇文章，得出以下结论：1.编译和重新编译编译是SqlServer为指令生成执行计划的过程，SqlServer需要to分析指令需要做什么，分析它需要访问的表结构，就是生成执行计划的过程。这个进程主要是做各种计算，所以CPU占用比较集中。执行计划生成后，会缓存在内存中，以供重复使用。但并不是所有的都可以重复使用。很多时候，因为数据量变了，或者数据结构变了，如果执行同一句话，就会重新编译。2、排序（sort）和聚合计算（aggregation）查询时经常会进行orderby、distinct等操作，同时也会进行avg、sum、max、min等聚合计算。数据加载到内存后，最后还需要用到CPU来完成这些计算。所以这些操作的语句CPU使用率会更高。3、表连接（Join）操作当一条语句需要连接两个表时，SQLServer往往会选择NestedLoop或Hash算法。算法的完成需要运行CPU，所以join有时会带来CPU占用比较集中的地方。4.Count(*)语句执行过于频繁，尤其是大表Count()，因为如果Count()后面没有条件，或者条件没有使用索引，会造成全表扫描，并大量的CPU操作我们都知道大概的原因，但是说到上面的两条SQL，似乎都存在上述的问题，那么究竟是哪条是攻击的元凶，又该如何优化呢？查看SQL查询计划SQLServer的查询计划清楚地告诉我们在哪一步消耗的资源最多。先来看一下top30的记录：排序占用了94%的资源。原来是它！同事立马想到用orderno排序会更快。先在SQLServer中执行上面的语句。清除缓存后，大概需要2到3秒，然后sort字段改成orderno。只需不到1秒，而且非常有用。但是orderno的顺序和alarmTime的顺序并不完全一致。orderno的顺序不能代替alarmTime的顺序，怎么办？我觉得，因为选择的是top，那么因为orderno是聚簇索引，那么选择前30条记录，就可以立即返回，不需要遍历整个结果，所以如果alarmTime是索引字段，可以排序被加速？选择top记录时，先尝试索引order子句的字段：IFNOTEXISTS(SELECT*FROMsysindexesWHEREid=OBJECT_ID('eventlog')ANDname='IX_eventlog_alarmTime')CREATENONCLUSTEREDINDEXIX_eventlog_alarmTimeONdbo.eventlog(AlarmTime)正在检查执行计划：看是否有any，刚才那个耗时的Sort查询已经消失了，那么如何验证它是否可以有效的降低我们的CPU呢？是不是一定要去现场Deployment，当然不是。SELECTOP10TEXTAS'SQLStatement',last_execution_timeAS'上次执行时间',(total_logical_reads+total_physical_reads+total_logical_writes)/execution_countAS[AverageIO],(total_worker_time/execution_count)/1000000.0AS[AverageCPUTime(sec)elcount)exe,(to/1000000.0AS(to/1000000.0AS)Elapsed(AverageIO)],execution_countAS"ExecutionCount",qs.total_physical_reads,qs.total_logical_writes,qp.query_planAS"QueryPlan"FROMsys.dm_exec_query_statsqsCROSSAPPLYsys.dm_exec_sql_text(qs.plan_handle)stCROSSAPPLYsys.dm_exec_query_plan(qs.plan_handle)qpORDERBYtotal_elapsed_time/execution_countDESCLet'scomparetheCPUbeforeandafterindexing:明显减少了，通过建立相关索引减少表扫描，我们来看看如何优化count(*)语句，因为上面这句话和count语句的区别在于orderby的排序。老规矩，看查询计划，用语句selectcount(0)fromeventlog看表已经有20多条了记录，每次查询30条数据，都要遍历这个20w的表两次，能不耗cpu吗？让我们看看我们是否可以使用相关条件来减少表扫描。显然，我们可以为MgrObjId创建一个索引：CREATENONCLUSTEREDINDEXIX_eventlog_moidONdbo.eventlog(MgrObjId)但是无论我怎么尝试，索引都没有被使用。会不会是IN子句和NOTIN子句不能使用索引，肯定会造成表扫描。于是上网查了资料，找到了花仔的文章，里面有答案：SQLSERVER对过滤条件（searchargument/SARG）的写法有一些建议。对于不使用SARG运算符的表达式，索引是无用的。与最佳实践相比，它们很难使用。非SARG运算符包括NOT、NOTEXISTS、NOTIN、NOTLIKE和内部函数，如：Convert、Upper等。但这只是说明IN可以被索引。我很困惑。经过一番咨询，得到了答案：用索引不一定好。sqlserver根据你查询中字段值重复的比例来决定扫描表还是索引是合理的。但是我查了一下，重复值不高，怎么会出问题。关键是如果对你选择的字段使用索引，性能会更差。您选择的字段id、addrid、agentbm、mgrobjtypeid和name不在索引中。真是一语惊醒梦中人，缺的是收录指数！！！这篇文章《我是如何在SQLServer中处理每天四亿三千万记录的》已经提到了收录指数的重要性，没想到这里又磕磕绊绊了。在实践中，它真的很重要！通过创建索引让SQL语句遍历索引。嗯，马上建一个相关的索引：IFNOTEXISTS(SELECT*FROMsysindexesWHEREid=OBJECT_ID('eventlog')ANDname='IX_eventlog_moid')CREATENONCLUSTEREDINDEXIX_eventlog_moidONdbo.eventlog(MgrObjId)INCLUDE(EventBm,AgentBM)我们再看看查询计划：看看有没有没有事件日志表的表扫描。对比一下前后的CPU：显然cou??nt优化对于查询top还是有效的。到目前为止，使用这两个查询后，没有出现CPU太高的情况。其他优化方法通过服务器的推送，只有在有事件报警或解除时才查询数据库。优化上面的查询语句，比如count(*)，可以用count(0)来代替优化后的语句，先查询所有的MgrObjId，然后在做连接后给管理对象，location表等添加索引，事件表的插入很简单，会很慢，如何优化？可以按分区创建索引，每天不忙的时候，将新的记录移动到建有索引的分区中。当然，这些优化方法都是后续的事情，我要做的事情也基本结束了。.要断定服务器CPU过高，首先要查看系统进程，确定导致CPU高的进程。SQLServerProfiler可以很容易地监控哪些SQL语句执行时间过长并消耗最多的CPU。通过SQL语句可以查看每条SQL语句消耗的CPU。导致CPU高的是那些计算量大的语句：包括内存排序、表扫描、编译计划等，如果使用Top选择前几条语句，尽量为OrderBy子句建立索引，可以减少需要对所有的选择结果进行排序。使用Count查询记录条数时，尽量为where子句的相关字段建立索引，减少表扫描。如果要连接多个表，则在包含索引中建立相关的表连接字段。通过服务端通知减少SQL语句的查询。通过表分区，尽量减少因为增加索引导致表插入慢的影响。
