[SQL Server]善用Database Engine Tuning Advisor改善SQL效率
摘要
在處理資料庫效能問題時，我們常常會朝向幾個點去找尋問題點：
1. SQL指令是否使用過多的運算？
2. SQL指令是否有子查詢？
3. SQL的Where條件是否過度複雜？
4. Table是否有建立索引鍵？
5. Transaction是否包的太大，導致執行過長？
6. 資料量是否太大？
以上大致是我們在處理資料庫效能時很常見到的一些問題，但若需要一句SQL一句SQL去分析，如果我們的系統SQL指令有上萬句，該從何處去分析呢？以下介紹一個很常使用的SQL Tuning工具—SQL Server Database Engine Tuning Advisor。
SQL Server Database Engine Tuning Advisor
利用此工具我們可以做到以下效果：
- 利用查詢最佳化工具來分析工作負載中的查詢，以建議資料庫索引的最佳混合情況。
- 針對工作負載所參考的資料庫來建議對齊或非對齊的資料分割。
- 建議工作負載所參考之資料庫的索引檢視。
- 分析所提出之變更的效果，其中包括索引用法、資料表之間的查詢分佈，以及工作負載中的查詢效能。
- 建議針對一小組問題查詢來微調資料庫的方式。
- 可讓您指定磁碟空間條件約束之類的進階選項來自訂建議。
- 提供報表來總結針對給定工作負載來實作建議的效果。
- 設想替代方案，讓您以假設性組態的形式來提供可能的設計選項，供 Database Engine Tuning Advisor 進行評估。
其實從以上的說明中，我們簡單一句話說明：Tuning Advisor這工具可以幫我們分析我們對資料庫運行的SQL指令，並給予索引的最佳化建議。
詳細資料請參考：Database Engine Tuning Advisor 概觀
如何開始？
要分析SQL指令，那我們總應該告訴它應該分析哪些指令，若要把指令一句一句收集起來，相信大家都會很火大，因此這邊我們建議最好也最切近實際的做法是透過SQL Profiler去收集某個時段內所執行的所有指令，並對這些指令加以分析，因此這邊我們先使用SQL Profiler來收集SQL吧，首先開啟SQL Server Profiler：
按下左上角的
新增追蹤，跳出以下視窗後請輸入要分析的資料庫資訊：
這一頁可不管，直接按下執行：
接著就可以看到SQL Profiler會攔到所有針對資料庫執行的SQL指令與操作囉：
接下來要作的動作就是等待，等到收集到的SQL指令與觀察的時間區間已足夠我們排除我們所遭遇的效能問題，這段時間可長可短，到也不一定。
若進行完後，我們按下左上角的
停止追蹤，接著選擇檔案-->儲存檔案，將此次追蹤到的SQL指令集合存檔，下例中我將檔案存成mytrace.trc檔：
開始分析
前頭我們已經收集好我們所需要的基本資料了，接下來就是開始進行SQL指令的分析囉，首先我們先打開Database Engine Tuning Advisor，仍然需要我們進行登入，請輸入與前頭SQL Profiler所對應的資料庫相同的連線資料：
登入後出現下方的畫面，請在Workload中選擇File，然後按下
選擇前頭存下來的mytrace.trc檔：
選擇Database for workload analysis，選到我們系統中最主要的資料庫：
選擇要Tuning的資料庫，這邊可以選擇多個，若有多個資料我們在過程中都會使用到，建議都選擇進來一起tune…
設定完成後請按下左上角的
開始進行分析：
分析完成後我們可以看到以下的結果，我們可以看到Tuning後它所建議的索引會被全部列出來，另外還有提供估計的改善比例(Estimated Improvement)：
當分析已完成，也已經取得改善的建議後，我們可以選擇工具列中的Actions，看你是要直接將它的建議套用到資料庫(會自動建立好索引)或者是將這次建議的索引內容存成*sql檔，找時間再進行：
結語
這個工具十分方便，且使用上也非常簡單，但建立索引不保證總是能解決我們遭遇到的SQL效能問題，後續若有機會，我們再做其他分享。
 |
游舒帆 (gipi) 探索原力Co-founder，曾任TutorABC協理與鼎新電腦總監，並曾獲選兩屆微軟最有價值專家 ( MVP )，離開職場後創辦探索原力，致力於協助青少年培養面對未來的能力。認為教育與組織育才其實息息相關，都是在為未來儲備能量，2018年起成立為期一年的專題課程《職涯躍升的關鍵24堂課》，為培養台灣未來的領袖而努力。 |
