摘要: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