SQL Tuning

70%資料庫效能問題都因為不良SQL造成，
所以分享SQL Tuning實戰技巧

Transcript

1. 陳泓翔(Rico) 部落格:RiCo技術農場 SQL TUNING
2. 前言 70%的資料庫效能問題都因為”暗黑SQL” 最頂級的硬體伺服器依然會存在效能低落情況 任何資料庫優化都不及調效一句SQL所帶來的 效益 SQL Tuning Tools只能作為輔助參考 了解資料庫相關知識才是王道
3. 大綱 RBO、CBO SQL Server如何處理單一查詢 統計值、SARG、提示和執行計畫 如何閱讀圖形執行計畫 統計值建立準則 聯接選擇準則 SQL撰寫準則 索引設計準則 總結
4. RBO、CBO RBO(Rule–Based Optimization):基於規則的優化程式 剖析SQL並依內部規則產生執行計畫(編號越小優先權越 大) 。 缺點:靈活性太低(想像世界是美好的)，需大量改寫SQL。 優點:優化過程簡單。 CBO(Cost-Based Optimization):基於成本的優化程序 剖析SQL並根據統計值來估算所使用的CPU和 I/O成本， 然後尋找比對估算最佳執行計畫，成本愈底表示執行計畫越 好。 缺點:優化過程複雜較RBO耗時。 優點:提高SQL靈活性(可以寫很複雜性)。
5. SQL Server如何處理單一查詢
6. 了解統計值、SARG、提示和執行計畫 統計 統計 統計 統計值 值 值 值: 記錄資料內容分佈的情況 記錄資料內容分佈的情況 記錄資料內容分佈的情況 記錄資料內容分佈的情況 查詢最佳化程式找出最佳執行計畫的邏輯步驟必要 查詢最佳化程式找出最佳執行計畫的邏輯步驟必要 查詢最佳化程式找出最佳執行計畫的邏輯步驟必要 查詢最佳化程式找出最佳執行計畫的邏輯步驟必要 資料 資料 資料 資料 統計值過時 統計值過時 統計值過時 統計值過時、 、 、 、樣本不足或沒有統計值可能建立不良 樣本不足或沒有統計值可能建立不良 樣本不足或沒有統計值可能建立不良 樣本不足或沒有統計值可能建立不良 的執行計畫 的執行計畫 的執行計畫 的執行計畫 可以針對資料表某個欄位或索引建立 可以針對資料表某個欄位或索引建立 可以針對資料表某個欄位或索引建立 可以針對資料表某個欄位或索引建立 自動更新或手動更新 自動更新或手動更新 自動更新或手動更新 自動更新或手動更新*
7. SARG格式 格式 格式 格式: 中文叫做查詢參數(意指Where 子句中的所有條件) 讓查詢最佳化程式透過索引快速搜尋資料 符合的運算子:>、<、>=、<=、Between和Like(視萬用字元% 所在位置)。 不符合的運算子:<>、Not、Not in、Not Exists和Not Like..等。 不要對欄位做運算和使用函數..等也不符合SARG SQL Tuning 最直覺的第一步 格式範例: 欄位名稱 運算子 常數或變數 Name = ‘Rico’
8. 提示 提示 提示 提示(Hints): 覆寫查詢最佳化程式決定的過程 資料表提示、查詢提示、聯結提示 大多數情況，查詢最佳化程式會選擇較佳執行計畫*
9. 執行 執行 執行 執行計畫 計畫 計畫 計畫: 描述 SQL Server 執行查詢SQL和所需結果集的實體和 邏輯作業順序 程式快取區(SQL Server記憶體集區) 執行內容+查詢計畫 查詢最佳化程式並不會找出最完美執行計畫
10. 如何閱讀圖形計畫 由右到左、由上而下 箭頭表示作業之間的資料流向 箭頭厚度表示作業之間傳遞的資料列量(越厚資料越 多) 每個圖示都表示一項以執行的邏輯或實體作業
11. 實體作業名稱 實際和估計的資料列 排序 執行數目 運算子/子樹成本
13. 統計值建立準則 查看 SET STATISTICS PROFILE 輸出的Warnings欄位來確定是 否有需要建立統計值(NO STATS、Miss…等) 輸出的Rows和EstimateRows兩個欄位差距太大也是代表統計 值有問題
14. Database Engine Tuning Advisor(DTA) 建議建立統計資料 查詢會從資料子集中選取(篩選的統計值)
16. 聯接選擇準則 Merge(sort merge): 適合連接資料行有索引(clustered or cover)且排序。 成本和所聯接總資料行成正比。 Loop: 適合小型查詢且索引選擇性很高(大海撈針)。 成本和兩輸入資料量成正比。 Hash: 可以有效處理大型資料、無索引和資料差異大的輸入。 一般來說成本較昂貴(平行處理) 。
18. SQL撰寫準則 較低CPU資源 較低I/O資源 較低回覆時間 合理的系統吞吐量(system throughput)
20. 索引設計準則 評估索引數量 評估索引類型 評估索引Key和排列組合 評估索引需求有效性 評估索引重建時間 評估叢集索引Key 評估非叢集索引Key
23. SQL Tuning 總結 1.眼見為憑(Performance Monitor、Profiler Trace、 DMV..等) 2.將非SARG改寫為SARG 3.評估統計值 4.開啟statistics io 和 time 5.執行暗黑SQL並產生實際執行計畫 6.降低高成本作業(80/20法則) 7.沒有最完美的調校(合理的系統吞吐量)
25. 謝謝大家