前言

　　效能優化是資料庫運維人員和中、高階軟體開發人員的必備技能，很多時候老司機和新司機的區別就在寫出的東西是否優化。

　　博主接觸過近千家客戶的系統，這些系統都存在著各種各樣的效能問題。那麼如何透徹的瞭解我們的資料庫效能問題？今天就用一個案例來說明效能優化的那點兒事兒。

　　PS:很多技術人員對優化有一套自己的理解，在閱讀本文前請放下你自己的理解。

　　正所謂：跟著博主不迷路，博主帶你上高速！

　 點開案例跟著博主的思路看看優化這些事兒 ： 本文案例Demo

瞭解系統環境
　　優化首先要知道資料庫在一個什麼樣的硬體/軟體環境下執行？設定是怎麼樣的？記憶體、CPU這些是否能完全被應用？資料庫體量多大？

　　首先我們先看一下系統的設定：

　　軟體層面，我們要知道我們的作業系統版本，SQL Server版本，以及對應版本的硬體限制（如32位元系統不開AWE無法使用超過4G記憶體、server 2008 標準版最大支援32G記憶體等）

　　本例中我們可以看出，系統環境沒有異常問題，SQL Server的修補程式不是最新的，CPU資源不充足，可能CPU會成為系統的瓶頸。

全域性層面看效能

　　全域性層面看問題主要指綜合伺服器的各種指標表象定位系統的瓶頸或問題，在效能優化中最忌諱的就是看到一個指標馬上就下手，針對一個指標的判斷是盲目的，很可能使問題偏離本身的根本原因，也可能使優化根本無法解決根本問題而只是表象得到了緩解。

　
效能計數器
　　CPU：在大量時間內CPU的使用率達到100%，說明CPU已經成為瓶頸。

 

　　記憶體：記憶體計數器生命週期在11點時已經降到0，惰性寫入器也彪高，說明記憶體也存在壓力，而且比較嚴重。

 

 

 

 

　　磁碟：磁碟的平均佇列很高（一般系統最佳情況佇列應該低於2），並且讀佇列和寫佇列都很高。由於記憶體存在壓力，所以現在無法判斷磁碟的壓力是由於記憶體不足引起的還是磁碟速度不能滿足需要。

 

 

 

 

 

 

 　　其他計數器：

 

　　可以看到系統中全表掃面的次數比較多，這表明很多查詢沒有應用索引。

 

 

 

 

　　系統在11點左右和11點24左右發生大量鎖等待，並且等待時間很長（超過150s）

 

 

 

　　通過很多類計數器能綜合看出系統的問題。這裡不一一細說了

 　　

 

系統等待

 

　　等待是另一個可以全域性層面看系統的指標，系統執行的卡慢問題很大部分是因為等待而引起的，那麼等待的型別也是可以很直觀的反映出系統的問題。

 

　　幾個主要等待：　　

 

　　ASNC_NETWORK_IO：一般反應出有部分查詢可能返回大量資料，請加查具體的等待語句是否需要返回如此多的資料。

 

　　WAITFOR ：可能是設定了CDC釋出訂閱或程式中使用了語句waitfor delay

 

　　CXPACKET：CPU的排程等待。

 

　　LCK_M_U ：更新語句之間的語句阻塞。

 

　　WRITELOG：說明程式中有迴圈的插入跟新操作而頻繁的寫入紀錄檔，磁碟速度不能滿足寫入頻率而造成。

 

 

 

 

綜合分析

 

　　綜合系統等待和效能計數器，我們基本可以判定出來系統存在以下問題：

 

　　系統的CPU、記憶體、磁碟均存在較大的壓力，尤其CPU負荷接近100%，系統中存在大量表掃面可能缺失比多索引。系統中有的語句可能要返回大量的不必要資料，系統鎖情況嚴重，等待時間很長，語句執行時間也必然很長。

 

　　語句執行的整體情況：由於上述的問題影響，那麼系統中必然存在大量的長時間語句！

 

 

 

解決問題

 

　　問題的定義是很重要的一步，從全域性的多項指標綜合分析，讓所有問題無所遁形。定位問題後我們先來看一下解決這些問題的基本步驟。

 

　　本案例是自己模擬的一個情況，所以雖然在表象上來看資源壓力很大，但實際在執行的語句不多，場景也有限，但在生產系統如果存在這樣的表象，那麼說明你的系統效能問題非常嚴重急需一次詳細的優化了。

 

　　那麼下面也介紹一下生產系統遇到這樣的問題應該怎麼優化，有哪些必要的步驟。

 

步驟一 針對系統問題對資料庫進行全面的優化，提升整體效率

 

　　很多人優化可能直奔語句，認為語句就可以解決效能的所有問題，其實這樣的觀點是不全面的，系統的設定，資料庫的設定，索引的規劃等都是解決效能的必要步驟。

 

