章立民研究室

您已經學會如何建立索引，也讀了許 多索引的設計準則與注意事項，甚至也自行做了多次測試，不過筆者相信還是有非常多人無法肯定自己所設計與建立的索引究竟能否提昇系統平日各項作業的速度。 基於此原因，筆者要指導您如何使用 SQL Server 的相關工具來替您評選與分析索引，並提出相關的建議。

請注意：本文所使用的「索引調校練習」資料庫已附於「SQL Server 2005 完全實戰」一書的範例光碟中。

善用 Database Engine Tuning Advisor

或許您的功力不怎麼高深，經驗更是貧乏，且對資料庫的架構、系統負載、與 SQL Server 內部運作模式都不十分瞭解，沒關係，只要有了 Database Engine Tuning Advisor（或稱 DTA），您仍舊能輕鬆建立出一組最適切的索引。

附註：
SQL Server 提供兩種介面的 Database Engine Tuning Advisor ，一個是圖形化介面，另外一個是命令列介面的 dta.exe 公用程式，本書僅介紹圖形化介面，如果您要對 dta.exe 公用程式有更多的認識，請自行參考 SQL Server 線上叢書。

Database Engine Tuning Advisor 最為人所稱道者，就是它能具體建議該替特定資料庫的各個資料表建立哪些索引，它甚至還可以讓您執行這些建議。不過話又說回來，要評選出適切的索引，總得有個評量的對象吧！也就是說，您必須提出一個例行作業的工作負載來讓 Database Engine Tuning Advisor 加以評估。工作負載就是能具體呈現您資料庫平日所進行之各項活動的代表性樣本，工作負載可以是下列兩者之一：

• 一個 SQL 指令檔（.sql）。
• 一個檔案或資料表，而此一檔案或資料表係記錄內含 SQL 批次或遠端程序呼叫（RPC）事件類別、Event Class 和 Text 欄位的 SQL Server Profiler 追蹤。

比方說，您可以將平日經常執行的新增、修改、刪除、與查詢作業的代表性程式碼存放在一個 .sql 檔中，然後由 Database Engine Tuning Advisor 來加以評估，它便會告知您該建立哪些索引才能提昇這些作業的速度。

歸納起來，Database Engine Tuning Advisor 的功能主要有下列各項：

• 利用查詢最佳化工具（Query Optimizer）去分析您所提供之工作負載中的命令敘述，並具體建議一組最佳的索引。
• 針對某些查詢提出一些調校資料庫的建議，並透過多種報表讓您瞭解套用這些建議的效果。
• 執行整合式微調作業來評量各種不同類型的資料庫結構設計，並提出一個分析報告來說明若根據其建議進行修改索引將獲得哪些具體改善。比方說，Database Engine Tuning Advisor 可以根據不同的索引鍵值欄組合，來提升查詢的效率。
• 允許您自行設定 Database Engine Tuning Advisor 的索引調校參數。比方說，您的應用程式需要存取多個資料庫才能完成工作，此時就可以設定 Database Engine Tuning Advisor 評量與調校多個資料庫。或者您可以限制 Database Engine Tuning Advisor 微調所耗用的時間，一般來說，指定較長的微調時間可以分析更多的工作負載，也可以得到更好的分析結果。

請注意，當遇到下列狀況時，Database Engine Tuning Advisor 無法對索引提出建言：

