遇到的問(wèn)題

有同事反應(yīng)服務(wù)器CPU過(guò)高，一看截圖基本都是100%了，my god，這可是大問(wèn)題，趕緊先看看。

讓同事查看系統(tǒng)進(jìn)程，發(fā)現(xiàn)是SQLServer的CPU占用比較高。首先想到的是不是報(bào)表生成的時(shí)候高，因?yàn)檫@塊之前出現(xiàn)過(guò)問(wèn)題，關(guān)掉服務(wù)程序，還是高。難道是客戶(hù)端程序引發(fā)的?但是這么多的客戶(hù)端連接，難不成每個(gè)都叫人關(guān)閉，很簡(jiǎn)單，把網(wǎng)絡(luò)斷開(kāi)即可。網(wǎng)絡(luò)斷開(kāi)之后，CPU立馬下降。那么問(wèn)題到底在哪里呢，是時(shí)候祭出我們的利器了——SQLServer Profiler。

使用SQLServer Profiler監(jiān)控?cái)?shù)據(jù)庫(kù)

讓同事使用SQLProfiler監(jiān)控了大概20分鐘左右，然后保存為跟蹤文件*.rtc。

我們來(lái)看看到底是哪句SQL有問(wèn)題：

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：獲取當(dāng)前的總報(bào)警記錄數(shù)

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')  

這是典型的獲取數(shù)據(jù)并分頁(yè)的數(shù)據(jù)，一條獲取***分頁(yè)記錄總數(shù)，一條獲取分頁(yè)記錄，正是獲取***事件這里導(dǎo)致的CPU過(guò)高。這里的業(yè)務(wù)大概是每個(gè)客戶(hù)端，每3秒執(zhí)行一次數(shù)據(jù)庫(kù)查找，以便顯示***的告警事件。好了，元兇找到了，怎么解決?

有哪些SQL語(yǔ)句會(huì)導(dǎo)致CPU過(guò)高?

上網(wǎng)查看了下文章，得出以下結(jié)論：

1.編譯和重編譯

編譯是 Sql Server 為指令生成執(zhí)行計(jì)劃的過(guò)程。Sql Server 要分析指令要做的事情，分析它所要訪(fǎng)問(wèn)的表格結(jié)構(gòu)，也就是生成執(zhí)行計(jì)劃的過(guò)程。這個(gè)過(guò)程主要是在做各種計(jì)算，所以CPU 使用比較集中的地方。

執(zhí)行計(jì)劃生成后會(huì)被緩存在 內(nèi)存中，以便重用。但是不是所有的都可以 被重用。在很多時(shí)候，由于數(shù)據(jù)量發(fā)生了變化，或者數(shù)據(jù)結(jié)構(gòu)發(fā)生了變化，同樣一句話(huà)執(zhí)行，就要重編譯。

2.排序(sort) 和 聚合計(jì)算(aggregation)

在查詢(xún)的時(shí)候，經(jīng)常會(huì)做 order by、distinct 這樣的操作，也會(huì)做 avg、sum、max、min 這樣的聚合計(jì)算，在數(shù)據(jù)已經(jīng)被加載到內(nèi)存后，就要使用CPU把這些計(jì)算做完。所以這些操作的語(yǔ)句CPU 使用量會(huì)多一些。

3.表格連接(Join)操作

當(dāng)語(yǔ)句需要兩張表做連接的時(shí)候，SQLServer 常常會(huì)選擇 Nested Loop 或 Hash 算法。算法的完成要運(yùn)行 CPU，所以 join 有時(shí)候也會(huì)帶來(lái) CPU 使用比較集中的地方。

4.Count(*) 語(yǔ)句執(zhí)行的過(guò)于頻繁

特別是對(duì)大表 Count() ，因?yàn)?Count() 后面如果沒(méi)有條件，或者條件用不上索引，都會(huì)引起 全表掃描的，也會(huì)引起 CPU 的大量運(yùn)算

大致的原因，我們都知道了，但是具體到我們上述的兩個(gè)SQL，好像都有上述提到的這些問(wèn)題，那么到底哪個(gè)才是***的元兇，我們能夠怎么優(yōu)化?

查看SQL的查詢(xún)計(jì)劃

SQLServer的查詢(xún)計(jì)劃很清楚的告訴了我們到底在哪一步消耗了***的資源。我們先來(lái)看看獲取top30的記錄：

排序竟然占了94%的資源。原來(lái)是它!同事馬上想到，用orderno排序會(huì)不會(huì)快點(diǎn)。先把上述語(yǔ)句在SQLServer中執(zhí)行一遍，清掉緩存之后，大概是2~3秒，然后排序字段改為orderno，1秒都不到，果然有用。但是orderno的順序跟alarmTime的順序是不完全一致的，orderno的排序無(wú)法替代alarmTime排序，那么怎么辦?

我想，因?yàn)檫x擇的是top，那么因?yàn)閛rderno是聚集索引，那么選擇前30條記錄，可以立即返回，根本無(wú)需遍歷整個(gè)結(jié)果，那么如果alarmTime是個(gè)索引字段，是否可以加快排序?

選擇top記錄時(shí)，盡量為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)  

在查看執(zhí)行計(jì)劃： 

看到?jīng)]有，剛才查詢(xún)耗時(shí)的Sort已經(jīng)消失不見(jiàn)了，那么怎么驗(yàn)證它能夠有效的降低我們的CPU呢，難道要到現(xiàn)場(chǎng)部署，當(dāng)然不是。

