您 已經學會如何建立索引，也讀了許多索引的設計準則與注意事項，甚至也自行做了多次測試，不過筆者相信還是有非常多人無法肯定自己所設計與建立的索引究竟能 否提昇系統平日各項作業的速度。基於此原因，筆者要指導您如何使用 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 所示的選項進行設定。請特別注意，這個設定會與下面兩個選項的設定相互影響。在本例中，我們選取 ○ 索引與索引檢視。
選項 | 新增的索引種類 |
| 索引與索引檢視 | 叢集索引、非叢集索引、與索引檢視表 |
| 索引檢視 | 索引檢視表 |
| 索引 | 叢集索引、非叢集索引 |
| 非叢集索引 | 非叢集索引 |
| 只評估現有PDS的使用情形 | 無 |
圖表 6
- 在這個設定中，您要決定是否讓 Database Engine Tuning Advisor 提出資料庫分割的建議，這將會影響下一個步驟的設定。如果您希望 Database Engine Tuning Advisor 在分析結果中，不要提出任何分割資料表的建議，請選取「要採用的分割策略」項目中的選項鈕 ○ 沒有資料分割；反之，如果您希望可以得到分割資料表的建議，請選取選項鈕 ○ 完整的資料分割。假如您希望瞭解 Database Engine Tuning Advisor 所提出的資料庫分割建議，可以選取選項鈕 ○ 對齊的資料分割。
由於本例中"索引調校練習"資料庫的資料記錄還不是很多，我們將指定使用沒有資料分割選項。 - 最後您必須決定要 Database Engine Tuning Advisor 保留或移除哪些索引，做法有下列五種（如圖表 7 所示）。特別值得一提的是，當您選取使用保留對齊的資料分割選項時，請記得選取「要採用的分割策略」項目中之對齊的資料分割選項。
以本例而言，我們指定使用不要保留現有的 PDS 選項，讓 Database Engine Tuning Advisor 替我們決定要移除或是新增哪些索引。
選項 | 做法 |
| 不要保留現有的 PDS | 會考慮移除既有的叢集索引、非叢集索引、與索引檢視表。值得一提的是，如果選取了只評估現有PDS的使用情形選項，Database Engine Tuning Advisor只會從既有的索引進行評估，然後決定是否要將這些索引移除。 |
| 只保留索引 | 會考慮移除索引檢視表。 |
| 保留所有現有的 PDS | 不會移除任何的叢集索引、非叢集索引、與索引檢視表。 |
| 只保留叢集索引 | 會考慮移除非叢集索引、與索引檢視表。 |
| 保留對齊的資料分割 | 不考慮對資料表或檢視表進行新的分割，此舉將保留既有的分割狀態。 |
圖表 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。
SQL Server 2005 - 索引的評選與分析 - Part2
索引分析
Database Engine Tuning Advisor 能夠在索引層面的運用上，對應用程式系統做完整且全面性的評估，然而如果您只是要分析某單一查詢或批次是否善用索引來提昇效率，使用 Database Engine Tuning Advisor 顯然是殺雞用牛刀。
欲分析某單一查詢或批次是否善用索引來提昇效率，應借助下列工具與陳述式：
- SHOWPLAN 陳述式
- 查詢編輯器的顯示圖形執行計劃功能
- STATISTICS IO 陳述式
接下來筆者就一一說明如何使用上述工具與陳述式來分析某單一查詢或批次的索引使用情況。
SHOWPLAN 陳述式
SHOWPLAN 陳述式能夠幫助您分析出查詢的下列資訊：
- 查詢使用了哪一個索引。
- 關於查詢的摘要與詳細資訊。比方說，連結資料表的每一個步驟。
基本上，您可以使用 SHOWPLAN 陳述式去檢視某一個查詢的查詢計劃。SHOWPLAN 陳述式的語法如下所示：
SET SHOWPLAN_ALL { ON│OFF }
－或－
SET SHOWPLAN_TEXT { ON│OFF }
－或－
SET SHOWPLAN_XML { ON│OFF }
於使用 SHOWPLAN 陳述式時，必須注意下列事項：
- SHOWPLAN 陳述式會讓 SQL Server 不執行 Transact-SQL 程式碼，但會傳回如何執行這些程式碼的詳細資訊。
- SET SHOWPLAN_ALL 陳述式所傳回的資訊較詳細且完整，而 SET SHOWPLAN_TEXT 以及 SHOWPLAN_XML 陳述式所傳回的資訊則比較簡單扼要。
- SHOWPLAN_TEXT 陳述式將以純文字格式傳回資訊，而 SHOWPLAN_XML 陳述式會以 XML 格式傳回資訊。
- 如果您同時使用並開啟這三個 SHOWPLAN 陳述式， SQL Server 會以第一個被使用的 SHOWPLAN 陳述式為準。比方說，您依序指定 SET SHOWPLAN_XML ON、SET SHOWPLAN_ALL ON 與 SET SHOWPLAN_TEXT ON，SQL Server 會以 XML 格式傳回執行程式碼的資訊。
- SHOWPLAN 陳述式所產生的資訊僅限於某一個序列。如果您重新連結至 SQL Server，您必須再次執行 SHOWPLAN 陳述式去產生此序列的相關資訊。
- SHOWPLAN 陳述式要能夠發生作用，必須執行於一個批次（Batch）中。
- 在您將 SHOWPLAN 陳述式設定成 ON 之後，SQL Server 會傳回所有後續之 Transact-SQL 陳述式的查詢計劃，直到您再度將 SHOWPLAN 設定成 OFF 為止，此時 SQL Server 才會真正地去執行程式碼。
使用 SHOWPLAN 陳述式的範例
本範例的操作步驟中，我們會先將 SHOWPLAN_TEXT 設定成 ON，然後從資料庫效能調校練習中的 vfpman 資料表提取出身份證字號為"M123456781"的資料記錄，以便檢視這兩個查詢的相關執行資訊與索引使用狀況。
1. 開啟 Transact-SQL 查詢編輯器。
2. 於查詢視窗中鍵入下列陳述式：
USE 索引調校練習;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT uid FROM vfpman WHERE uid = 'M123456781';
GO
SET SHOWPLAN_TEXT OFF;
GO
3. 使用滑鼠按一下工具列中的執行按鈕 。
4. 執行完畢後，如圖表 27 所示，於 Transact-SQL 查詢編輯器下半部的窗格中，會列出此查詢係使用哪一個索引來提昇其效率。
圖表 27
使用顯示圖形執行計劃
Transact-SQL 查詢編輯器的顯示圖形執行計劃功能所顯示的資訊與 SHOWPLAN_ALL、SHOWPLAN_TEXT 或 SHOWPLAN_XML 陳述式所傳回者相同，其間最大的差異是，SHOWPLAN_ALL、SHOWPLAN_TEXT 陳述式所傳回的資訊是以文字格式來顯示，SHOWPLAN_XML 陳述式則是以 XML 的格式來列示，而顯示圖形執行計劃則是以圖形來呈現特定陳述式或查詢的執行情況，值得一提的是，使用顯示圖形執行計劃還可以執行查詢的程式碼。
下面的操作步驟中，我們就實際示範如何使用 Transact-SQL 查詢編輯器的顯示圖形執行計劃功能來檢視查詢的查詢計劃：
1. 開啟 Transact-SQL 查詢編輯器。
2. 於查詢視窗中鍵入下列陳述式：
USE 索引調校練習;
GO
SELECT uid, name FROM vfpman WHERE uid = 'M123456781';
3. 請執行下列三項操作之一來啟動顯示圖形執行計劃：
- 使用滑鼠按一下工具列中的「顯示估計執行計劃」按鈕 。
- 直接按住 Ctrl 鍵不放，接著按下 L 鍵。
- 從 SQL Server Management Studio 的「查詢」功能表中選取「顯示估計執行計劃」指令。
4. 此時下半部窗格中的「執行計劃」標籤，會顯示圖表 28 所示的執行計劃。請用滑鼠在叢集索引搜尋圖示上按一下，接著按一下滑鼠右鍵選擇「屬性」指令以列表方式來檢視詳細資訊，以本例而言，顯然它是使用索引 PK_Vfpman 來搜尋資料。
事實上，您可以如圖表 29 所示，將滑鼠指標移至圖示上方來檢視更細部的資訊。
圖表 28
圖表 29
附註：
如果您覺得圖示太小，請在圖示上按一下滑鼠右鍵，選擇「放大」指令。
我們以實例示範在執行顯示圖形執行計劃時，也能順便執行要查詢的程式碼：
1. 開啟 Transact-SQL 查詢編輯器。
2. 於查詢視窗中鍵入下列陳述式：
USE 索引調校練習;
SELECT uid, name FROM vfpman WHERE uid = 'M123456781';
3. 請執行下列三項操作之一來開啟「包括實際執行計劃」功能：
- 按一下工具列中的「包括實際執行計劃」按鈕， 使此按鈕成凹陷狀態。
- 直接按住 Ctrl 鍵不放，接著按下 M 鍵。
- 從「查詢」功能表中選取「包括實際執行計劃」指令，以便使此指令成凹陷狀態。
4. 使用滑鼠按一下工具列中的「執行」按鈕 。
5. 接下來的操作步驟與上個範例之步驟 4 相同，於此不贅述。
STATISTICS IO 陳述式
STATISTICS IO 陳述式能夠幫助您分析出查詢的下列資訊：
- 用來傳回查詢結果的 I／O 量。
- 顯示查詢之邏輯 I／O 與實體 I／O 方面的資訊。
顯然您可以使用 STATISTICS IO 陳述式去檢視處理一個查詢所需的 I／O 量，以便決定是否需要重新撰寫查詢陳述式或是重新設計索引。特別的是，STATISTICS IO 陳述式還會傳回查詢的掃描次數、自資料快取所讀取之分頁的數目（邏輯讀取）、自磁碟所讀取之分頁的數目（實體讀取）、被置入快取之分頁的數目（讀取前讀 取）、自資料快取所讀取之大型物件資料型別分頁的數目（LOB 邏輯讀取）、自磁碟所讀取之大型物件資料型別分頁的數目（LOB 實體讀取）、以及被置入快取之大型物件資料型別分頁的數目（LOB 讀取前讀取）。
STATISTICS IO 陳述式的語法如下所示：
SET STATISTICS IO { ON│OFF }
於使用 STATISTICS IO 陳述式時，必須注意下列事項：
- 一個 I／O 表示讀取一個分頁，因此一個較低的 I／O 是較佳的。如果一個查詢需要讀取 20 個分頁，則此查詢的成本是 20 個 I／O。由於使用索引絕對要比掃描整個資料表所需的 I／O 數來得少，因此您應該盡量利用索引來降低 I／O 數。
- 在您將 SET STATISTICS IO 設定成 ON 之後，SQL Server 會傳回所有後續之 Transact-SQL 陳述式的統計資訊，直到您再度將 SET STATISTICS IO 設定成 OFF 為止。
- 所傳回的邏輯讀取數值，其實是邏輯讀取與實體讀取的累計值。比方說，如果邏輯讀取的數目是 20，實體讀取的數目是 5，則確實的邏輯讀取數目應該是 15。
- SET SHOWPLAN 與 SET STATISTICS IO 不能同時被設定成 ON，因為一個會讓 SQL Server 停止執行程式碼，一個卻要求 SQL Server 去執行程式碼。
使用 STATISTICS IO 陳述式的範例
本範例我們要示範如何使用 STATISTICS IO 陳述式檢視查詢的 I／O 量，而且我們還會比較未使用索引與使用索引在 I／O 量上的差異。此處的查詢對象是"索引調校練習"資料庫中的 Foxman 資料表，此資料表並未擁有任何索引。
1. 開啟 Transact-SQL 查詢編輯器。
2. 於查詢視窗中鍵入下列程式碼：
USE 索引調校練習;
GO
SET STATISTICS IO ON;
SELECT uid,name,content FROM Foxman WHERE uid = 'M123456781';
GO
SET STATISTICS IO OFF;
3. 使用滑鼠按一下工具列中的「執行」按鈕 。
4. 執行完畢後，使用滑鼠按一下 Transact-SQL 查詢編輯器下半部窗格中的「訊息」標籤，會列出圖表 30 所示此查詢的 I／O 量。我們發現，由於資料表 Foxman 並未擁有任何索引，因此本查詢之邏輯讀取的數目高達 43。
圖表 30
5. 為證明建立適切的索引確實能降低查詢的 I／O 量，現在請您立即切換至 SQL Server Management Studio，然後使用 PRIMARY KEY 條件約束將資料表 Foxman 的 uid 欄位定義為主索引鍵，並且將此主索引鍵的索引設定成叢集索引。完成後，切換回 Transact-SQL 查詢編輯器，再次按一下工具列中的「執行」按鈕 ，我們發現，同樣的查詢由於使用了索引，使其邏輯讀取的數目大幅降至 2（如圖表 31 所示）。
圖表 31
http://blog.xuite.net/alwaysfuturevision/liminzhang/8335596
--
[垃圾桶] 裡沒有會話群組？
沒有留言:
張貼留言