• 系統資料表中的索引。
• 正處於單一使用者模式的資料庫。
• PRIMARY KEY 與 UNIQUE 條件約束的索引。基於此原因，筆者建議您，於使用 Database Engine Tuning Advisor 前，還是應該先自行替資料表建立 PRIMARY KEY 與 UNIQUE 條件約束，並使用 FOREIGN KEY 條件約束去定義資料表間的關聯性連結。
• 您要 Database Engine Tuning Advisor 進行微調的資料表與工作負載中的資料表不同。
• 工作負載儲存在遠端伺服器的追蹤資料表中。基於安全考量，您應該將追蹤資料表複製到欲執行 Database Engine Tuning Advisor 的伺服器上，或是改用追蹤檔案。
• Database Engine Tuning Advisor 無法替 SQL Server 7.0 或更舊版本中的資料庫進行微調。
• Database Engine Tuning Advisor 係自資料表取樣資料來評估出什麼樣的索引能提昇效率，因此如果資料表中的資料記錄不夠多，Database Engine Tuning Advisor 將無法提出建議。
• 如果 Database Engine Tuning Advisor 發現沒有任何索引能比既存的索引更能提昇效率，它也不會提出建議。
• 如果不是 db_owner 資料庫角色或系統管理員（sysadmin）固定伺服器角色成員的使用者來執行 Database Engine Tuning Advisor。
• 微調超過您所指定的時間。

光是紙上談兵也不是辦法，現在就請大家遵循下列步驟進行，實地學習如何使用 Database Engine Tuning Advisor 來替我們評選出適切的索引。請特別注意，第一次使用 Database Engine Tuning Advisor 時，要由隸屬系統管理員（sysadmin）固定伺服器角色成員的使用者來啟動初始化作業，爾後只要是 db_owner 資料庫角色或系統管理員（sysadmin）固定伺服器角色成員的使用者就都可以進行微調作業。

1. 請執行下列三項操作之一來啟動Database Engine Tuning Advisor：

• 從 SQL Server Management Studio 的「工具」功能表中選取「Database Engine Tuning Advisor」指令。
• 從「開始」功能表依序選取「程式集／Microsoft SQL Server 2005／效能工具、Database Engine Tuning Advisor（TW）」程式項目。
• 如圖表 1 所示，於 SQL Server Management Studio「查詢編輯器」中按一下滑鼠右鍵，選取「在 Database Engine Tuning Advisor 中分析查詢」指令。當然您也可以直接於「查詢編輯器」中，按下工具列上的「在 Database Engine Tuning Advisor 中分析查詢」按鈕 。

圖表 1

2. 緊接著將出現「連接到伺服器」視窗，請於「伺服器名稱」下拉選項選擇要微調的 SQL Server，接著根據您實際的環境將相關選項設定完畢，然後按一下「連接」按鈕。

3. 接下來，您必須進行下列設定，以便指定分析工作階段名稱、工作負載、選取要進行調校的資料庫與資料表、與日後是否要可以查閱分析記錄。請特別注意，如果您是第一次使用 Database Engine Tuning Advisor，必須要等待初始化完成後才能進行下列的設定:

• 請在「一般」標籤所在頁面的「工作階段名稱」中鍵入分析工作階段的名稱，Database Engine Tuning Advisor 會自動以當時登入的使用者、日期與時間作為預設的工作階段名稱。
• 如前所述，您必須提供一個工作負載給 Database Engine Tuning Advisor 評估。如果您的工作負載已事先建立並儲存於一個檔案中，請選取「工作負載」項目中的選項鈕 ○ 檔案。如果您的工作負載係儲存於某一個資料庫的資料表中，請選取選項鈕 ○ 資料表。以本例而言，筆者係將工作負載存放於範例光碟之 CH7 目錄中的 CH7_Demo005.sql，因此請按一下選項鈕  ○ 檔案，接著按一下「瀏覽工作負載檔案」按鈕 ，並利用「選取工作負載檔案」視窗選取CH7_Demo005.sql（如圖表2所示）。

圖表 2

請注意：
假設您欲使用 SQL 指令檔（.sql）作為工作負載，且指令檔內含中文，請記得於存檔時，使用 Unicode-字碼頁 1200 的編碼方式存檔（如圖表 3 所示）。

圖表 3

• 您必須決定要使用 Database Engine Tuning Advisor 去分析哪些資料庫或是某些資料表，並提出索引方面的建議。於這個步驟中，如果要分析某個資料庫，請將您需要分析之資料庫所在列最左側的核取方塊勾選起來。 如果欲分析某個資料表，請按一下欲分析之資料表所屬之資料庫所在列的「按一下即可選取個別資料表」超連結，或是按下最右側的下拉式清單來選取要分析的資料 表。由於要分析的資料表愈多，所需的分析時間也就愈長，因此請慎選資料表。
  
  以本例而言，我們要分析「索引調校練習」資料庫，因此請勾選該資料庫所在列最左側的核取方塊。
