SQL Server Performance tuning 兩年菜鳥心得雜談

前言

畢業至今也兩年多了，第一份工作也就是現在的工作，一開始主要擔任後端開發的工程師，串接後端的Service，主要負責商業邏輯層跟資料存取層的開發。

由於公司產業的關係，對於交易效能有極度的需求，為了避免不必要的I/O存取，所以大部分的商業邏輯都寫在資料庫，所以有很多的時間在接觸資料庫的開發。
後來在效能調校的工作上有點小小成績，而且也玩出興趣來了，在前輩的Promote下成為我們team的DB Owner。
在此也特別感謝我們公司的前輩們，放任我選擇自己所愛的領域，自由的發展，也不時的糾正出我的錯誤，才能有今天的我。
還記得那時候剛來公司三個多月就上錯了Script把Production 的table 給Drop掉了(汗)，不過那是另一段故事了...好了進入正題，分享一點小小心得

如何成為一個良好的Performance tuner ?

圖片來源: 書籍 : SQL Server Performance Tuning 效能調校
http://www.books.com.tw/products/0010638550

這張圖是截自 胡百敬 老師所撰寫 : SQL Server Performance Tuning 效能調校 書中所提到的，要成為一個專業的效能調校專家，除了你的專業技術能力，最重要的就是你對於產品的熟悉度了！
如果不能把你的產品當作像是自己小孩一樣關心，有事沒事就花點時間檢查他的health，並定期去建立Baseline， 深入了解所有的Domain，只有單單透過專業的技術能力所提供的Solution，未必是最佳解，因為可能從Business某個地方著手調整就能解決問題了。

不過看上圖會發現，你是整間公司最強的資料庫開發者，還是有4%都要靠運氣了(神來一筆的靈感之類的...)

如何著手進行效能調校 ?

大部分的效能調校都是根據以下流程的 :

• 定義瓶頸(網路? 程式嗎? SQL Server?) :
  這點尤其重要，千萬不要自己效能調校了半天效能瓶頸根本不在DB

• 定義問題點
• 找到發生的原因
  找到了造成效能的瓶頸，接下來就是找出造成這個問題的原因了，如果是Stored procedure，起手式就是查看這支Stored Procedure在Production的Cache plan，根據這些日子的經驗，效能有問題有70%是你本身撰寫的語法就不甚恰當，或者沒有良好的Index...，如果不是這些問題再去查看是否為硬體或者Config的設定，或者這段期間被什麼東西Block，Sniffing...等等，非常多種可能性。

• 評估幾種解決方案
  這個要根據事情的輕重緩急，如果客戶急著要，而且系統正在營運的話，要選擇對系統影響最小幅度的方案，而且要自己評估對解決方案的風險掌控度。
  如果用了Work around的方式，千萬要寫到某個大家都看的地方做紀錄，以我們公司來說會統一記錄到JIRA的一張 ticket，回頭再用完善的Long term solution解決，不然久了100%會被忘記，千萬不要累積過多技術債就是。

• 驗證你的Improvement
  身為一個專業的工程師不隨便說白話講幹話，千萬不要說：我感覺、我猜..，人類對於事情的揣測都是隨著心情浮動的，尤其我們又是從事科學類型的工作，應該讓數據來說話。
  Linus Torvalds : Talk is cheap, show me the code.
  
  驗證流程大致為 :
  1. 建立修改前的Baseline : 可能是execution plan、logical read / physical read...等等
  2. 著手進行修改
  3. 比較自己所建立的Baseline，確認符合自己的預期，如果沒有就再回到第一個步驟，重複到自己滿意為止。

效能調校有一個非常重要的黃金守則：一次只修改一個東西。

千萬不要一次修改了一大堆再去做驗證，說不定你其中的某幾個調整反而會降低效能呢！

永遠遵守80/20法則

 

圖片來源:書籍 : SQL Server Query Performance Tuning, 4/e
https://www.tenlong.com.tw/products/9781430267430

上圖是SQL Server performance tuning的書中所提到的，大部分的效能問題都是寫不好的T-SQL，或者是不恰當的索引設計，剩下的才會是你的硬體或者Config設定，所以從這部分著手通常是最高效益的，如果無法達到預期再往更上面的部份去找問題。
用20%的時間去解決80%的問題，而非花了80%才達到20%的效益。

Performance v.s. Price

先反問自己 : 這樣的效能對於產品來說是可以接受的嗎?

不論是硬體的提升，或者投入下去的人天，其實對於公司來說都是一大成本，同上面的80/20法則。

=> Good enough tuning
 

建立Baseline

Production上面如果沒有定期去建立Baseline的話，如果某天客戶跟你反應系統非常的慢，你沒有之前的數據做比對，根本就無法得知是否是這次的修改造成的，說不定這問題已經存在一年多了，只是客戶的忍受程度變低了。

另外Baseline也同上面所提到的，能協助我們做好效能調校，得知自己的修改上到Production調整了多少幅度。

建立Baseline的方法很多，透過DMV自行撰寫的monitor script、Performance mointor、extended event、Data collector...等等。

後記

一路走來的歷程，也是犯過許多錯誤。

還記得初出社會的時候看到網路上介紹Trigger這個功能，貪求其記錄Log的方便性，就在我們系統上最頻繁做寫入的table上面建立了，後來引發嚴重的效能問題，被電到翻過去了。

從這時候才了解到做好充分的測試，以及不要隨便從網路上看到什麼，就輕易拿來套用的重要性。

雖然到了現在還是很常犯錯，但重要的是能從錯誤中學習就是。

幾個我學習的方法跟大家分享

1. 多花點時間閱讀，下面是我用trello建立自己的書架，有機會歡迎交換看。
   https://trello.com/b/vJDehQpw/max-bookshelf
2. 多討論:千萬不要害怕指出別人的錯誤，透過互相切磋進步最快了，就如台語說：互相漏氣求進步。(不過前提你的公司文化要夠扁平啦!Orz)
3. 知識文件化：
   像我是熱愛使用OneNote的，只有時間充裕的話我就把曾經遇到的問題給記錄下來，或者是網路上不錯的文章也會列到待讀清單，我都認為我的頭腦是RAM，不具有Durability，所以還是乖乖的把東西落到真正的Hard disk就是。

4. 勇於實驗:
   多花點時間在資料庫上面做一下小小的實驗，我是都拿我們公司的底層環境啦!!
5. 召集同好參加讀書會 :
   目前我們team有建立一個關於資料庫的讀書會，經由讀書會強迫自己讀書，也可以吸收別人花很多時間整理好的知識，互相交流下知識才能最大化。