如题所述
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 TOP 10 TEXT AS 'SQL Statement'
,last_execution_time AS 'Last Execution Time'
,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count 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中处理每天四亿三千万记录的》已经提到过了,没想到在这里又重新栽了个跟头。实践,真的是太重要了!
通过建立包含索引来让SQL语句走索引
好吧,立马建立相关索引:
IF NOT EXISTS(SELECT * FROM sysindexes WHERE id=OBJECT_ID('eventlog') AND name='IX_eventlog_moid')
CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId) INCLUDE(EventBm,AgentBM)
我们再来看看查询计划:
看到没有,已经没有eventlog表的表扫描了。我们再来比较前后的CPU:
很明显,这个count的优化,对查询top的语句依然的生效的。目前为止,这两个查询用上去之后,再也没有CPU过高的现象了。
其他优化手段
通过服务端的推送,有事件告警或者解除过来才查询数据库。
优化上述查询语句,比如count(*)可以用count(0)替代——参考《SQL开发技巧(二)》
优化语句,先查询出所有的MgrObjId,然后在做连接
为管理对象、地点表等增加索引
添加了索引之后,事件表的插入就会慢,能够再怎么优化呢?可以分区建立索引,每天不忙的时候,把新的记录移入到建好索引的分区
当然,这些优化的手段是后续的事情了,我要做的事情基本完了。
总结
服务器CPU过高,首先查看系统进程,确定引发CPU过高的进程
通过SQLServer Profiler能够轻易监控到哪些SQL语句执行时间过长,消耗最多的CPU
通过SQL语句是可以查看每条SQL语句消耗的CPU是多少
导致CPU高的都是进行大量计算的语句:包括内存排序、表扫描、编译计划等。
如果使用Top刷选前面几条语句,则尽量为Order By子句建立索引,这样可以减少对所有的刷选结果进行排序
使用Count查询记录数时,尽量通过为where字句的相关字段建立索引以减少表扫描。如果多个表进行join操作,则把相关的表连接字段建立在包含索引中
通过服务端通知的方式,减少SQL语句的查询
通过表分区,尽量降低因为添加索引而导致表插入较慢的影响
参考文章
SQLSERVR语句 in和exists哪个效率高本人测试证明
Sql Server Cpu 100% 的常见原因及优化
SQLSERVER排查CPU占用高的情况
人人都是 DBA(XII)查询信息收集脚本汇编
????最后,感谢博客园DBA桦仔的热心指点。
温馨提示:答案为网友推荐,仅供参考