摘要: 前言做了两年多针对淘宝的电子商务数据线下数据系统,越到后面越觉得自己还没入门,不管技术上还是业务上,这篇文章既是对自己的积累的一次梳理,更想的是能在和各位朋友交流中,互相进步。ps:所有字段并不是正式项目所使用字段,请根据自己的业务需求进行酌情查看处理,类目属性,商品,订单结构可以参考淘宝API数据接口进行查看具体字段。商品模块设计商品模块是支撑整个架构的核心,如果这块没设计好,那么所有后期的复杂的统计需求基本都满足不了。为什么这样子设计属性看这里和这里,把品牌从类目中剥离出来是为了降低程序针对商品属性这块的复杂度。这里通过淘宝的添加宝贝的操作来说明上面的数据结构如何满足下面的需求:PS:本来 阅读全文
posted @ 2012-07-05 00:10 活雷锋 阅读(65207) 评论(84) 推荐(135) 编辑
摘要: 淘宝名词解释产品 和 商品的区别:淘宝标准化产品,由类目+关键属性唯一确定。如:手机类目,关键属性是品牌和型号,Nokia N95就是一个产品,nokia是品牌,N95是型号。产品除了关键属性还包括一般信息、销售属性和非关键属性。参考:如"诺基亚N95"就是一个产品。通过类目的关键属性组合来确定唯一的产品。后台标准类目叶子节点下,一组共同特征商品的组合(例如:化妆品+雅芳+保湿单品+容量),属于同... 阅读全文
posted @ 2011-04-21 00:57 活雷锋 阅读(112633) 评论(76) 推荐(74) 编辑
摘要: 在上一篇的文章中,那样的封装让开发变的很爽,好吧,我承认这是面向数据库编程的思维。在一个类中,封装了单表的几乎所有操作,我的看法是,因为ORM封装了所有的与数据库打交道的操作,它就是项目中的数据交换层,由于这个泛型类封装了单对象的操作,那么使得我们独立了所有对象的基本操作,使复用达到最高,使我们关注点集中了在对象关系处理和复杂的业务逻辑中。(希望有经验的人能给我指导或者相互讨论)一.让封装回归正途 多数据库的支持 public class BaseDao<T> where T : Entity { public DbSession db; public BaseDao(DbSess 阅读全文
posted @ 2011-03-24 20:44 活雷锋 阅读(2361) 评论(2) 推荐(1) 编辑
摘要: 引子  你是否被页面上这样的验证烦死:又或者你已经封装了JS库,又或者你在用一些JS验证插件,但是还是得不停的一遍一遍写着正则,提示信息,等待返回结果.恩,我跟你一样,也烦透了!于是,我便做了个毛坯,到今天,把这个毛坯盖成了个茅草屋。好吧,来看看验证要做些什么工作吧。介绍与使用  Step 1:引入JS文件和CSS文件  Step 2:拖入一个验证控件 选择验证类型,能否为空,提示信息等自己所需要... 阅读全文
posted @ 2010-09-03 00:59 活雷锋 阅读(8289) 评论(51) 推荐(45) 编辑

程序猿是如何解决SQLServer占CPU100%的

 

文章目录

 

遇到的问题

有同事反应服务器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','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 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')

这是典型的获取数据并分页的数据,一条获取最新分页记录总数,一条获取分页记录,正是获取最新事件这里导致的CPU过高。这里的业务大概是每个客户端,每3秒执行一次数据库查找,以便显示最新的告警事件。好了,元凶找到了,怎么解决?

有哪些SQL语句会导致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 * 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语句的查询
  • 通过表分区,尽量降低因为添加索引而导致表插入较慢的影响

参考文章

    最后,感谢博客园DBA桦仔的热心指点。


如果您觉得阅读本文对您有帮助,请点一下“推荐”按钮,您的“推荐”将是我最大的写作动力!
如果您想持续关注我的文章,请扫描二维码,关注马非码的微信公众号,我会将我的文章推送给您,并和您一起分享我日常阅读过的优质文章。

本文版权归作者和博客园共有,来源网址:http://www.cnblogs.com/marvin/ 欢迎各位转载,但是未经作者本人同意,转载文章之后必须在文章页面明显位置给出作者和原文连接,否则保留追究法律责任的权利。
 
 
绿色通道: 好文要顶 关注我 收藏该文与我联系 
12
0
 
(请您对文章做出评价)
 
« 上一篇:站在风口,你或许就是那年薪20w+的程序猿
posted @ 2014-12-18 22:32 马非码 阅读(885) 评论(15) 编辑 收藏

 
 
#1楼 2014-12-18 23:47 潇湘隐者  
实际环境中,你所例举的方法不一定有效。我就曾经碰到几起这种案例!
昨天面试被问到了。。。
#3楼[楼主] 2014-12-19 09:18 马非码  
@潇湘隐者
嗯,我只是从我的角度说一下基本的思路,未必能够解决所有的问题。不知道隐者大大遇到什么案例,能否贴几篇博文观摩观摩
#4楼[楼主] 2014-12-19 09:19 马非码  
@信息时代的缔造者
兄弟后来咋样啊?
#5楼 2014-12-19 09:39 pursuer.chen  
这些算是基础的解决方法,建包含索引或者改变排序字段这些都是执行计划选择的优化方案,如果想更加深入了解这些,还是要知道为什么添加了包含索引就会提高查询速度因为索引字段是排序的,它就不会走哈希匹配。
#6楼 2014-12-19 09:50 superunusa  
嗯,这是CPU高的性能的一种。可以使用DMV查询CPU高使用率语句。正方便一些。不错,赞一个。
#7楼[楼主] 2014-12-19 10:03 马非码  
@superunusa
一查DMV,又出现好多名词,真是活到老学到老……
#8楼[楼主] 2014-12-19 10:05 马非码  
@pursuer.chen
有道理,有时间还是要深入了解,掌握了原理才是王道
#9楼 2014-12-19 10:10 pursuer.chen  
@马非码
加油
#10楼 2014-12-19 10:54 wy123  
关于TOP SQL,通过活动监视器查看不是更方便
#11楼 2014-12-19 11:00 ShanksGao  
给朋友提点建议:
1 在高CPU消耗下开sql profiler本身就是将SQL Server消耗雪上加霜.
你应该了解下xevents相关实现方式.了解下等待事件.
2 高CPU消耗的原因很多,就如你所谈的,仔细了解CPU bound是一方面.
至于优化手段,这是一个宽泛的课题,慢慢体会积累吧.
最后支持下,非DBA有如此兴趣值得赞下!
@马非码
拿到offer ~
#13楼 2014-12-19 11:46 阿水  
顶楼主!!!
#14楼[楼主] 2014-12-19 11:47 马非码  
@wy123
兄弟,看了下活动监视器,看起来逼格很高啊,但是用起来很不爽,刷新也不够及时,很多参数也看不到,还是不如用dmv查询来得快
#15楼[楼主] 2014-12-19 11:54 马非码  
@ShanksGao
oh,谢高神指点,研究下这个xevents先。有其他问题再请教下高神,你哪些数据库的文章很给力哈。主要是公司没dba,程序的性能好解决,剩下就是数据库这边了,不得不去研究啊

 

posted @ 2014-12-19 12:44 活雷锋 阅读(17902) 评论(0) 推荐(0) 编辑
摘要: 本系列文章仅从个人有限的对事物的认知出发,如有不同意见,请温和提出态度,毕竟都是成年人,别那么幼稚。 情和义,值千金。 今天和很久没见的朋友L喝酒,L目前是一家不错公司的开发管理,手下10几号开发。中途他给我聊了一个很有意思的话题:公司正在转型,那么由于成本压缩控制会裁掉一些人,由于担心裁人会引发和... 阅读全文
posted @ 2014-08-18 01:37 活雷锋 阅读(1201) 评论(3) 推荐(6) 编辑
摘要: 本系列文章仅从个人有限的对事物的认知出发,如有不同意见,请温和提出态度,毕竟都是成年人,别那么幼稚。我一直都认为,任何正当的职业都一样,本质都是首先养活自己,在满足这个前提下实现为人民服务的崇高理想。我是一个程序员,我很喜欢我的职业和从事这个职业的大部分人。程序员是一个很奇葩的职业,在外界很多人看来... 阅读全文
posted @ 2014-08-15 03:04 活雷锋 阅读(2410) 评论(4) 推荐(1) 编辑
摘要: 好吧~我承认,以下内容来自刚才在床上躺了十分钟之后的想法。8点边吃饭边看一个求职节目,主持人叫来宾点评各个企业,那个姐们点评的有点不太靠谱,这时候,脑袋一闪,如果丫叫我点评博客园,我会怎么说,毕竟是天天被我上的网站,好吧,躺在床上想了十分钟左右,觉得该写下来,大家看个乐吧。我猜想的博客园收入现状 这是我瞎扯,但是从目前来看,博客园的收入就来自于网络广告和一些厂商定投的广告。至于卖T恤,那真挣不了啥钱。如果说是培养出博客园一种文化和一个品牌形象的维护,还是比较靠谱的。我觉得太不靠谱之前看到过和沪江英语的合作,大家觉着吧,有一部分程序员想着锻炼英语,那么直接来沪江吧。可是很大一部分直接走线下了,. 阅读全文
posted @ 2012-04-18 00:35 活雷锋 阅读(10759) 评论(97) 推荐(42) 编辑
摘要: 很久没看到这样一部让我感触颇多的电影.年少的仓持真知寿生活在一个富有的家庭,这个漂亮的小孩痴迷于画画,电影前段画面中的仓持永远脖子上挂着一个画板画着。这孩子行为近乎古怪,上课画画,为了画火车,站在铁轨上等着火车直到火车被迫停下…要是这孩子出生在普通人家,早打的丫半死了。在中国也是一样,大家总会对有钱有势的家庭的子女莫名的好,所以就算他逼停火车,司机知道他是某某家的孩子,也只是对他说:小朋友,请你走开了…也有可能天才就是这种性格拉.这也是他一辈子最让我感动和厌恶的地方…某天,父亲公司破产自缢和后面母亲的自杀,小正太变成了孤儿,被寄养在小伯家里,小伯和婶婶是电视剧里专业的后妈形象,后面因为受不了他 阅读全文
posted @ 2011-12-12 23:23 活雷锋 阅读(640) 评论(1) 推荐(1) 编辑
摘要: IIS7配置.Net程序接管IIS请求 和IIS6有太大的差别 我按照以下配置成功~!Making URL rewriting on IIS 7 work like IIS 6Written on December 11, 2006 by Mark S. Rasmussen inSysadmin: Windows,Sysadmin: IISTweetUpgrading to IIS 7 should be rather transparent, unfortunately that is not the case when it comes to URL rewriting as we kne 阅读全文
posted @ 2011-10-09 16:16 活雷锋 阅读(6085) 评论(0) 推荐(1) 编辑
摘要: 这篇文章的起因 由于在 Session 有没有必要使用它 这篇文章里面对Session对并发访问的影响这个列子中的提出自己的看法,我认为是开玩笑的说了一句两毛钱关系,跟一位叫ice6的朋友发生不愉快的争吵并且我出口伤人…在此对你再一次道歉.由于以前知道浏览器会延迟加载请求,但是并不太清楚原因,所以在当天晚上就好好学习了资料,这几天太忙,今天晚上花时间记录在此,也给不清楚的朋友提供一个信息.浏览器为什么会延迟加载请求 这是一个历史问题:在浏览器刚刚流行的时候,大部分用户是通过拨号来上网,由于受当时的带宽条件的限制,无法使得用户的同时多个请求被处理。同时,当时的服务器的配置也比现在差很多,所以现在 阅读全文
posted @ 2011-08-09 00:56 活雷锋 阅读(14543) 评论(4) 推荐(9) 编辑
摘要: 遇到一个很奇快的问题,在INT自增的表中,插入信息时没有涉及主键字段,报错:Violation of PRIMARY KEY constraint 'PK_****’. Cannot insert duplicate key in object 'dbo.TableName'. 无法在表中插入重复键!直接把语句拿到查询分析器里去执行,也报相同错误。解决办法:1.取消主键,再设置主键,程序照常运行2.执行以下SQL语句,复制得到的结果,执行SELECT 'DBCC CHECKIDENT (' + Table_Name + ')' FROM 阅读全文
posted @ 2011-08-08 21:21 活雷锋 阅读(2563) 评论(0) 推荐(1) 编辑
摘要: 前言 那个啥…前面发了2篇文章讲这个商品表的设计,后面越多需求浮出水面才发现设计依旧有问题,好吧,乐观一点,正如我博客的标题一样,我在进化…^_^为什么要这样设计先说几个需求,看看您现在是如何去实现:一个用户来到我们网站,在前台页面,1.他要买洗发水,他进入了洗发水的类别,他想买带去屑止痒功效的500ml的洗发水,能否直接搜索出来所有品牌带这个功效属性是500ml的洗发水2.接着他要买一件T恤,他想买V领,短袖的T恤,能否直接通过2个属性搜索出所有品牌的T恤展示给他3.他进入一个T恤的详情页面,由于白色卖的比较好,所以白色会比其他颜色贵一些,所以他选择不同颜色+不同尺码的搭配,就会显示出不同的 阅读全文
posted @ 2011-08-03 00:03 活雷锋 阅读(43634) 评论(42) 推荐(22) 编辑
点击右上角即可分享
微信分享提示