程式猿是如何解決SQLServer占CPU100%的

標籤：

原文:程式猿是如何解決SQLServer占CPU100%的

文章目錄

• 遇到的問題
  

• 使用SQLServer Profiler監控資料庫

  • SQL1：尋找最新的30條警示事件
    
  • SQL2：擷取當前的總警示記錄數
    
• 有哪些SQL語句會導致CPU過高？
  

• 查看SQL的查詢計劃

  • 選擇top記錄時，盡量為order子句的欄位建立索引
    
  • 查看SQL語句CPU高的語句
    
  • 通過建立相關索引來減少表掃描
    
• 其他最佳化手段
  

• 總結

遇到的問題

有同事反應伺服器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 qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER 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樺仔的熱心指點。

如果您覺得閱讀本文對您有協助，請點一下“推薦”按鈕，您的“推薦”將是我最大的寫作動力！如果您想持續關注我的文章，請掃描二維碼，關注馬非碼的公眾號，我會將我的文章推送給您，並和您一起分享我日常閱讀過的優質文章。
   
本文著作權歸作者和部落格園共有，來源網址：http://www.cnblogs.com/marvin/歡迎各位轉載，但是未經作者本人同意，轉載文章之後必須在文章頁面明顯位置給出作者和原文串連，否則保留追究法律責任的權利。

程式猿是如何解決SQLServer占CPU100%的