共計 6026 個字符，預計需要花費 16 分鐘才能閱讀完成。

這篇文章給大家介紹 SQL Server 中占 CPU100% 如何解決，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

SQL1：查找 *** 的 30 條告警事件

select top 30 a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.Name as AddrName,b.Name asMgrObjName,a.Ch,a.Value,a.Content,a.Level ,ag.Name asAgentServerName,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)替代

優化語句，先查詢出所有的 MgrObjId，然后在做連接

為管理對象、地點表等增加索引

添加了索引之后，事件表的插入就會慢，能夠再怎么優化呢? 可以分區建立索引，每天不忙的時候，把新的記錄移入到建好索引的分區

當然，這些優化的手段是后續的事情了，我要做的事情基本完了。

總結

服務器 CPU 過高，首先查看系統進程，確定引發 CPU 過高的進程

通過 SQLServer Profiler 能夠輕易監控到哪些 SQL 語句執行時間過長，消耗最多的 CPU

通過 SQL 語句是可以查看每條 SQL 語句消耗的 CPU 是多少

導致 CPU 高的都是進行大量計算的語句：包括內存排序、表掃描、編譯計劃等。

如果使用 Top 刷選前面幾條語句，則盡量為 Order By 子句建立索引，這樣可以減少對所有的刷選結果進行排序

使用 Count 查詢記錄數時，盡量通過為 where 字句的相關字段建立索引以減少表掃描。如果多個表進行 join 操作，則把相關的表連接字段建立在包含索引中

通過服務端通知的方式，減少 SQL 語句的查詢

通過表分區，盡量降低因為添加索引而導致表插入較慢的影響

關于 SQL Server 中占 CPU100% 如何解決就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。