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

這篇文章將為大家詳細講解有關 SQL Server 中 CPU 100% 如何解決，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

使用 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)替代

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

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

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

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