cpu占用过高怎么办 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*FROMsysindexesWHEREid=OBJECT_ID('eventlog')ANDname='IX_eventlog_alarmTime')
CREATENONCLUSTEREDINDEXIX_eventlog_alarmTimeONdbo.eventlog(AlarmTime)
IF NOT EXISTS(SELECT*FROMsysindexesWHEREid=OBJECT_ID('eventlog')ANDname='IX_eventlog_alarmTime')
CREATENONCLUSTEREDINDEXIX_eventlog_alarmTimeONdbo.eventlog(AlarmTime)
在查看执行计划:
看到没有,刚才查询耗时的Sort已经消失不见了,那么怎么验证它能够有效的降低我们的CPU呢,难道要到现场部署,当然不是 。
查看SQL语句CPU高的语句
SELECTTOP10TEXTAS'SQL Statement'
,last_execution_timeAS'Last Execution Time'
,(total_logical_reads+total_physical_reads+total_logical_writes)/execution_countAS[AverageIO]
,(total_worker_time/execution_count)/1000000.0AS[AverageCPUTime(sec)]
,(total_elapsed_time/execution_count)/1000000.0AS[AverageElapsedTime(sec)]
,execution_countAS"Execution Count",qs.total_physical_reads,qs.total_logical_writes
,qp.query_planAS"Query Plan"
FROMsys.dm_exec_query_statsqs
CROSSAPPLYsys.dm_exec_sql_text(qs.plan_handle)st
CROSSAPPLYsys.dm_exec_query_plan(qs.plan_handle)qp
ORDER BYtotal_elapsed_time/execution_countDESC
SELECTTOP10TEXTAS'SQL Statement'
,last_execution_timeAS'Last Execution Time'
,(total_logical_reads+total_physical_reads+total_logical_writes)/execution_countAS[AverageIO]
,(total_worker_time/execution_count)/1000000.0AS[AverageCPUTime(sec)]
,(total_elapsed_time/execution_count)/1000000.0AS[AverageElapsedTime(sec)]
,execution_countAS"Execution Count",qs.total_physical_reads,qs.total_logical_writes