Database Engine Tuning Advisor資料庫引擎優化顧問--zt

​

2.7 Database Engine Tuning Advisor資料庫引擎優化顧問

SQL Server 2005 将之前版本的索引向導（Index Tuning Wizard）單獨擴增成一個應用程式，稱為“Database Engine Tuning Advisor”。協助使用者在尚未深入了解資料庫結構、索引、資料分布統計、索引視圖（Indexed View）和資料分區的運作原理之前，就能夠有效地建立這些對象以提升系統性能，而不至于誤用導緻性能更差。

“Database Engine Tuning Advisor”會針對一個以上資料庫的運作情況分析其工作負荷。工作負荷指的是針對一或多個資料庫執行的多句 T-SQL 文法。當優化資料庫時，“Database Engine Tuning Advisor”會利用存在硬碟上的跟蹤檔案，或存在 SQL Server 資料庫内的跟蹤資料表，抑或是 T-SQL 腳本​​​​來作為工作負荷的輸入。你可以利用 Management Studio 中的查詢編輯器來建立 T-SQL 腳本工作負荷，或是用 Profiler 工具程式，在“使用模闆”選項選擇“Tuning”，以錄制存放在跟蹤檔案或跟蹤資料表的工作負荷。

若應用程式需要使用多個資料庫以完成工作，則我們直接編寫的 T-SQL 通路文法，或通過 SQL Trace 機制以及 Profiler 工具程式錄制的工作負荷内容，經常一句文法通路多個資料庫内的對象。“Database Engine Tuning Advisor”能同時優化多個資料庫，這就比以往版本的“索引向導”有用多了。遇到多資料庫對象，以往的“索引向導”就放棄優化L。現今使用者可以在“Database Engine Tuning Advisor”指定要優化的資料庫集合，便能對所有使用到且被選取的資料庫提出結構上的建議。

另一個與前版不同的是：“Database Engine Tuning Advisor”提供了“限制優化時間”選項，要求它在期限前交出報告。避免你急着要結果，但它卻一直跑個不停，或在某個時間點一定要退出，不然會幹擾線上系統的執行。當然，若分析的資料庫結構和查詢負荷複雜，則時間充裕才可能提供較精确的分析。

通過“開始”菜單上的“程式”－“Microsoft SQL Server 2005”－“性能工具”－“Database Engine Tuning Advisor（資料庫引擎優化顧問）”選項，或是在 Management Studio、Profiler 等程式環境内，都可以通過主菜單打開“Database Engine Tuning Advisor”工具程式，其執行界面如圖2-20所示：

------------------------------------------------------------------------------------------------------------------------------------------------------------

​​​​ 若要直接分析某些 T-SQL 腳本，可通過 Management Studio 查詢編輯器輸入查詢文法，并選取想要分析的部分。接着以滑鼠右鍵點選所用的查詢，并選擇快捷選單中的“在 Database Engine Tuning Advisor 中分析查詢”選項。此時 Management Studio 會将“Database Engine Tuning Advisor”打開，并賦予反白的文法。此時你會在如圖2-20 的“正常”頁簽上方“工作負荷”部分看到多一個“查詢”選項，預設已經設定好且不能改變（此處筆者是直接指定負荷檔案，是以并沒有該選項）。

圖2-20 通過“正常”和“優化選項”頁簽設定會話的内容

打開“Database Engine Tuning Advisor”後，首先可以通過主菜單“檔案”－“建立會話”菜單建立會話（session），并在圖2-20左上方“會話螢幕”視窗中，通過滑鼠右鍵點選某個會話，以快捷菜單的“重新命名會話”菜單，或是在“正常”頁簽的“會話名稱”給予一個有意義的名字。

會話是進行分析的管理機關，内容包含建立資料庫連接配接的資訊、“工作負荷”的來源、需要優化的資料庫與資料表、優化的選項設定以及分析的結果等等，在圖形界面中可以檢視目前與以往優化會話的結果。而主菜單“檔案”内的子選項可以讓你管理這些曆來的會話。

接着是定義工作負荷，預設可以指定“檔案”或“資料表”兩種方式，其中檔案可以指定 .sql、.xml 和 .trc 三種格式的檔案。.sql 檔案内放置一般的 T-SQL 文法，其他兩種格式則是 Profiler 所錄制的工作負荷。而“資料表”則是指定 Profiler 存放跟蹤結果的資料表。

“Database Engine Tuning Advisor”會利用查詢最佳化工具來分析工作負荷中的文法，以建議增加、删除或修改資料庫中的執行個體對象結構。内容包括聚集索引、非聚集索引的最佳設計方式、對齊或非對齊​​​​的資料分區、可用的索引視圖以及需要增加的資料分布統計等。

并提供包括索引用法、資料庫、資料表與字段之間的文法使用百分比以及工作負荷中的查詢性能，套用變更後性能改善的比例等許多報表。

“Database Engine Tuning Advisor”執行完畢後顯示的建議與報表如圖2-21及2-22所示：

-------------------------------------------------------------------------------------------------------------------------------------------------------

[1] 雖然定義索引分區時，可以與其下資料表的分區定義不同。不過，通常是先設計資料表分區之後，再對資料表建立索引，這時 SQL Server 會使用與資料表相同的“資料分區方案”和“分區資料列”分區索引。是以，索引和資料表的的分區定義相同，這稱為“對齊”。

個别為索引指定不同的資料分區方案，或在建立索引時指定不同的檔案夾來存放，則 SQL Server 不會将索引與資料表的分區對齊。若基底資料表并未做資料分區，但想直接對索引資料進行分區，或索引鍵是唯一且不含資料表定義的分區資料列，則會單獨建立索引的分區定義，此稱為非對齊。

如果需要增加額外的資料分區來擴充索引存放空間，或者索引會經常地做資料分區切換，則索引與資料表兩者的分區需要對齊。

圖2-21 分析完畢後，提供建置與删除資料分區、索引、統計以及索引視圖的建議

當分析完畢後，通過檢視如圖2-21的“建議”頁簽，你可以在每一個建議選項前勾選想要立刻執行或存儲的建議，而後通過主菜單“操作”－“套用建議”或“存儲建議”選項來立即對連接配接的執行個體執行修改結構的 T-SQL DDL，或是将這些 DDL 文法存放在硬碟檔案上。切換到如圖2-22的“報表”标記後，可以在上方視窗内看到整個分析過程的摘要資訊以及在下方視窗可以選擇不同的報表，檢視分析細節。

圖2-22 分析完畢後所提供的建議報表

“Database Engine Tuning Advisor”工作過程的中繼資料與建議都可以輸出儲存。其中，會話的定義可以儲存供以後重新打開使用，或是通過指令行工具程式 dta.exe 以批處理的方式來執行分析。例如上述的定義通過主菜單“檔案”－“導出會話定義”菜單，将分析設定儲存為 dta AdvWkdw. xml，則通過 dta 的執行指令可約略如下：

C:\>dta —S .\yukon —E —s mySession2 —ix dta_AdvWkdw.xml —ox MyXMLOutput2.xml

在上述範例中，執行 dta 工具程式時，菜單的指定方式大小寫有别。在此以 —S 菜單設定要連接配接的執行個體，—E 選項設定以內建驗證的方式，也就是以目前執行程式的 Windows 賬号登入，以 —s 選項設定會話的名稱，以 —ix 選項設定上述儲存的設定檔案，以 —ox 設定存放輸出結果的檔案。

整個 dta 的功能與設定相當複雜，你可以參照線上幫助“工具與公共程式參考”——“指令提示符公共程式”——“dta 公共程式”節點。