• 如果您希望將這次微調的紀錄保留下來，以供後續分析之用，請讓核取方塊 □ 儲存微調記錄 保持被勾選的狀態；反之，如果您不想保留這次的微調記錄，可考慮不勾選核方塊 □ 儲存微調記錄。

4. 完成上述設定，請按下「微調選項」標籤來切換至其頁面。本步驟的工作項目有二，一是決定分析的細膩程度，一是設定 Database Engine Tuning Advisor 的調校參數（如圖表 4 所示）。

圖表 4

• 工作負載的內容愈多，相對的 Database Engine Tuning Advisor 就需要更多的時間才能完成整個分析工作，如果您要限制分析工作負載可用的時間長度，請讓核取方塊  □ 限制微調時間 保持被勾選的狀態，然後在停止時間下拉式清單中指定要停止分析的日期與時間。在指定時間終了之前，Database Engine Tuning Advisor 會產生一項分析建議，不過由於並不是分析得很徹底，因此其建議結果將不會使您得到最佳的整體效能改善。
  
  預設的終了時間是我們開啟這個工作階段之後的一個小時，假如我們所要分析的工作負載很大，沒有花上一時半刻是無法完成的，此時就應該取消勾選核取方塊 □ 限制微調時間，讓 Database Engine Tuning Advisor 完全沒有微調時間的限制，以便分析更多的工作負載並產生極為正確且有效的建議。

附註：
如果是透過 dta.exe 公用程式來分析工作負載，預設的終了時間是八個小時之後。

• 如果您要設定 Database Engine Tuning Advisor 的調校參數，請按下「進階選項」按鈕來開啟圖表 5 所示的畫面，您可以依序設定分析的建議最多能使用多少空間、建議之索引的總大小上限、以及於執行每一個索引的建議時，是否可以存取資料表。
  
  如果不想影響 SQL Server 執行效能，請選取 ○ 所有建議都是離線；如果您希望讓 Database Engine Tuning Advisor 儘可能地自動替您決定最好的方式，請選取 ○ 如果可能，產生線上建議；反之，您於執行索引建議時，即使影響 SQL Server 效能也無所謂，請選取 ○ 只產生線上建議。在本例中，我們將選取 ○ 如果可能，產生線上建議 來讓 Database Engine Tuning Advisor 自動決定最好的執行索引方式。
  
  完成調校參數設定，請按下「確定」按鈕。

圖表 5

• 接著指定分析結果應該要新增哪些索引，您可以在「資料庫中要使用的實體設計結構（PDS）」項目中依照圖表 6 所示的選項進行設定。請特別注意，這個設定會與下面兩個選項的設定相互影響。在本例中，我們選取 ○ 索引與索引檢視。

 圖表 6

• 在這個設定中，您要決定是否讓 Database Engine Tuning Advisor 提出資料庫分割的建議，這將會影響下一個步驟的設定。如果您希望 Database Engine Tuning Advisor 在分析結果中，不要提出任何分割資料表的建議，請選取「要採用的分割策略」項目中的選項鈕 ○ 沒有資料分割；反之，如果您希望可以得到分割資料表的建議，請選取選項鈕 ○ 完整的資料分割。假如您希望瞭解 Database Engine Tuning Advisor 所提出的資料庫分割建議，可以選取選項鈕 ○ 對齊的資料分割。
  
  由於本例中「索引調校練習」資料庫的資料記錄還不是很多，我們將指定使用沒有資料分割選項。