　　例如：系統中的語句都是最佳的，資料庫執行還是很慢，可能就是因為你的CHECKDB設定的問題，也有可能因為你自動收縮沒有關閉而導致的效能問題。

 

優化作業系統設定
　　針對伺服器進行設定檢查，檢視是否有設定不合理或可以優化的設定項，比如是否設定了虛擬記憶體？伺服器層面是否限制的資源使用？伺服器是否高效能模式執行？

 

優化資料庫層面的設定
　　針對資料庫引數進行合理設定使硬體充分發揮硬體功能，優化不合理設定，降低對資料庫造成衝擊的可能性。比如：最大並行度？最大記憶體？

 

 

是否大量缺失索引

 

　　大量索引缺失必然導致語句效能不佳，並且消耗大量的系統資源，很可能就會造成上面伺服器高壓力的表象

 

 

 

 

刪除無用索引

 

　　針對資料庫中無用的索引進行刪除。提升更新操作的時間。

 

 

 

刪除重複索引

 

　　針對資料庫中重複的索引進行刪除。提升更新操作的時間。

 

 

 

對重點語句建索引

 

　　針對系統中消耗大的語句或執行次數多的語句進行分析，評估語句效能問題，並建立合適的索引提，降低語句的資源消耗，升語句執行效率。

 

 

 

解決阻塞

 

　　解決語句間的阻塞，這需要分析語句的阻塞鏈，到底語句被什麼樣的操作阻塞了，為什麼會阻塞？

　　很多新手經常問的問題：為什麼我有的時候查很快有的時候查就很慢？ 答：大多數情況就是你的語句被阻塞了。

 

 

 

優化TempDB

 

　　針對TempDB調優，減少TempDB資源爭用導致的壓力。本例中可以死看到有TempDB的爭用等待，所以對TempDB的優化也是必要的。

 

 

 

優化紀錄檔碎片

 

　　針對紀錄檔增大，帶來的紀錄檔碎片問題進行優化。

 

清除索引碎片

 

　　檢查系統的索引維護情況，並針對碎片過大的表進行碎片清除操作。主要體現在系統中有老化的索引，索引的老化導致索引的效能不高或失效。

 

一階段預期效果

 

　　一階段的優化是對效能的整體提升，效能提升也會很明顯，針對不同系統提升一般在2-3倍。

 

步驟二 處理熱點問題

 

　　處理熱點問題主要是在階段一的基本優化後針對重點的語句進行調優，可能包含建立索引，修改寫法，查詢提示，計劃嚮導等等。

　　在語句調優中請主要關注：是否有缺失索引，是否存在隱式轉換，語句的執行時間、CPU、邏輯讀寫量、物理讀寫量、佔用TempDB空間等資訊。

 

　　例：這樣一條語句經過第一階段的優化並沒有太大的提升，而且資源消耗依然很大，那麼我們可以針對這條語句進行詳細的二階段優化。

 

 

 

簡單的優化一下

 

 

 

 

 

　　只是簡單的改了下語句的寫法時間有7秒變成1秒，記憶體消耗從300+MB 變成 1MB

 

二階段預期效果

    階段二的優化屬於細緻的優化步驟，要針對更為具體的語句、具體的情況。經過本階段優化可以使系統中大部分語句從寫法、設定、執行指標都趨於優化值。

 

步驟三 針對業務

 

　　這個步驟需要配合開發人員，到底哪些功能依然慢？執行了哪些語句？是領導用的功能？還是一般可以慢的功能？如果大領導用的功能，那可能你就需要多花些心思了。這部分這裡就不展開說了。

 

三階段預期效果

 

    第三階段屬於最細緻的階段，可以結合業務真正對等的消滅系統中存在問題。

 

導圖

 

　　針對效能優化奉上幾個圖希望能幫助資料庫從業者梳理一下優化的思路（個人思路僅供參考，不完善的地方也請見諒）

 

CPU：

 

 

 

　　記憶體：

 

 

 

　　磁碟：

 

 

　　等待：

 

 

 

 總結

 

　　在效能優化中最忌諱的就是看到一個指標馬上就下手，針對一個指標的判斷是盲目的，很可能使問題偏離本身的根本原因，也可能使優化根本無法解決根本問題而只是表象得到了緩解。

　　本文只是通過一個例子簡述一下優化的基本思路，希望幫助更多資料庫從業者，瞭解效能優化。

　　本文只闡述了思路，具體的各部分解決方式請參見我的系列文章：SQL SERVER全面優化-------Expert for SQL Server 診斷系列

　　效能的調優是一個持續性的工作，不是一次解決了問題以後就可以高枕無憂了，定期的巡檢也是資料庫從業者必要的工作之一，做到及早發現及早解決。

 

　　巡檢系列文章請參見：輕鬆精通資料庫管理之道——運維巡檢系列