738
阿裏雲RDS SQL Server－最佳實踐—高CPU使用率問題排查
摘要：在阿裏雲SQL Server最佳實踐係列在線直播中，阿裏雲數據庫專家汪建明總結了7大問題並結合案例為大家分享了阿裏雲SQL Server高CPU使用率問題排查的實踐經驗。本期直播回顧鏈接：https://yq.aliyun.com/webinar/play/237 更多係列精彩課程直播，盡在 雲數據庫經典案例和最佳實踐專場，等待你的參與！
演講者簡介
汪建明（花名：風移），近10年SQL Server數據庫DBA經驗。曾就職於新蛋中國6年、新蛋美國3年半。現任阿裏雲數據庫專家，負責SQL Server產品線。
分享議程
本文將按照SQL Server高CPU使用率問題排查的7個方麵進行分享：
- 缺失索引 (Missing Indexes)
- 索引碎片 (Indexes Fragmentation)
- 數據類型轉換 (Data Conversion)
- 非SARG查詢 (Non-SARG Query)
- 參數嗅探 (Parameter Sniffing)
- 統計信息 (Statistics)
- TOP CPU查詢 (TOP SQL)
一、缺失索引 (Missing Indexes)
真正排查出的高CPU使用率的第一大因素就是Missing Indexes，那麼為什麼索引的缺失是SQL Server CPU使用率的第一大殺手呢？要回答這個問題就需要首先回答什麼是索引。索引的結構其實是基表的某一列或者某幾列數據的投影，並且這些列的數據是按照升序或者降序排列完畢之後的特殊結構，這個特殊結構使得查詢的性能會更加高效，特別是對於經常會使用到的查詢語句。既然索引特殊的結構已經排序完成了，那麼在進行檢索的時候效率就會非常高，可以很快地定位到數據所在的位置，這樣就能夠大大降低SQL Server本身的IO的消耗，IO的消耗降低之後CPU的使用率自然也會下降。
發現缺失索引的方法
如何發現哪些表中又缺失了哪些索引呢？第一種方法就是DTA (Database Tuning Advisor)。第二種方法就是執行計劃中存在索引缺失的警告，也就是當執行某一條語句的時候，執行計劃會報出一個警告提示這裏缺少一個索引，這個時候就可以將缺失的索引找出來並創建它。第三種方法就是訪問係統的動態視圖，大致有sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_group_stats以及sys.dm_db_missing_index_details這三個視圖，具體怎樣去使用大家可以查閱幫助文檔。
不要盲目地創建缺失的索引
在創建缺失索引時一定不要盲目，一定要確保創建的缺失索引是有效的，這樣做的第一個原因是創建索引會導致一定的存儲開銷，因為索引的數據結構也會占用數據文件空間。第二個原因是DML操作會導致索引的維護成本增加，因為索引的結構是基於表的某列或者某幾列組合出來的數據結構，這個數據結構的一致性一定是隨著基表的數據變化而變化的，當我們進行Delete、Insert以及Update操作的時候也需要去維護索引的數據結構，因為需要保證索引結構數據與基表數據的一致性，所以就會帶來索引維護成本的上升。
這部分的相關Demo請參考雲棲社區的博客：https://yq.aliyun.com/articles/72265
二、索引碎片 (Indexes Fragmentation)
剛才提到了索引缺失會導致CPU使用率的升高，而另外一個問題是：是不是索引創建以後CPU的使用率就一定會降低呢？或者是說在索引不缺失的情況下，CPU的使用率就一定不會上升呢？這兩個問題的答案都是否定的。這裏涉及的話題就是索引碎片，這裏的索引碎片可以理解為索引數據頁中的一些空隙，這應該如何理解呢？假如某一個頁裏麵是滿的，比如是8K，如果存在25%的空隙，那麼真正有效的數據隻有75%，舉個簡單的例子比如某個表格的索引數據有100個頁，但是碎片率是25%，所以這100個換頁麵裏麵隻有75個頁麵的數據是有效的。所以在索引的碎片率非常高的情況下，索引的效率就會非常低，因為其IO的使用率也會非常低。
解決索引碎片的方法其實很簡單，也就是進行一個Rebuild Indexes的操作，做完這個操作之後統計信息會被更新，相應的執行計劃中的緩存信息也會被清空，當相同的語句再過來的時候，SQL Server就會重新進行執行計劃的評估和選擇，並獲得更好的執行計劃。
注意事項
Rebuild Indexes操作的方式能夠很容易地解決索引碎片問題，但是還是存在三個地方需要大家注意。因為做Rebuild Indexes操作的時候會導致數據日誌文件的增長，那麼基於SQL Server日誌文件的技術比如Database mirroring、Log shipping以及alwayson等，這些基於日誌的技術都會導致進程變得很慢，因為日誌文件會在短時間內出現暴漲的情況，所以這裏需要提醒大家注意這個問題，在後麵也會分享如何解決這些問題。
如何去做Rebuild Indexes
我們所需要基於的原則是一定在100%的需要時才去做Rebuild Indexes，那些使用率比較低的，哪怕是碎片率很高的表也不會太過於關注，比如一些很小的表或者是heap的表，對於很小的表而言，SQL Server在做執行計劃的時候發現表格很小則會走Table Scan而不是Index Seek或者Index Scan的操作。第二個原則是在Rebuild Indexes的時候一定要去對每一個索引級別進行索引碎片率的檢查，而不要盲目對整個表級Rebuild Index。第三個原則是當發現索引的碎片率處於不同的級別的時候選擇的處理方法也是不一樣的，如果碎片率在10%以下，那麼就不需要去做Rebuild Indexes操作，如果索引碎片率在10%到30%之間，應該選擇做reorganize操作，當索引碎片率大於30%，可以做Rebuild Indexes操作。這裏還請主要，使用SQL Server的版本，如果是企業版本，請選擇ONLINE＝ON選項，以較小Rebuild Index對應用程序對影響。
還有一點需要提醒大家的就是在做Rebuild Indexes操作的時候一定要選擇在業務的低峰期，因為Rebuild Indexes是一個IO密集型的操作，所以會非常消耗IO。除此之外，當存在Database mirror或者Log shipping以及alwayson的時候，如何做Rebuild Indexes才能夠使影響最小呢？這裏使用的技術是table partition，可以在大表上麵建立table partition，然後逐個partition去做Rebuild Indexes，因為每個partition都會對於數據進行切分，切分之後數據量就會變得更小，這樣產生的影響也會變得更小。
這部分的相關Demo請參考雲棲社區的博客：https://yq.aliyun.com/articles/72348
三、數據類型隱式轉換 (Data Conversion Implicitly)
很多同學不了解數據類型的轉換，特別是數據類型的隱式轉換。在這裏和大家簡單分享一下。
SQL Server在做數據類型比較的時候一定要確保比較運算符兩端的數據類型是一致的，比如等於、On子句、或者大於等於以及小於等於，一定要確保比較運算符兩端的數據類型是一致的，這樣才能進行比較。如果數據類型不一致的話，SQL Server會自動地進行數據類型的隱式轉換，這個隱式轉換對於用戶而言是比較隱蔽的，用戶可能是毫無感知的，但是數據庫係統卻在背後做了大量的隱式轉換工作，而且這些工作都是比較會消耗係統性能的，從而導致了高CPU的使用率。
數據類型轉換原則
SQL Server數據類型轉換的規則其實很簡單，就是將數據類型從低優先級轉向高優先級，比如char和int數據類型，int的數據類型的優先級要比char高的，那麼在做這兩者數據類型的比較的時候就需要轉換char數據類型到int數據類型上麵來，然後再進行比較。
注意事項
這裏需要提醒大家的是一旦SQL Server對數據類型進行了隱式轉換，並且隱式轉換發生在正式表的基表上麵的時候，是無法進入Index Seek的，而是會使用性能非常差的Index Scan，這樣就會使得SQL Server的IO使用率大大升高，IO使用率的增加導致CPU的使用率升高，這就是隱式數據轉換導致高CPU使用率的理論基礎。
那麼如何避免數據類型的隱式轉換呢？
第一個方法就是Review表的數據類型設計，因為在反範式理論中有一個方法是在同一個字段表達同一個含義為了避免多表join的時候采用反範式的設計，在多個表中存儲相同含有的字段，在這種情況下一定要保證這些字段的數據類型是一致的，在後麵進行查詢或者是執行on子句進行join的時候，SQL Server就不需要在後台進行數據類型的隱式轉換工作了。
第二個方法就是當where語句裏麵使用了像“Where column = 常量”這種傳入參數的時候，一定要確保傳入的參數的數據類型和基表中這個字段的數據類型是一致的，這樣才不會導致數據類型的隱式轉換。其實經常會遇到的問題就是用戶傳入的參數的數據類型比基表字段的數據類型的優先級更高，這時SQL Server就需要在後台自動轉換基表字段的數據類型，如果基表有一億條數據，那麼SQL Server就需要對這一億條數據的列數據類型進行轉換並進行比較，這樣對於IO的消耗會非常大，進而會導致CPU使用率的上升。
第三個方法就是去檢查執行計劃，在執行計劃中可以通過一個CONVERT_IMPLICT關鍵字知道是否做了XML隱式數據類型轉換。第四個方法是搜索執行計劃的緩存，可以拿到緩存的XML文件，在XML文件中會有隱式數據類型轉換的關鍵字。
以上就是由於隱式數據類型轉換導致高CPU使用率的場景，這樣的場景也是非常多見的，這部分的相關Demo請參考雲棲社區的博客：https://yq.aliyun.com/articles/72420
四、非SARG查詢 (Non-SARG Query)
Non SARG是什麼呢？其實是由於即使基表的某些列上建立了索引，SQL Sever的查詢優化器也必須要去掃描所有的行，這樣就會導致了Non SARG查詢。
通常情況
上麵講的可能比較理論，通常情況下，是在where字句中，在數據庫基表的字段上使用函數，比如像Convert、Cast、以及數據類型隱式轉換等，對於時間進行操作的函數，比如取時間差Datediff、對時間進行加減的Dateadd以及取年的Year，以及Upper、Lower大小寫轉化的函數，對字符串進行操作的Rtrim、Substring、Left以及像Like的完全模煳匹配、Isnull函數以及用戶自定義函數等。
大家在寫SQL的where等語句對於基表進行函數運算的時候一定要注意這裏麵存在一個Non SARG查詢會導致CPU使用率的上升。這部分的相關Demo請參考雲棲社區的博客：https://yq.aliyun.com/articles/72482
五、參數嗅探 (Parameter Sniffing)
參數嗅探其實是一個非常有意思的話題，那什麼是參數嗅探呢？歸根結底，導致參數嗅探的原因是由於SQL Server對執行計劃的編譯和緩存的過程導致的。想要理解這句話就明白SQL Server是如何執行一條查詢語句的，當SQL Server的服務端接收到一條SQL語句之後，首先要進行語法檢查，之後進行語義分析，再之後進行編譯，選擇最優的執行計劃路徑，並將所得到的結果緩存到執行計劃緩存中。
參數嗅探的問題所在
而問題就出在編譯的過程中該如何編譯這些查詢語句上，因為某些查詢語句是有參數的，當編譯的時候一定是根據當時傳入的參數的值編譯一個最好的執行計劃，但是當隨著時間的推移，數據發生了變更就可能導致統計信息發生變化甚至可能發生數據傾斜的情況，如果發生了這樣的情況，那麼之前緩存的執行計劃就可能不是最優的了，因為之前傳入的參數可能是另外一個值，對應的統計信息可能就不是最優的解法了，這就是導致參數嗅探的一個原因。
參數嗅探的解決方案
剛才談到了導致參數嗅探的原因是執行計劃的編譯和緩存過程，那麼如何解決這個問題呢？很簡單的一個思路就是：既然之前緩存的執行計劃不是最優的，那麼就清空這個緩存。這裏為大家提供幾種方法，但是其中也有不太推薦的方法。
1.第一種就是重啟整個操作係統，因為操作係統重啟了，內存當然就清空了，那麼執行計劃的緩存也會被清空，這時候SQL Server啟動起來之後，查詢語句提交到SQL Server服務端，當然會重新編譯、使用最新的執行計劃，這樣可以解決參數嗅探的問題，但是問題在於這樣的做法動作太大了，有點像使用大炮打蚊子，所以這樣做思路是對的，但是方法卻不恰當，所以這一種是不太推薦大家使用的。
2.第二種方法就是重啟SQL Server服務，其實這樣也可以解決問題，這個方法會導致SQL Server短暫停機和不可服務。這樣的做法比第一種稍微好一點，但是也不是推薦的方法。
3.第三種方法是使用DBCC FREEPROCCACHE命令來清空執行計劃的緩存，這種方法比第二種方法又稍微好一點，但是這樣還是會清空所有執行計劃的緩存，但仍舊不是最好的方法，這樣有點像是“寧可錯殺一千，也不放過一個”的思維模式，因為真正出現問題就是某一個或者某幾個執行計劃的緩存，如果把所有的執行計劃都清空了是可以解決這樣的問題，但是也會產生“錯殺其他的999人”的問題，所以這也不是最好的解決方法。
4.最好的解決方法就是針對於特定的查詢語句或者存儲過程去清空特定的執行計劃緩存。
5.另外一種是使用Query Hits Option，這將會告訴SQL Server在執行存儲過程或者查詢語句時每次都會進行重新編譯，而不進行緩存，這也是一種思路。
6.還有一種就是更新統計信息，這個方法的原因是執行計劃的編譯和最優路徑的選擇基礎數據就是統計信息，那麼將統計信息更新之後相應的查詢語句的執行計劃緩存會被清空，下一次執行的時候會重新進行編譯通過最新的統計信息獲取最新的執行計劃。
7.最後一個方法就是剛才提到的創建缺失索引或者刪除不必要的、多餘的以及重複的索引。
以上這些都是解決參數嗅探的方法，這部分的相關Demo請參考雲棲社區的博客：https://yq.aliyun.com/articles/61767
六、統計信息 (Statistics)
統計信息也是非常重要的，但是在很多時候統計信息往往會被忽略，因為對於用戶而言，統計信息其實是躲在幕後的英雄。那麼到底什麼是統計信息呢？
統計信息實際上為SQL Server的優化器提供了數據基礎，怎麼理解呢？其實就是SQL Server在做最優路徑選擇的時候是基於統計信息的值進行預估的，也就是SQL Server是基於統計信息值的分布來選擇執行計劃的最優路徑的。
如何創建統計信息呢？
創建統計信息的一種方法是手動創建，另外一種就是係統自動創建，數據庫中有一個選項可以讓數據庫自動創建，第三種就是在創建索引時係統自動創建。
如何更新統計信息呢？
更新統計信息的第一種方法是使用Update statistics，第二種是使用係統的sys.sp_updatestats存儲過程，第三種方法可以通過Stats_date拿到統計信息最後一次的更新時間。
這部分的相關Demo請參考雲棲社區的博客：https://yq.aliyun.com/articles/67176
七、TOP CPU查詢 (TOP SQL)
當然這裏涉及了兩個比較小的問題，第一個就是如何找出某一個查詢語句總的CPU使用率的倒敘排名，也就是比如某個查詢語句執行了1000次，這1000次總的CPU消耗是一個維度；這個查詢語句每一次查詢的時候的總的TOP SQL是第二個維度。將這個業務鋪開來看，IO的Read和Write也有相同的邏輯，也就是TOP IO Read、TOP IO WRITE以及TOP Duration這些都可以通過相應的方法排列出來找到相應的TOP SQL。
從整個過程來看，上麵的分享已經基本上覆蓋了SQL Server高CPU使用率的所有的場景。
更多關於阿裏雲SQL Server最佳實踐的精彩文章，請移步風移的雲棲社區個人主頁：https://yq.aliyun.com/users/1034676001110800
Q&A
1、zyowe：那個語句是自動生成索引缺失創建語句？
By風移：參見我的雲棲社區帖子［RDS SQL Server - 最佳實踐 - 高CPU使用率係列之索引缺失］中的“索引缺失”部分，鏈接：https://yq.aliyun.com/articles/72265
2、DMV 裏麵 xml 檢測是否存在轉換？
By風移：參見我的雲棲帖子［RDS SQL Server - 最佳實踐 - 高CPU使用率係列之數據類型轉換］，鏈接：https://yq.aliyun.com/articles/72420
打開實際的執行計劃 ＝> 執行查詢後 => 打開執行計劃XML ＝> 以下兩處會發現數據類型隱式轉化。
第一：
第二：
查看執行計劃的方法：
第一步：打開實際的執行計劃
第二步：執行查詢語句
第三步：打開執行計劃XML：右鍵點擊執行計劃選擇：show Execution Plan xml
第四步：從打開的xml中，查找關鍵字Convert
以上是手動查看的方法，你完全可以寫一個xml的解析方法，獲取執行計劃緩存中的xml，然後查找關鍵字。
這裏有一篇類似的文章，參見我的雲棲社區文章【RDS SQL Server - 專題分享 - 巧用執行計劃緩存之索引缺失】，鏈接：https://yq.aliyun.com/articles/93785
3、david_ho：存儲過程執行時間很長，有100分鍾，但cpu不算高，怎麼破？
By風移：這個問題不是一個高CPU使用率的問題，是一個執行時間過長的問題。這個應該也要非常小心並加以重視，我之前遇到類似的Case是，開發人員寫了一個死循環在存儲過程裏麵，導致執行時間很長，一致無法退出。解決方法：
方法一：使用Profiler Trace，這幾個事件應該就可以了
這裏需要注意的是，記得設置Filter：
第二種方法：使用下麵的查詢，不斷執行，看看存儲過程執行到哪個語句慢，針對性的調優
select
@@servername AS Server
,B.Text
,sql_statement = (SELECT TOP 1 SUBSTRING(B.Text,stmt_start / 2+1 ,
(
(
CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),B.text)) * 2)
ELSE stmt_end END
) - stmt_start) / 2+1
)
)
,A.hostName
,A.program_name
,A.loginame
,db_name(a.dbid) AS DbName
,A.status
,A.lastwaittype
,A.waitresource
,A.spid
,A.blocked
,A.login_time
,currnet_time = GETDATE()
,duration = DATEDIFF(MINUTE,login_time,GETDATE())
--,A.*,B.*
from sys.sysprocesses AS A
cross apply sys.dm_exec_sql_text(A.sql_handle) aS B
where B.text like '%YourStoreProcedureName%'
AND A.spid <> @@SPID4、動態公式計算有什麼解決思路?就是一個表達式是動態組合的，要計算‘5*(2-1)’=5字符串計算成結果，不是計算列。
By風移：實在不好意思，還是沒太明白這個“動態公式計算”的含義，已經郵件到您163的郵箱，後續我們再溝通。
5、數據庫頻繁的delete insert 造成死鎖， 怎麼破？
By風移：請期待7月20號的直播分享【阿裏雲SQL Server最佳實踐：死鎖排查方法匯總】，鏈接地址：https://promotion.aliyun.com/ntms/act/apsaradblive.html
6、npf：目前guid作主鍵最大表4千萬條，過億後是否會有性能問題，有沒有解？
By風移：這個問題問的非常好。根據我的經驗，個人極不推薦使用guid做為主鍵，理由如下：
第一：guid字段寬度過長，char(36)，導致主鍵寬帶非常大；因此，間接也會導致其他非主鍵索引空間變大（因為非主鍵索引要記錄主鍵的值）。數據空間過大，查詢使用的IO自然就越大，CPU使用率就越高，效率相對就越低。
第二：guid值，每次的值大小是不可預知的。當有新的數據進入表中，如果guid主鍵是Clustered的話，會導致數據插入到某兩行數據的中間（比如，之前兩行數據主鍵是a和c，當b數據進來，會導致插入到a和c之間），這樣會導致數據移動。
第三：GUID值做為主鍵，極易導致主鍵的索引碎片，你自己可以按照我們之前分享的方法，查查索引碎片就知道了。
建議的做法：
使用identity屬性列，替換guid列做為主鍵。Identity屬性列的值是可以預知的，而且下一個主鍵值，一定是大於前麵的主鍵值，SQLServer的數據行依次往後寫就好了，不存在往中間插入的情況。好處是：1.int數據類型，寬度大大減少，以此降低了IO開銷和CPU開銷；2.沒有數據移動的開銷；3.還可以大大減少索引碎片的概率
可以參見我的雲棲社區帖子：【SQLTest係列之INSERT語句測試】，中的結論部分，鏈接地址：https://yq.aliyun.com/articles/64375
7、更新統計信息會影響查詢嗎？
By風移：會，兩個方麵，一個好的，一個壞的：
第一：更新的過程中，可能會導致短時間的查詢阻塞，不過你可以選擇業務低峰期來做
第二：更新完畢後，會帶來執行計劃評估相對更準確，因此查詢效率更高效。
8、怎麼優化 like ‘%a%’，沒有太明白剛剛說的優化完全模煳匹配
By風移：
方法一：使用fulltext解決LIKE完全模煳匹配的性能問題，參見我的雲棲帖子【SQL Server FullText解決Like字句性能問題】，鏈接：https://yq.aliyun.com/articles/64764?spm=5176.8091938.0.0.xuZoxl
方法二:從業務層麵來優化，比如直播中我們談論的基於電話號碼查詢功能的Case。
9、tony_yang:cpu過高 其他狀態都低是什麼問題
By風移：可能遇到CPU瓶頸了？給您思路：參照我們高CPU使用率的排解方法，先優化數據庫性能，如果已經將數據庫優化了，還是CPU過高，其他狀態都低的話，估計CPU有瓶頸了。
10、老師，同個語句在不同時間段執行效率差別很大，是因為數據庫性能下降嗎？
By風移：如果是相同查詢語句，相同數據結果的話1. 可能是數據庫有Blocking，或者是有其他大IO，高CPU消耗的查詢語句，影響到你的查詢；2.可能是參數嗅探討論的情況。
如果相同查詢，不同數據結果集：這個首先應該懷疑的是數據庫傾斜，不同條件值，數據量大小不同，查詢性能不相同，是正常的。需要讀取的數據量大，效率低，反之者高。
11、為什麼更新完統計信息後，數據庫還是生成了錯誤的執行計劃，清空計劃指南也不行？
By風移：需要清空執行計劃緩存。統計信息的更新需要做到相應查詢的所有表（有時候是視圖，需要找到視圖中相應的表）。另一個思路可能需要調優索引設計，missing Index？Duplicate Index？Index Fragmentation？
12、profiler的自動優化推薦是否可靠？
By風移：不可靠，可以作為參考，需要人為判斷。
13、all1019：大表的索引重建什麼時候做比較好？
By風移：思路：大表拆小，比如我們說的partition，然後每個partition逐個重建Index。時間的選擇，肯定是業務低峰期，注意使用企業版的ONLINE=ON選項，進一步減少對業務的影響。時機選擇：< 10% do nothing; 10%~ 30% reorganize; >30% rebuild
最後更新：2017-06-11 19:32:55