• 最後您必須決定要 Database Engine Tuning Advisor 保留或移除哪些索引，做法有下列五種（如圖表 7 所示）。特別值得一提的是，當您選取使用保留對齊的資料分割選項時，請記得選取「要採用的分割策略」項目中之對齊的資料分割選項。
  
  以本例而言，我們指定使用不要保留現有的 PDS 選項，讓 Database Engine Tuning Advisor 替我們決定要移除或是新增哪些索引。

 圖表 7

5. 終於，我們可以開始進行分析工作，請按一下工具列上的「開始分析」按鈕 ，此時 Database Engine Tuning Advisor 會開始進行分析（如圖表 8 所示），請稍安勿躁，靜待分析結果。

圖表 8

圖表 9

6. 當顯示圖表 9 所示的「建議」頁面畫面時，表示已完成分析作業。「索引建議」清單方塊中所列示者，即是 Database Engine Tuning Advisor 所建議的索引。我們可以清楚地看出，該替哪一個資料表建立單一鍵值或多重鍵值之叢集或唯一索引。此外，它也會告知您，如果採用建議的索引，會使工作負載提昇多少百分比的效能。

值得一提的是，您只要按下「報表」頁面標籤，並進行下列操作，即可檢視更詳盡的評估報告：

• 如圖表 10 所示，當切換到「報表」頁面中，請從「選取報表」下拉式清單方塊中選取「索引使用方式報表（建議的）」選項，會一一列出 Database Engine Tuning Advisor 所建議之各個索引被參考使用的次數以及它們於工作負載中的使用率，索引的使用率愈高，表示此索引愈加充分被用來提昇相關作業的速度。同時您可以在「微調摘要」窗格中，看到下列的摘要分析報告：
  
  • 調校的日期、時間、被調校的伺服器與資料庫。
  • 工作負載的來源，是使用檔案還是資料表。
  • 限制分析工作負載可用的時間長度。
  • 分析作業使用了多長的時間。
  • 如果採用建議的索引，預計可以提昇多少的工作負載效能。
  • 先前在「微調選項」頁面標籤中，按下「進階選項」按鈕所指定之分析建議最多能使用的最大空間。如果當初沒指定大小，Database Engine Tuning Advisor 會自動設定。
  • 目前索引所使用的空間大小。
  • 在建立新索引之後，預計將使用的空間大小。
  • 工作負載中的事件個數。
  • 工作負載中，已經被微調的事件個數。如果有事件沒有被微調，該事件將顯示在「進度」頁面標籤的「微調記錄」清單中。
  • 工作負載中的陳述式個數。
  • 工作負載中，使用 SELECT、INSERT、DELETE 與 UPDATE 陳述式且已被微調後所佔用的比重。
  • 建議建立或刪除之索引的個數。
  • 建議建立或刪除之索引檢視表的個數。
  • 建議建立之統計資料的個數。

圖表 10

• 如圖表 11 所示，從「選取報表」下拉式清單方塊中選取「索引使用方式報表（目前的）」選 項，會一一列出每一個資料表原先所擁有之各個索引被參考使用的次數以及它們於工作負載中的使用率。同樣的，索引的使用率愈高，表示此索引愈加充分被用來提 昇相關作業的速度；反之，如果某個索引的使用率是 0，表示工作負載中沒有任何一項作業使用此索引來提昇效率，亦即此索引是多餘的。

圖表 11

• 如圖表 12 所示，從「選取報表」下拉式清單方塊中選取「事件頻率報表」選項，會依序列出工作負載中的事件個數。

圖表 12

• 如圖表 13 所示，從「選取報表」下拉式清單方塊中選取「陳述式成本報表」選項，會依序列出工作負載中某個陳述式在使用目前之索引與建議之索引組態下，預計改善的工作負載百分比。

圖表 13

• 如圖表 14 所示，從「選取報表」下拉式清單方塊中選取「陳述式詳細資料報表」選項，您可以清楚檢視在目前之索引組態與建議之索引組態下，所有陳述式的作業成本。

圖表 14

• 如圖表 15 所示，從「選取報表」下拉式清單方塊中選取「陳述式-索引關聯性報表（目前的）」選項，會一一列出工作負載中每一個陳述式所參考使用之原先的索引名稱與該索引所屬的資料表。