查看SQL語(yǔ)句CPU高的語(yǔ)句

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做個(gè)對(duì)比：

已經(jīng)明顯減低了。

通過(guò)建立相關(guān)索引來(lái)減少表掃描

我們?cè)賮?lái)看看count(*)這句怎么優(yōu)化，因?yàn)樯厦娴倪@句跟count這句差別就在于order by的排序。老規(guī)矩，用查詢(xún)計(jì)劃看看。 

用語(yǔ)句select count(0) from eventlog一看，該表已經(jīng)有20多w的記錄，每次查詢(xún)30條數(shù)據(jù)，竟然要遍歷這個(gè)20多w的表兩次，能不耗CPU嗎。我們看看是否能夠利用相關(guān)的條件來(lái)減少表掃描。很明顯，我們可以為MgrObjId建立索引：

CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId) 

但是無(wú)論我怎么試，都是沒(méi)有利用到索引，難道IN子句和NOT IN子句是沒(méi)法利用索引一定會(huì)引起表掃描。于是上網(wǎng)查資料，找到樺仔的文章，這里面有解答：

SQLSERVER對(duì)篩選條件(search argument/SARG)的寫(xiě)法有一定的建議

對(duì)于不使用SARG運(yùn)算符的表達(dá)式，索引是沒(méi)有用的，SQLSERVER對(duì)它們很難使用比較優(yōu)化的做法。非SARG運(yùn)算符包括

NOT、、NOT EXISTS、NOT IN、NOT LIKE和內(nèi)部函數(shù)，例如：Convert、Upper等

但是這恰恰說(shuō)明了IN是可以建立索引的啊。百思不得其解，經(jīng)過(guò)一番的咨詢(xún)之后，得到了解答：

不一定是利用索引就是好的,sqlserver根據(jù)你的查詢(xún)的字段的重復(fù)值的占比，決定是表掃描還是索引掃描

有道理，但是我查看了下，重復(fù)值并不高，怎么會(huì)有問(wèn)題呢。

關(guān)鍵是，你select的字段，這個(gè)地方使用索引那么性能更差，你select字段 id,addrid,agentbm,mgrobjtypeid,name都不在索引里。

真是一語(yǔ)驚醒夢(mèng)中人，缺的是包含索引!!!關(guān)于包含索引的重要性我在這篇文章《我是如何在SQLServer中處理每天四億三千萬(wàn)記錄的》已經(jīng)提到過(guò)了，沒(méi)想到在這里又重新栽了個(gè)跟頭。實(shí)踐，真的是太重要了!

通過(guò)建立包含索引來(lái)讓SQL語(yǔ)句走索引

好吧，立馬建立相關(guān)索引：

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)  

我們?cè)賮?lái)看看查詢(xún)計(jì)劃：

看到?jīng)]有，已經(jīng)沒(méi)有eventlog表的表掃描了。我們?cè)賮?lái)比較前后的CPU： 

很明顯，這個(gè)count的優(yōu)化，對(duì)查詢(xún)top的語(yǔ)句依然的生效的。目前為止，這兩個(gè)查詢(xún)用上去之后，再也沒(méi)有CPU過(guò)高的現(xiàn)象了。

其他優(yōu)化手段

• 通過(guò)服務(wù)端的推送，有事件告警或者解除過(guò)來(lái)才查詢(xún)數(shù)據(jù)庫(kù)。
• 優(yōu)化上述查詢(xún)語(yǔ)句，比如count(*)可以用count(0)替代
• 優(yōu)化語(yǔ)句，先查詢(xún)出所有的MgrObjId，然后在做連接
• 為管理對(duì)象、地點(diǎn)表等增加索引
• 添加了索引之后，事件表的插入就會(huì)慢，能夠再怎么優(yōu)化呢?可以分區(qū)建立索引，每天不忙的時(shí)候，把新的記錄移入到建好索引的分區(qū)

當(dāng)然，這些優(yōu)化的手段是后續(xù)的事情了，我要做的事情基本完了。

總結(jié)

• 服務(wù)器CPU過(guò)高，首先查看系統(tǒng)進(jìn)程，確定引發(fā)CPU過(guò)高的進(jìn)程
• 通過(guò)SQLServer Profiler能夠輕易監(jiān)控到哪些SQL語(yǔ)句執(zhí)行時(shí)間過(guò)長(zhǎng)，消耗最多的CPU
• 通過(guò)SQL語(yǔ)句是可以查看每條SQL語(yǔ)句消耗的CPU是多少
• 導(dǎo)致CPU高的都是進(jìn)行大量計(jì)算的語(yǔ)句：包括內(nèi)存排序、表掃描、編譯計(jì)劃等。
• 如果使用Top刷選前面幾條語(yǔ)句，則盡量為Order By子句建立索引，這樣可以減少對(duì)所有的刷選結(jié)果進(jìn)行排序
• 使用Count查詢(xún)記錄數(shù)時(shí)，盡量通過(guò)為where字句的相關(guān)字段建立索引以減少表掃描。如果多個(gè)表進(jìn)行join操作，則把相關(guān)的表連接字段建立在包含索引中
• 通過(guò)服務(wù)端通知的方式，減少SQL語(yǔ)句的查詢(xún)
• 通過(guò)表分區(qū)，盡量降低因?yàn)樘砑铀饕鴮?dǎo)致表插入較慢的影響