<b>摘要：</b>在阿裡雲sql server最佳實踐系列線上直播中，阿裡雲資料庫專家汪建明總結了7大問題并結合案例為大家分享了阿裡雲sql server高cpu使用率問題排查的實踐經驗。
<b>以下内容均根據演講視訊以及ppt整理而成。</b>
<b>演講者簡介</b>
<b>汪建明（花名：風移）</b>，近10年sql server資料庫dba經驗。曾就職于新蛋中國6年、新蛋美國3年半。現任阿裡雲資料庫專家，負責sql server産品線。
<b>分享議程</b>
本文将按照sql server高cpu使用率問題排查的7個方面進行分享：
缺失索引 (missing indexes)
索引碎片 (indexes fragmentation)
資料類型轉換 (data conversion)
非sarg查詢 (non-sarg query)
參數嗅探 (parameter sniffing)
統計資訊 (statistics)
top cpu查詢 (top sql)
<b>一、缺失索引 (missing indexes)</b>
<b>為什麼索引缺失會降低sql server的cpu使用率？</b>
真正排查出的高cpu使用率的第一大因素就是missing indexes，那麼為什麼索引的缺失是sql server cpu使用率的第一大殺手呢？要回答這個問題就需要首先回答什麼是索引。索引的結構其實是基表的某一列或者某幾列資料的投影，并且這些列的資料是按照升序或者降序排列完畢之後的特殊結構，這個特殊結構使得查詢的性能會更加高效，特别是對于經常會使用到的查詢語句。既然索引特殊的結構已經排序完成了，那麼在進行檢索的時候效率就會非常高，可以很快地定位到資料所在的位置，這樣就能夠大大降低sql server本身的io的消耗，io的消耗降低之後cpu的使用率自然也會下降。
<b>發現缺失索引的方法</b>
如何發現哪些表中又缺失了哪些索引呢？第一種方法就是dta (database tuning advisor)。第二種方法就是執行計劃中存在索引缺失的警告，也就是當執行某一條語句的時候，執行計劃會報出一個警告提示這裡缺少一個索引，這個時候就可以将缺失的索引找出來并建立它。第三種方法就是通路系統的動态視圖，大緻有sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_group_stats以及sys.dm_db_missing_index_details這三個視圖，具體怎樣去使用大家可以查閱幫助文檔。
<b>不要盲目地建立缺失的索引</b>
在建立缺失索引時一定不要盲目，一定要確定建立的缺失索引是有效的，這樣做的第一個原因是建立索引會導緻一定的存儲開銷，因為索引的資料結構也會占用資料檔案空間。第二個原因是dml操作會導緻索引的維護成本增加，因為索引的結構是基于表的某列或者某幾列組合出來的資料結構，這個資料結構的一緻性一定是随着基表的資料變化而變化的，當我們進行delete、insert以及update操作的時候也需要去維護索引的資料結構，因為需要保證索引結構資料與基表資料的一緻性，是以就會帶來索引維護成本的上升。
<b>二、索引碎片 (indexes fragmentation)</b>
剛才提到了索引缺失會導緻cpu使用率的升高，而另外一個問題是：是不是索引建立以後cpu的使用率就一定會降低呢？或者是說在索引不缺失的情況下，cpu的使用率就一定不會上升呢？這兩個問題的答案都是否定的。這裡涉及的話題就是索引碎片，這裡的索引碎片可以了解為索引資料頁中的一些空隙，這應該如何了解呢？假如某一個頁裡面是滿的，比如是8k，如果存在25%的空隙，那麼真正有效的資料隻有75%，舉個簡單的例子比如某個表格的索引資料有100個頁，但是碎片率是25%，是以這100個換頁面裡面隻有75個頁面的資料是有效的。是以在索引的碎片率非常高的情況下，索引的效率就會非常低，因為其io的使用率也會非常低。
<b>rebuild indexes</b>
解決索引碎片的方法其實很簡單，也就是進行一個rebuild indexes的操作，做完這個操作之後統計資訊會被更新，相應的執行計劃中的緩存資訊也會被清空，當相同的語句再過來的時候，sql server就會重新進行執行計劃的評估和選擇，并獲得更好的執行計劃。
<b>注意事項</b>
rebuild indexes操作的方式能夠很容易地解決索引碎片問題，但是還是存在三個地方需要大家注意。因為做rebuild indexes操作的時候會導緻資料日志檔案的增長，那麼基于sql server日志檔案的技術比如database mirroring、log shipping以及alwayson等，這些基于日志的技術都會導緻程序變得很慢，因為日志檔案會在短時間内出現暴漲的情況，是以這裡需要提醒大家注意這個問題，在後面也會分享如何解決這些問題。
<b>如何去做rebuild indexes</b>
我們所需要基于的原則是一定在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都會對于資料進行切分，切分之後資料量就會變得更小，這樣産生的影響也會變得更小。
<b>三、資料類型隐式轉換 (data conversion implicitly)</b>
很多同學不了解資料類型的轉換，特别是資料類型的隐式轉換。在這裡和大家簡單分享一下。
<b>什麼是資料類型隐式轉換</b>
sql server在做資料類型比較的時候一定要確定比較運算符兩端的資料類型是一緻的，比如等于、on子句、或者大于等于以及小于等于，一定要確定比較運算符兩端的資料類型是一緻的，這樣才能進行比較。如果資料類型不一緻的話，sql server會自動地進行資料類型的隐式轉換，這個隐式轉換對于使用者而言是比較隐蔽的，使用者可能是毫無感覺的，但是資料庫系統卻在背後做了大量的隐式轉換工作，而且這些工作都是比較會消耗系統性能的，進而導緻了高cpu的使用率。
<b>資料類型轉換原則</b>
sql server資料類型轉換的規則其實很簡單，就是将資料類型從低優先級轉向高優先級，比如char和int資料類型，int的資料類型的優先級要比char高的，那麼在做這兩者資料類型的比較的時候就需要轉換char資料類型到int資料類型上面來，然後再進行比較。
這裡需要提醒大家的是一旦sql server對資料類型進行了隐式轉換，并且隐式轉換發生在正式表的基表上面的時候，是無法進入index seek的，而是會使用性能非常差的index scan，這樣就會使得sql server的io使用率大大升高，io使用率的增加導緻cpu的使用率升高，這就是隐式資料轉換導緻高cpu使用率的理論基礎。
<b>那麼如何避免資料類型的隐式轉換呢？</b>
第一個方法就是review表的資料類型設計，因為在反範式理論中有一個方法是在同一個字段表達同一個含義為了避免多表join的時候采用反範式的設計，在多個表中存儲相同含有的字段，在這種情況下一定要保證這些字段的資料類型是一緻的，在後面進行查詢或者是執行on子句進行join的時候，sql server就不需要在背景進行資料類型的隐式轉換工作了。
第二個方法就是當where語句裡面使用了像“where column = 常量”這種傳入參數的時候，一定要確定傳入的參數的資料類型和基表中這個字段的資料類型是一緻的，這樣才不會導緻資料類型的隐式轉換。其實經常會遇到的問題就是使用者傳入的參數的資料類型比基表字段的資料類型的優先級更高，這時sql server就需要在背景自動轉換基表字段的資料類型，如果基表有一億條資料，那麼sql server就需要對這一億條資料的列資料類型進行轉換并進行比較，這樣對于io的消耗會非常大，進而會導緻cpu使用率的上升。
第三個方法就是去檢查執行計劃，在執行計劃中可以通過一個convert_implict關鍵字知道是否做了xml隐式資料類型轉換。第四個方法是搜尋執行計劃的緩存，可以拿到緩存的xml檔案，在xml檔案中會有隐式資料類型轉換的關鍵字。
<b>四、非sarg查詢 (non-sarg query)</b>
<b>原因</b>
non sarg是什麼呢？其實是由于即使基表的某些列上建立了索引，sql sever的查詢優化器也必須要去掃描所有的行，這樣就會導緻了non sarg查詢。
<b>通常情況</b>
上面講的可能比較理論，通常情況下，是在where字句中，在資料庫基表的字段上使用函數，比如像convert、cast、以及資料類型隐式轉換等，對于時間進行操作的函數，比如取時間差datediff、對時間進行加減的dateadd以及取年的year，以及upper、lower大小寫轉化的函數，對字元串進行操作的rtrim、substring、left以及像like的完全模糊比對、isnull函數以及使用者自定義函數等。
<b>五、參數嗅探 (parameter sniffing)</b>
<b>sql server中的參數嗅探是什麼？</b>
參數嗅探其實是一個非常有意思的話題，那什麼是參數嗅探呢？歸根結底，導緻參數嗅探的原因是由于sql server對執行計劃的編譯和緩存的過程導緻的。想要了解這句話就明白sql server是如何執行一條查詢語句的，當sql server的服務端接收到一條sql語句之後，首先要進行文法檢查，之後進行語義分析，再之後進行編譯，選擇最優的執行計劃路徑，并将所得到的結果緩存到執行計劃緩存中。
<b>參數嗅探的問題所在</b>
而問題就出在編譯的過程中該如何編譯這些查詢語句上，因為某些查詢語句是有參數的，當編譯的時候一定是根據當時傳入的參數的值編譯一個最好的執行計劃，但是當随着時間的推移，資料發生了變更就可能導緻統計資訊發生變化甚至可能發生資料傾斜的情況，如果發生了這樣的情況，那麼之前緩存的執行計劃就可能不是最優的了，因為之前傳入的參數可能是另外一個值，對應的統計資訊可能就不是最優的解法了，這就是導緻參數嗅探的一個原因。
<b>參數嗅探的解決方案</b>
剛才談到了導緻參數嗅探的原因是執行計劃的編譯和緩存過程，那麼如何解決這個問題呢？很簡單的一個思路就是：既然之前緩存的執行計劃不是最優的，那麼就清空這個緩存。這裡為大家提供幾種方法，但是其中也有不太推薦的方法。
1.第一種就是重新開機整個作業系統，因為作業系統重新開機了，記憶體當然就清空了，那麼執行計劃的緩存也會被清空，這時候sql server啟動起來之後，查詢語句送出到sql server服務端，當然會重新編譯、使用最新的執行計劃，這樣可以解決參數嗅探的問題，但是問題在于這樣的做法動作太大了，有點像使用大炮打蚊子，是以這樣做思路是對的，但是方法卻不恰當，是以這一種是不太推薦大家使用的。
2.第二種方法就是重新開機sql server服務，其實這樣也可以解決問題，這個方法會導緻sql server短暫停機和不可服務。這樣的做法比第一種稍微好一點，但是也不是推薦的方法。
3.第三種方法是使用dbcc freeproccache指令來清空執行計劃的緩存，這種方法比第二種方法又稍微好一點，但是這樣還是會清空所有執行計劃的緩存，但仍舊不是最好的方法，這樣有點像是“甯可錯殺一千，也不放過一個”的思維模式，因為真正出現問題就是某一個或者某幾個執行計劃的緩存，如果把所有的執行計劃都清空了是可以解決這樣的問題，但是也會産生“錯殺其他的999人”的問題，是以這也不是最好的解決方法。
4.最好的解決方法就是針對于特定的查詢語句或者存儲過程去清空特定的執行計劃緩存。
5.另外一種是使用query hits option，這将會告訴sql server在執行存儲過程或者查詢語句時每次都會進行重新編譯，而不進行緩存，這也是一種思路。
6.還有一種就是更新統計資訊，這個方法的原因是執行計劃的編譯和最優路徑的選擇基礎資料就是統計資訊，那麼将統計資訊更新之後相應的查詢語句的執行計劃緩存會被清空，下一次執行的時候會重新進行編譯通過最新的統計資訊擷取最新的執行計劃。
7.最後一個方法就是剛才提到的建立缺失索引或者删除不必要的、多餘的以及重複的索引。
<b>六、統計資訊 (statistics)</b>
<b>什麼是sql server中的統計資訊</b>
統計資訊也是非常重要的，但是在很多時候統計資訊往往會被忽略，因為對于使用者而言，統計資訊其實是躲在幕後的英雄。那麼到底什麼是統計資訊呢？
統計資訊實際上為sql server的優化器提供了資料基礎，怎麼了解呢？其實就是sql server在做最優路徑選擇的時候是基于統計資訊的值進行預估的，也就是sql server是基于統計資訊值的分布來選擇執行計劃的最優路徑的。
<b>如何建立統計資訊呢？</b>
建立統計資訊的一種方法是手動建立，另外一種就是系統自動建立，資料庫中有一個選項可以讓資料庫自動建立，第三種就是在建立索引時系統自動建立。
<b>如何更新統計資訊呢？</b>
更新統計資訊的第一種方法是使用update statistics，第二種是使用系統的sys.sp_updatestats存儲過程，第三種方法可以通過stats_date拿到統計資訊最後一次的更新時間。
<b>七、top cpu查詢 (top sql)</b>
最後一部分就是top sql，之前講解了rds sql server高cpu使用率的理論基礎和demo，後面的問題就來了：如何知道哪些語句的cpu使用率分别是多少，并且需要按照cpu使用率倒序排列，也就是我們說的top sql。需要按照cpu使用率的倒叙來排列，可以友善地找出比如像排名前十的最消耗cpu的查詢語句是什麼。那麼如何去實作這樣的排序呢，其實就是使用上圖中的代碼。
當然這裡涉及了兩個比較小的問題，第一個就是如何找出某一個查詢語句總的cpu使用率的倒叙排名，也就是比如某個查詢語句執行了1000次，這1000次總的cpu消耗是一個次元；這個查詢語句每一次查詢的時候的總的top sql是第二個次元。将這個業務鋪開來看，io的read和write也有相同的邏輯，也就是top io read、top io write以及top duration這些都可以通過相應的方法排列出來找到相應的top sql。
從整個過程來看，上面的分享已經基本上覆寫了sql server高cpu使用率的所有的場景。
<b>q&a</b>
<b>1、zyowe：那個語句是自動生成索引缺失建立語句？</b>
<b>2、dmv 裡面 xml 檢測是否存在轉換？</b>
打開實際的執行計劃 ＝> 執行查詢後 => 打開執行計劃xml ＝> 以下兩處會發現資料類型隐式轉化。
第一：
第二：
檢視執行計劃的方法：
第一步：打開實際的執行計劃
第二步：執行查詢語句
第三步：打開執行計劃xml：右鍵點選執行計劃選擇：show execution plan xml
第四步：從打開的xml中，查找關鍵字convert
以上是手動檢視的方法，你完全可以寫一個xml的解析方法，擷取執行計劃緩存中的xml，然後查找關鍵字。
<b>3、david_ho：存儲過程執行時間很長，有100分鐘，但cpu不算高，怎麼破？</b>
<b>by風移：</b>這個問題不是一個高cpu使用率的問題，是一個執行時間過長的問題。這個應該也要非常小心并加以重視，我之前遇到類似的case是，開發人員寫了一個死循環在存儲過程裡面，導緻執行時間很長，一緻無法退出。解決方法：
方法一：使用profiler trace，這幾個事件應該就可以了
這裡需要注意的是，記得設定filter：
第二種方法：使用下面的查詢，不斷執行，看看存儲過程執行到哪個語句慢，針對性的調優
<b>4、動态公式計算有什麼解決思路?就是一個表達式是動态組合的，要計算‘5*(2-1)’=5字元串計算成結果，不是計算列。</b>
<b>by風移：</b>實在不好意思，還是沒太明白這個“動态公式計算”的含義，已經郵件到您163的郵箱，後續我們再溝通。
<b>5、資料庫頻繁的delete insert 造成死鎖， 怎麼破？</b>
<b>6、npf：目前guid作主鍵最大表4千萬條，過億後是否會有性能問題，有沒有解？</b>
<b>by風移：</b>這個問題問的非常好。根據我的經驗，個人極不推薦使用guid做為主鍵，理由如下：
第一：guid字段寬度過長，char(36)，導緻主鍵寬帶非常大；是以，間接也會導緻其他非主鍵索引空間變大（因為非主鍵索引要記錄主鍵的值）。資料空間過大，查詢使用的io自然就越大，cpu使用率就越高，效率相對就越低。
第二：guid值，每次的值大小是不可預知的。當有新的資料進入表中，如果guid主鍵是clustered的話，會導緻資料插入到某兩行資料的中間（比如，之前兩行資料主鍵是a和c，當b資料進來，會導緻插入到a和c之間），這樣會導緻資料移動。
第三：guid值做為主鍵，極易導緻主鍵的索引碎片，你自己可以按照我們之前分享的方法，查查索引碎片就知道了。
<b>建議的做法：</b>
使用identity屬性列，替換guid列做為主鍵。identity屬性列的值是可以預知的，而且下一個主鍵值，一定是大于前面的主鍵值，sqlserver的資料行依次往後寫就好了，不存在往中間插入的情況。好處是：1.int資料類型，寬度大大減少，以此降低了io開銷和cpu開銷；2.沒有資料移動的開銷；3.還可以大大減少索引碎片的機率
<b>7、更新統計資訊會影響查詢嗎？</b>
<b>by風移：</b>會，兩個方面，一個好的，一個壞的：
第一：更新的過程中，可能會導緻短時間的查詢阻塞，不過你可以選擇業務低峰期來做
第二：更新完畢後，會帶來執行計劃評估相對更準确，是以查詢效率更高效。
<b>8、怎麼優化 like ‘%a%’，沒有太明白剛剛說的優化完全模糊比對</b>
<b>by風移：</b>
方法一：使用fulltext解決like完全模糊比對的性能問題，參見我的雲栖文章【sql server fulltext解決like字句性能問題】，連結：https://yq.aliyun.com/articles/64764?spm=5176.8091938.0.0.xuzoxl
方法二:從業務層面來優化，比如直播中我們談論的基于電話号碼查詢功能的case。
<b>9、tony_yang:cpu過高 其他狀态都低是什麼問題</b>
<b>by風移：</b>可能遇到cpu瓶頸了？給您思路：參照我們高cpu使用率的排解方法，先優化資料庫性能，如果已經将資料庫優化了，還是cpu過高，其他狀态都低的話，估計cpu有瓶頸了。
<b>10、老師，同個語句在不同時間段執行效率差别很大，是因為資料庫性能下降嗎？</b>
<b>by風移：</b>如果是相同查詢語句，相同資料結果的話1. 可能是資料庫有blocking，或者是有其他大io，高cpu消耗的查詢語句，影響到你的查詢；2.可能是參數嗅探讨論的情況。
如果相同查詢，不同資料結果集：這個首先應該懷疑的是資料庫傾斜，不同條件值，資料量大小不同，查詢性能不相同，是正常的。需要讀取的資料量大，效率低，反之者高。
<b>11、為什麼更新完統計資訊後，資料庫還是生成了錯誤的執行計劃，清空計劃指南也不行？</b>
<b>by風移：</b>需要清空執行計劃緩存。統計資訊的更新需要做到相應查詢的所有表（有時候是視圖，需要找到視圖中相應的表）。另一個思路可能需要調優索引設計，missing index？duplicate index？index fragmentation？
<b>12、profiler的自動優化推薦是否可靠？</b>
<b>by風移：</b>不可靠，可以作為參考，需要人為判斷。
<b>13、all1019：大表的索引重建什麼時候做比較好？</b>
<b>by風移：</b>思路：大表拆小，比如我們說的partition，然後每個partition逐個重建index。時間的選擇，肯定是業務低峰期，注意使用企業版的online=on選項，進一步減少對業務的影響。時機選擇：< 10% do nothing; 10%~ 30% reorganize; >30% rebuild