圖表 15

• 如圖表 16 所示，從「選取報表」下拉式清單方塊中選取「陳述式-索引關聯性報表（建議的）」選項，會一一列出工作負載中每一個陳述式參考使用建議的索引組態資訊。

圖表 16

• 如圖表 17 所示，從「選取報表」下拉式清單方塊中選取「陳述式成本範圍報表」選項，您可以清楚檢視在目前之索引組態與建議之索引組態下，於各區間中工作負載陳述式的個數。以本例來說，在目前之索引組態下，有 7 個工作負載陳述式是在 0～10% 的範圍裡；而在使用建議之索引組態下，有 12 個工作負載陳述式是在此範圍裡。這代表套用 Database Engine Tuning Advisor 的建議之後，使用 0～10% 之成本的工作負載會增加 5 個陳述式。

圖表 17

• 如圖表 18 所示，從「選取報表」下拉式清單方塊中選取「索引詳細資料報表（目前的）」選項，會一一列出工作負載中每一個既有之索引的詳細資訊，包括：是否為叢集或唯一索引、索引大小（以 MB 為單位）、資料記錄的個數等資訊。

圖表 18

• 如圖表 19 所示，從「選取報表」下拉式清單方塊中選取「索引詳細資料報表（建議的）」選項，會一一列出工作負載中每一個建議之索引的詳細資訊。

圖表 19

• 如圖表 20 所示，從「選取報表」下拉式清單方塊中選取「檢視-資料表的關聯性報表」選項，會列出在建議之索引組態下，所參考的檢視表與檢視表所屬的資料表名稱。

圖表 20

• 如圖表 21 所示，從「選取報表」下拉式清單方塊中選取「工作負載分析報表」選項，您可以清楚檢視在建議之索引組態下，所有各類作業陳述式的成本變化。很明顯的，成本減少的個數越少，就代表您既有的索引組態越好。

圖表 21

• 如圖表 22 所示，從「選取報表」下拉式清單方塊中選取「資料庫存取報表」選項，會列出工作負載中所參考的每一個資料庫、該資料庫被參考使用的個數，以及每個被參考使用之資料庫的比重。以本例來說，只有參考一個資料庫，因此使用率便是 100%。

圖表 22

• 如圖表 23 所示，從「選取報表」下拉式清單方塊中選取「資料表存取報表」選項，會列出工作負載中所參考的每一個資料表、該資料表所隸屬的資料庫名稱、資料表被參考使用的次數、與每個被參考使用之資料表的比重。

圖表 23

• 如圖表 24 所示，從「選取報表」下拉式清單方塊中選取「資料行存取報表」選項，會依序列出與資料庫存取報表相同的資訊，所不同的是它還會列出負載中所參考的每個欄位名稱。

圖表 24

特別提醒您，您在檢視上述這些報表的同時，可以在報表窗格內按下滑鼠右鍵，選取「匯出到檔案」指令來將報表儲存為 XML 檔。

7. 最後您必須決定要如何依 Database Engine Tuning Advisor 的建議去重組索引，做法有下列三種：

• 立即執行。欲達此目的，請如圖表 25 所示，按一下「動作」功能表中的「套用建議」指令讓 Database Engine Tuning Advisor 馬上替您建立或刪除索引等作業。

圖表 25

• 將重組索引的程式碼儲存至一個 SQL 指令檔，並由使用者自行決定何時去執行它。欲達此目的，請如圖表 26 所示，按一下「動作」功能表中的「儲存建議」指令或是按一下工具列上的「儲存建議」按鈕 。

圖表 26

• 重新進行調校分析工作。假如您希望某些資料庫物件不要被分析，請按一下「建議」標籤來切換到該頁面，然後在索引建議列示清單中，清除不要被分析之資料庫物件前面的核取方塊，然後按一下「動作」功能表中的「評估建議」指令來讓 Database Engine Tuning Advisor 建立一個新的工作階段，接著請重複步驟 1～7。