Microsoft SQL Server 2000 超級管理手冊(三五)-CSDN博客

本文链接：https://blog.csdn.net/gzwl/article/details/123833

35. 使用SQL Query Analyzer和SQL Profiler

使用SQL Query Analyzer

使用SQL Profiler

最佳化T-SQL陳述式

本章總結

本章我們將繼續第 21 章中關於預存程序的說明。本章將學習如何利用 SQL Query Analyzer 和 SQL Profile 分析預存程序和其他陳述式。從分析中可以確定 T-SQL 陳述式是否有效率。一個有效的 SQL Server 查詢會使用適當的操作次序和適當的索引，來減少過程的行列數並使 I/O 操作數達到最小。

用 Query Analyzer 可以檢視 SQL Server query optimizer 為 T-SQL 陳述式選擇的執行計劃。query optimizer 是用來為每個 T-SQL 陳述式找尋最佳執行計劃的模組。query optimizer 分析每個 T-SQL 陳述式，考量大量可能的執行計劃，並根據所需要的資源和程序時間來計算每個計劃的資源消耗。選擇最不會消耗資源的計劃。每個計劃的資源消耗決定於系統收集關於資料的統計結果，但這些資料不一定是最新的。因為您可能比 query optimizer 更瞭解資料庫和資料內容，所以能夠決定比 query optimizer 更好的計劃。使用 Query Analyzer 提供的資訊，可以決定 query optimizer 提供給陳述式的計劃是否有效率。如果確定它不夠好，就試著修改 T-SQL 陳述式或用 SQL 提示將陳述式最佳化。本章將學習如何使用 Query Analyzer，以及如何最佳化 T-SQL 陳述式。

使用 Profiler 可以分析 SQL Server 系統內部活動，以確定哪些 SQL 陳述式和預存程序的使用造成系統資源的負擔。有了這項資訊，就能先調整這些陳述式和預存程序。本章除了學習如何使用 Profiler 外，也將說明如何最有效地使用 Profiler 提供的資訊。

使用 SQL Query Analyzer
　

Microsoft SQL Server 2000 提供的 Query Analyzer 工具，取代 Windows (ISQL/W)中的 Interactive SQL 成為 SQL 圖形介面。然而您可能注意到 Query Analyzer 顯示在任務管理員中的是 isqlw.exe。可以用 Query Analyzer 來處理 T-SQL 陳述式，並檢視這些陳述式的結果。Query Analyzer 也可以被當作除錯工具，用於評估 query optimizer 為 T-SQL query optimizer 陳述式所產生的執行計劃。

執行 T-SQL 陳述式
　

Query Analyzer 最基本的功能是執行 T-SQL 陳述式，並顯示陳述式結果。依照下列步驟用 Query Analyzer 來執行 T-SQL 陳述式：

- 點選開始 / 程式集 / Microsoft SQL Server / Query Analyzer ，會顯示連線到SQL Server 對話方塊，如圖35-1所示。利用這個對話方塊和 SQL Server 系統連線。
  　
  
  圖35-1　連線到 SQL Server 對話方塊
- 在 SQL Server 文字方塊中鍵入伺服器名稱。可以是本地伺服器或遠端伺服器的名稱。選一個想要連接的本地伺服器，會顯示如圖35-1中的黑點。SQL Server 之下的核取方塊，可指定系統未運作時是否要開啟伺服器。在 連線使用 區域中選擇用來連線 SQL Server 的驗證方式。如果選擇使用 Windows 的帳戶驗證，就不必指定使用者名稱或密碼，因為 Windows 2000 的帳號會被用於驗證存取 SQL Server 上。如果選擇使用 SQL Server 的帳戶驗證，就必須指定 SQL Server 使用者名稱和密碼，才能存取 SQL Server。
- 按一下確定，連線到指定的 SQL Server 上，並啟動 Query Analyzer。當Query Analyzer 視窗出現時，只能看到 Query 和巡覽窗格，但是一旦開始傳送 T-SQL 陳述式，視窗就會變更。最大化 Query 窗格會填滿整個 Query Analyzer 視窗，如圖35-2所示。在工具列的下拉式清單中選取所要執行查詢的資料庫。圖35-2顯示選擇 master 資料庫，本範例中會在下拉式清單中選取 Northwind 資料庫。
  　
  
  圖35-2　SQL Query Analyzer 視窗
- 選擇資料庫之後，在右邊窗格中鍵入 T-SQL 陳述式，在這個範例中，請鍵入『SELECT * FROM customers』。現在有多個選項可用，可以按一下工具列上的 剖析查詢 按鈕（藍色的查核記號）來檢查 T-SQL 陳述式的語法，或者按一下 執行查詢 按鈕（朝右的綠色三角形）來執行陳述式。按 取消查詢執行 按鈕（正方形）可停止查詢的執行。圖35-3顯示了對 Northwind 資料庫的 customers 資料表的完整查詢。
  一旦傳送了 T-SQL 陳述式，Query Analyzer 將建立一個窗格，讓您可以垂直和水平的捲動來檢視結果，如圖35-3所示。Query Analyzer 還可以用來幫助您調整您的 T-SQL 陳述式，您將會在本章的
   ＜最佳化T-SQL陳述式＞一節中看到。
  
  　
  
  圖35-3　完整的 Query Analyzer 查詢

檢視執行計劃和修改 T-SQL 陳述式
　

Query Analyzer 也可以用來檢視執行計劃，這個執行計劃是 Query Optimizer 為您的 T-SQL 陳述式所做的選擇。這個特性可幫助您判斷 T-SQL 陳述式是否有效率，並確定應該選擇哪條執行路徑和資料存取路徑。然後您可以修改 T-SQL 陳述式和資料庫架構，檢查效能是否得以提高。要使用 Query Analyzer 來檢視 T-SQL 陳述式的評估執行計劃，請按照下列步驟操作：

- 在 Query Analyzer 視窗鍵入需要 Query Analyzer 評估的 T-SQL 陳述式後，按下 顯示評估的執行計劃 按鈕（這個按鈕在資料庫選擇下拉式清單的右邊），或者按Ctrl+L，顯示 評估的執行計劃 窗格，如圖35-4。在這個窗格中，查詢透過圖形來描述，每個操作的消耗與資料存取的方法都在此顯示。在圖35-4的窗格中，出現索引名稱 Customers.PK_Customers，表示叢集索引 Customers.Pk_Customers 被用來存取資料。
  　
  
  圖35-4　評估的執行計劃窗格
- 評估的執行計劃 窗格中還提供其它附加資料。要檢視操作的附加資料，請將滑鼠游標移動到操作圖示上。就會出現一個彈出視窗，其中包含附加資料，如圖35-5所示。
  　
  
  圖35-5　檢視關於操作的附加資料
  
  這個彈出視窗包含下列資訊：
  - 實體作業 查詢所執行的操作，例如索引掃描、聯結和總計等等。如果實體操作被顯示為紅色，那是 Query Optimizer 發出的警告，這時您應該固定 T-SQL 陳述式。
    　
    估計的資料列記數 預估由操作檢索的列數。
    　
    估計的資料列大小 需要檢索的列的估計大小。
    　
    估計的I/O耗用/估計的CPU耗用 由該操作佔用的估計 I/O 資源和 CPU時間。越低的值意味著該 T-SQL 陳述式越有效率。
    　
    估計的執行數 該操作在 T-SQL 陳述式中執行的次數。
    　
    估計的耗用 由 Query Optimizer 決定的操作消耗。這個消耗顯示為T-SQL 陳述式的總消耗百分數。
    　
    估計的子樹耗用時間 執行前面部分和這一部分 T-SQL 陳述式的消耗。如果存在多個子樹，這個選項允許您檢視執行每個子樹的消耗。
    　
    引數 T-SQL陳述式使用的引數。

說明

執行計劃（execution plan）描述了 Query Optimizer 如何選擇執行 T-SQL 陳述式，並包括步驟的次序和所使用的操作類型。資料存取方法（Data Access Method）是描述資料庫物件（資料表、索引等）如何被存取的一種物件。這兩者是相關聯的，因為在有些時候，資料存取方法被考量為執行計劃的一部分。當然也可以單獨的考量。

接下來將看到使用 Query Analyzer 較為複雜的範例。範例顯示低效率的 T-SQL陳述式對效能的影響：減慢回應時間，並佔用其他程序的系統資源。現在先看一個使用 Query Analyzer 檢視和修改 T-SQL 陳述式執行計畫的範例。修改 T-SQL 陳述式能達到較好的效能。在許多情況下，可以建立更有效、功能更佳的 T-SQL 陳述式。接下來會說明幾種 T-SQL 陳述式，它們屬於較複雜的預估執行計畫類型。

這些範例使用 Northwind 資料庫中的 Orders 資料表。現在來檢視這個資料表的組織。這個資訊會幫助確定 Query Optimizer 是否選擇了適當的執行計劃。Orders 資料表在 OrderID 一欄中有一個名為 PK_Orders 的叢集索引，另外還有八個其他的索引，如圖35-6中的 Manage Indexes 對話方塊所示。（要存取這個對話方塊，請在 Enterprise Manager中，展開一個伺服器組，展開一個伺服器，展開 資料庫 資料夾，展開 Northwind 資料庫，然後選取 資料表 資料夾。在右側窗格中的 Orders 資料表上按滑鼠右鈕，並從快顯功能表中選擇 所有工作 ，接著選擇 管理索引 。或者只要從 Query Analyzer 工具功能表中直接選擇 管理索引 ，接著從下拉式功能表中選擇 Orders 資料表。）

圖35-6　管理索引對話方塊

檢視計畫和修改 SELECT 陳述式
　

本節中，這個查詢的範例要求獲得關於訂單的資訊，這些訂單是由職員 ID 為 4的職員儲存的。請在 Qery Analyzer 輸入以下查詢，並執行 顯示評估的執行計劃 ：

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM orders
WHERE EmployeeID = 4

在該職員的組織中，每個職員處理訂單的一小部分，因此可能需要 SQL Server在進行查詢時使用 EmployeeID索引。但是，Query Analyzer 顯示的卻是 SQL Server 使用的存取方法是 PK_Orders 叢集索引，如圖35-7中的 評估的執行計劃 窗格所示。

圖35-7　評估的執行計劃窗格顯示了使用 PK_Orders 叢集索引。

要 Query Optimizer 使用 EmployeeID 索引，可以使用 SELECT 陳述式的提示（hint），程式碼如下（提示將在本章的＜使用提示＞一節中討論。）：

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM orders WITH (INDEX ( EmployeeID))
WHERE EmployeeID = 4

說明

在 SQL Server 7 中，較好用的索引提示是 INDEX=index_name。

在 SQL Server 2000 中，較好用的索引提示是 INDEX（index_name）。

透過這個資訊，可以指示 Query Optimizer 使用所需要的執行計劃，而不是 Query Optimizer 選擇的計劃。調整過的評估執行計劃窗格如圖35-8所示。可以從顯示的資料存取方法中看到，使用的是 EmployeeID 索引，然後傳回一個書籤搜尋（bookmark lookup），該查詢將獲得來自資料庫的資料。（書籤搜尋搜索一列資料的內部識別碼。）

圖35-8　調整過後的評估執行的計劃窗格

Query Optimizer 是一個有效率的工具，以不斷的更新統計資料，提供選擇最佳的執行計劃。由於不同的公司行號可能更清楚自己的組織和資料，所以某些情況下，公司可以選擇比 Query Optimizer 更有效率的最佳執行計劃。

注意

使用提示代替 Query Optimizer 的選擇時，必須承擔變更可能帶來的風險。雖然資料遺失或損毀的機率不大，不過可能對系統的執行效能產生的是負面的影響。

檢視聯結操作
　

執行聯結操作比執行選取操作多出了更過的執行過程，稍後會在評估執行的計劃窗格中看到。聯結操作會對數個資料表進行存取的動作，並在存取後組合所搜尋的資料。（聯結在第 14 章中討論）。請在 Query Analyzer 輸入以下的聯結操作範例：

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, 
     LastName, OrderDate
FROM Orders Join Employees ON Orders.EmployeeID = Employees.
EmployeeID

之前的陳述式包括 SQL-92 JOIN 操作元。建議使用此操作元在 SQL Server 2000中執行連結。下面的陳述式使用較傳統的連結語法：

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, 
     LastName, OrderDate
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID

T-SQL 陳述式在 EmployeeID 資料行中聯結了 Orders 和 Employees 資料表。評估執行計劃結果如圖35-9所示。

圖35-9　顯示在評估執行的計劃窗格的聯結操作

在窗格中可以看到兩個子樹中哪個的消耗較大，也可以看到規劃聯結操作的類型。SQL Server 支援多種不同的聯結操作，包括雜湊聯結（hash join）、巢狀迴圈聯結（nested loops join）和合併聯結（merge join）。複雜的聯結操作，執行計劃會相對的變得複雜。由於您的目的是要減少佔用 CPU 時間總量，以及減少執行 I/O 操作的數目，所以必須判斷是否使用了好的執行計劃。有時可以使用提示來指定需要使用的特殊索引，從而減少 CPU 和 I/O 行為。在這個查詢中，由於聯結是指定在 WHERE 子句中的唯一操作，這個執行計劃應該是最適合的。

檢視總計操作
　

下面顯示的 T-SQL 陳述式執行連結操作和總計操作，請在 Query Analyzer 中輸入：

SET QUOTED_IDENTIFIER ON
GO 
SELECT CustomerID, SUM("Order Details".UnitPrice)
FROM Orders JOIN "Order Details" ON Orders.OrderID = "Order 
Details".OrderID
GROUP BY CustomerID

說明

因為資料表名稱 Order Details 包含了一個關鍵字和一個空格，所以必須使用選項 SET QUOTED_IDENTIFIER ON。要獲得更多關於這個選項的資訊，請參閱《線上叢書》索引中的 SET QUOTED_IDENTIFIER。

這個複合操作的評估執行的計劃窗格顯示在圖35-10。

圖35-10　顯示在評估執行的計劃窗格中的總計操作

檢視預存程序
　

要顯示預存程序的執行計劃，可以簡單的在 Query Analyzer 中呼叫預存程序。Query Analyzer 會顯示所呼叫預存程序的評估執行計劃，如圖35-11所示的 sp_who。（需要注意的是，預存程序的執行計劃相當複雜）。即使不確定組成預存程序的 T-SQL 陳述式，也可以顯示該預存程序的執行計劃。

圖35-11　在評估執行的計劃窗格中的預存程序

使用物件瀏覽器
　

物件瀏覽器（Object Browser）包含在 SQL Server 2000 中，用來提昇 Query Analyzer 的功能。啟動 Query Analyzer 時，會在左邊視窗看到 物件瀏覽器 。 物件瀏覽器 分成資料庫物件區和通用物件區兩部份。在資料庫物件區，可以瀏覽像資料表和檢視表等物件；在通用物件區，可以存取系統物件和功能表。可以利用 物件瀏覽器 找出所提供的資訊後，然後決定用的資訊。

資料庫物件
　

物件瀏覽 頂端部份含有資料庫物件，可以立即看到預設資料庫以及所建立的資料庫，並顯示所屬的 SQL Server。想要知道在 物件瀏覽 之中有哪些可用資訊，只要展開物件即可。先展開 Northwind 資料庫，然後展開 使用者資料表 。現在可看到 Northwind 資料庫中可用的資料表，如圖35-12所示。

圖35-12　檢視物件瀏覽中的資料表

接著展開一個使用者定義資料表，然後展開包含關於欄位、索引、條件約束、資源依存性、觸發程序資訊的資料夾。圖35-13顯示展開的 Orders 資料表。或者可以展開資料夾，檢視關於系統資料表、檢視表、預存程序、功能和使用者定義資料類型的資訊。

在 Query Analyzer 中提供物件資訊，是相當方便的，這樣在建立 SQL 陳述式和預存程式時，就不需要再檢查 Query Analyzer 以外的物件資訊。不但可以在 物件瀏覽 中檢視資訊，還可以編輯物件、拖曳物件，甚至可以建立指令碼和修改物件，將功能加在一個已經很實用的工具中。

圖35-13　展開在物件瀏覽中的資料表

通用物件
　

在 物件瀏覽 較下面的部份是名為通用物件（Common Objects）的資料夾，這個資料夾中包含了如設定功能、游標功能、日期和時間功能以及數學功能等物件資訊的資料夾。因此您可以很快地存取一個要使用的功能而不須查詢語法。在這個區域展開資料夾的話，將顯示設定選項，如圖35-14所示。將這些功能拖曳到 Query 窗格中，或者用滑鼠點選這個功能以顯示一個簡要描述。這對於 ad-hoc 查詢過程是相當方便的。

圖35-14　在物件瀏覽的通用物件中展開一個資料夾

除了存取全域變數之外，您還可以存取很多其他有用的快顯功能表，如數學和字串功能。繼續展開物件，您可以用這些功能表得到參數要求的資訊。例如，圖35-15顯示展開 數學函數 中的參數資料夾。

圖35-15　檢視物件瀏覽中的參數資料夾

使用SQL Profiler
　

除了 Query Analyzer 以外，SQL Profiler 工具也可以幫助找出低效率的 T-SQL陳述式。Profiler 可以顯示所有系統中執行的 T-SQL 陳述式，並用圖形加以顯示。您也可以使用多種排序和篩選選項，找出使用了最多 CPU 和 I/O 資源的 T-SQL 陳述式。有了這些資訊就可以確定在調整系統時需要集中焦點在哪些 T-SQL 陳述式上。對於透過應用程式呼叫的 T-SQL 陳述式，可以檢視 T-SQL 陳述式，並決定利用存取應用程式來源程式碼時的效率如何。

SQL Server 2000 中的 Profiler 工具類似 SQL Server 7 中的 Profiler 工具，當然有些加強的功能。其中一項新功能是引用追蹤範本，用來建立追蹤檔案。（在確定能夠使用這個功能追蹤 SQL Server 之後，追蹤才能建立）。在 SQL Server 7 中，追蹤功能只能以手動建立。

要呼叫 Profiler 工具並執行追蹤，請遵循下列步驟：

- 按一下開始 / 程式集 / Microsoft SQL Server / Profiles 。在 Profiler 剛出現時，會看到一個空的 Profiler 視窗。在 Profiler 中如果沒有打開任何窗格，就不會剖析任何陳述式。
- 要開始分析，必須選擇已存在的追蹤模式，或是建立一個新的追蹤模式。（啟動過程將在步驟4介紹）。SQL Profiler 提供數種追蹤模式。使用這些功能由於不必從 scratch 建立追蹤，因此節省不少時間。要檢查追蹤清單，請在檔案功能表中點選 開啟舊檔 ，選擇 追蹤範本 ，顯示 開啟舊檔 對話方塊，如圖35-16所示。
  　
  
  圖35-16　開啟舊檔對話方塊中顯示可用的追蹤
  
  SQL Server 中的追蹤描述如下：
  - SQLServerProfilerSP_Counts.tdf 統計已經執行預存程序的數量。這個結果將根據預存程序的名稱進行分組，其中包括程序所執行的次數。
    　
    SQLServerProfilerStandard.tdf 收集關於連結點的資訊，執行預存程序，以及依序執行 SQL 批次檔。
    　
    SQLServerProfilerTSQL.tdf 收集所有的 T-SQL 陳述式傳送至 SQL Server 的次序且依使用者團體分類傳送。這個追蹤包含了 T-SQL 陳述式以及它所執行的時間。
    　
    SQLServerProfilerTSQL_Duration.tdf 顯示執行的 T-SQL 陳述式，以及這些 T-SQL 陳述式執行所用的時間（一千分之一秒為單位）。
    　
    SQLServerProfilerTSQL_Grouped.tdf 收集與 SQLServerProfilerTSQL 追蹤類似的資料，但是根據傳送陳述式的使用者來將資料分組。
    　
    SQLServerProfilerTSQL_Replay.tdf 提供關於已經執行的 T-SQL 陳述式的詳細資訊。該追蹤提供可以用來重新執行 Query Analyzer 中 T-SQL 陳述式的資料。
    　
    SQLServerProfilerTSQL_SPs.tdf 顯示預存程序以及預存程序中的 T-SQL指令。該結果根據陳述式執行的時間依序顯示。
    　
    SQLServerProfilerProfilerTuning.tdf 收集關於預存程序和執行 SQL 批次檔的資料。
    　
  這些追蹤相當有用。例如 SQLServerProfilerTSQL_Duration 追蹤可找出哪些T-SQL 陳述式花費了大量的執行時間。這些資訊使您可以開始最佳化查詢。一個陳述式執行得很慢，可能是因為它的工作量很大，或者效率很低。在下一個步驟中將說明，必須使用每個追蹤的預設定義。
- 要啟動追蹤，請選取檔案 / 新增，接著選擇追蹤。顯示 連線到SQL Server 對話方塊，如圖35-17所示。在這個對話方塊中，選擇要追蹤的 SQL Server 系統，然後按確定。
  　
  
  圖35-17　連線到SQL Server對話方塊
- 進入 追蹤屬性 視窗，如圖35-18所示。在一般頁籤，可以命名並選擇追蹤的起始點。本範例是選擇 SQLServerProfilerTSQLDuration 追蹤。在頁籤下面的部份，可以指定是否要將獲得的追蹤內容輸入到檔案或 SQL Server 資料表中。如果沒有選擇這些選項，那麼追蹤將只在螢幕上出現。另外，可以指定追蹤完成的時間，這在執行長時間追蹤時相當有用。
  　
  
  圖35-18　追蹤屬性視窗的一般頁籤
- 接著選擇事件頁籤，如圖35-19所示。這裡可以選取一個或多個追蹤記錄的事件。大量的類型和特殊的事件可以被追蹤，列在 可用的事件類別 清單中的資料均可追蹤。
  　
  
  圖35-19　追蹤屬性視窗的事件頁籤
- 選擇要追蹤的事件後，點選 資料行 頁籤，如圖35-20所示。在這個頁籤中，指定在追蹤過程中要收集哪些資料。這個資料包括終止時間、物件 ID 等等。
  　
  
  圖35-20　追蹤屬性視窗的資料行頁籤
- 選取 篩選器 頁籤，如圖35-21所示。這個頁籤中可以指定是否要擷取事件所需的條件。例如，排除追蹤 Profiler（預設值）。透過排除 SQL Server 過程，可以避免 Profiler 視窗的混亂，增加閱讀性。
  　
  
  圖35-21　追蹤屬性視窗的篩選器頁籤
- 完成設定選項後，按執行啟動追蹤。如果要修改追蹤，建議使用檔案功能表中的 另存新檔 選項來保存追蹤修改（用不同的名稱）。一旦開始追蹤，事件將會出現在 Profiler 視窗。使用本例中所選的追蹤，事件將會用 Duration 排序（千萬分之一秒）。圖35-22顯示 Profiler 視窗執行中的追蹤。

注意

在繁忙的環境中，Profiler 可能會使用重要的系統資源。追蹤越多的事件，會佔用越多的系統資源。

最佳化 T-SQL 陳述式
　

在前面的章節中學到了使用 Profiler 檢視系統中所執行的 T-SQL 陳述式，以及利用 Query Analyzer 確定執行計劃和資料存取。也可以利用修改 T-SQL 陳述式來改善效能。擁有這些工具，就有能力修改 T-SQL 陳述式，改善陳述式的執行效能。在本節中，會看到最佳化 T-SQL 陳述式的多種途徑，使其能夠提供更好的效能或佔用更少的系統資源。

圖35-22　執行中的追蹤

最佳化執行計劃
　

修改執行計劃是相當困難的，要建立一個比 Query Optimizer 的計劃更佳的執行計劃更不容易。有些操作更可能在執行計劃的修改中獲益，它們是 JOIN、GROUP BY、ORDER BY 和 UNION。對於這些操作的修改可以容易的透過使用提示來達成，這將在稍後的＜使用提示＞一節中提到。透過更改提示和顯示 Query Analyzer 的輸出，可以看到獲得了一個比較有效的操作。

不過，對於最佳化 T-SQL 陳述式並沒有一個特定的模式。因為每個資料庫都是獨一無二的，且應用程式也不相同，因此修改皆根據不同的情況進行。

選擇資料存取方法
　

正如本章前面所述，資料存取方法實際上就是一組物件，SQL Server 使用這些物件從資料庫中獲得資料。透過分析資料庫和資料庫中包含的資料，可以最佳化資料存取方法，減少 I/O 操作的數量。

和修改執行計劃一樣，修改最佳的資料存取方法也沒有一定的公式。下面的指導方針有助於選擇最佳的資料存取方法：

使用最佳的索引 為一個操作使用最佳的索引，這在獲得可能的最佳效能時是必須的。對於特定操作的最佳索引是最快速的找到資料，且最少 I/O 操作的一種方法。可以利用您對資料庫和資料的深入瞭解或使用 Query Analyzer 來確認最佳的索引。Query Analyzer 讓您嘗試不同的模擬狀況以確定哪個索引可以傳回最少的列數。（記住，Query Analyzer 只是簡單的估計了所要傳回的列數；要確定精確的列數，必須使用 Profiler。）
　

說明

正如第 17 章所提到的，索引對於 SQL Server 有很大的好處，但是如果使用不正確，可能會反過來影響效能。監控每個資料表的索引數目，特別是在執行了很多 INSERT、UPDATE 和 DELETE 陳述式操作的時候。太多的索引可能會導致這種類型操作的效能降低，這是由於修改索引的額外系統資源佔用所引起的效能降低。
使用覆蓋式索引 （covering indexes）　正如第 17 章所述，使用覆蓋式索引可以幫助您避免額外的 I/O 步驟。不必存取底層資料表，您可以從索引中獲得所需的資料。
　
減少傳回的資料列 決定是否需要從查詢傳回實際需要的所有資料。修改T-SQ L陳述式，以便於只存取需要存取的資料，不要傳回將被丟棄的資料列。減少從資料庫中獲得的資料列，這可以透過增加查詢的選擇性來達成。

使用提示
　

可以修改 T-SQL 陳述式來更改資料存取方法和執行計劃，但是如果執行時不夠不謹慎，反而會變更 T-SQL 陳述式的功能。較安全的最佳化 T-SQL 陳述式的方法是使用提示。提示可指定 Query Optimizer 要執行哪些操作和需要使用哪些物件。在本節中，將學習很多不同的 SQL Server 提示，以及它們的使用方法。

聯結提示
　

聯結提示（join hints）是用來指定 Query Optimizer 應該執行哪些類型的聯結操作。（如果在查詢中沒有指定類型，query optimizer 會自己選一個。）在 SQL Server 中，您可以執行巢狀迴圈聯結（nested loops joins）、雜湊聯結（hash joins）、合併聯結（merge joins）和遠端聯結（remote joins）。使用下列提示指定聯結的方法：

LOOP 指定巢狀迴圈聯結。在巢狀迴圈聯結中，將會檢查外部資料表中的每一列和內部資料表中的每一列，檢查值是否相等。
　
HASH 指定雜湊聯結。在雜湊聯結中，一個資料表會被重新組織為一個雜湊資料表。其他的資料表每次被掃描一列，雜湊函數就被用來搜尋相同的內容。
　
MERGE 指定一個排序合併聯結。在排序合併聯結中，每個資料表都被排序，然後按照降冪每次比較一列。
　
REMOTE 指定遠端聯結。遠端聯結是至少有一個聯結的資料表在遠端。

看看連結提示的範例，使用我們前面的範例（請參閱本章中的＜聯結操作＞一節），我們按照下列陳述式使用提示來指定一個雜湊聯結：

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName, 
     LastName, OrderDate
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
OPTION (HASH JOIN)

說明

聯結提示是彼此獨立的－每次只能使用其中的一種。

如果選擇使用 SQL-92 語法作為連結，您也能用提示來指定連結類型。使用SQL-92 語法，您可以重寫之前的查詢，如下：

SELECT OrderID, CustomerID, Employees.EmployeeID, Firstname, 
     LastName, OrderDate
FROM Orders INNER HASH JOIN Employees
ON (Orders.EmployeeID = Employees.EmployeeID)

聯結提示是進階的主題，我們並不能提供使用的經驗方法。有很多不同的選擇特殊聯結操作的原因，例如同等運算子的數目、在聯結中每個資料表的大小，以及聯結的多少資料表。有一種最佳的途徑可以確定更改聯結操作是否將提供額外的效能，就是在 Query Analyzer 中嘗試每一種類型的聯結，看看哪種可以提供最小的消耗。當然，Query Optimize r通常會為幫助選擇最佳的聯結操作。

查詢提示
　

查詢提示（query hints）用來指定如何執行特定的查詢操作。可用的查詢操作分為三類：分組（group by）、聯合（union）和混雜（miscellaneous）。

分組提示 下面的提示指定了如何執行 GROUP BY 或 COMPUTE 操作：

HASH GROUP BY 指定使用雜湊函數來執行 GROUP BY 操作。
　
ORDER GROUP BY 指定使用排序操作來執行 GROUP BY 操作。

使用前面的 GROUP BY 範例（請參閱本章的＜檢視總計操作＞一節），您可以按照下面使用提示來指定如何執行 HASH GROUP BY 操作：

SELECT CustomerID, SUM(OrderDetails.UnitPrice)
     FROM Orders, OrderDetails
HASH GROUP BY CustomerID
OPTION(HASH GROUP)

說明

GROUP BY 提示彼此獨立－每次只能使用其中一種。

聯合提示 下面的提示是用來指定如何執行 UNION 操作：

MERGE UNION 使用合併操作來執行 UNION。
　
HASH UNION 使用雜湊函數來執行 UNION。

CONCAT UNION 使用串聯功能來執行 UNION。
　
這是一個使用 CONCAT UNION 提示的範例：
```
SELECT OrderID, CustomerID, EmployeeID, OrderDATE
     FROM orders
     WHERE CustomerID = 'TOMSP'
UNION
SELECT OrderID, CustomerID, EmployeeID, OrderDate
     FROM orders
     WHERE EmployeeID = '4'
OPTION (CONCAT UNION)
```
說明

UNION 提示也是彼此獨立的。

不幸的是，沒有任何一定的公式可用以確定哪種 UNION 操作在您的環境中執行的最好。再一次說明，最好的途徑就是使用 Query Analyzer 來嘗試不同的 UNION 提示，看看哪種可以提供最少的消耗。通常 SQL Server Query Optimizer 可以為 UNION 提示決定最佳策略。

雜項提示 下面的提示可以用來執行多種的查詢操作：
FORCE ORDER 強制查詢按照查詢中資料表出現的次序執行。在預設狀態下，SQL Server 可以重新排序資料表存取。
　
ROBUST PLAN 強制 Query Optimizer 預備取得最有可能的最大資料列數。
　
以下是使用這個提示的範例：
```
SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName,
　　LastName, OrderDate
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
OPTION (ROBUST PLAN)
```
資料表提示
　

資料表提示 （table hints）用來控制資料表存取，兩種資料表提示如下：
- - FAST n 替代 FASTFIRSTROWS，用來保持向後的相容性。最佳化查詢以獲得最前面n列的資料。
    　
  - INDEX＝index_name 強制 Query Optimizer 在可能時使用指定的索引。在本章中的前面的範例之一示範了如何使用 INDEX 提示：
    　
    SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM orders WITH (INDEX = EmployeeID) WHERE EmployeeID = 5 OPTION (FAST 10)
WITH是選擇性的。

這裏的 INDEX = EmployeeID 提示將指定使用 EmployeeID 索引。透過指定FAST 10，SQL Server 將最佳化前面 10 列的取得（如果可能的話），然後傳回剩下的列。

本章總結
　

在本章中，您學到了如何使用 Query Analyzer 以確定對查詢最佳的執行計劃和資料存取方法。另外，您還學到了如何使用 Profiler 來檢視系統中執行的 T-SQL 陳述式，以及如何執行追蹤來確定是否其中一些 T-SQL 陳述式可能導致效能問題。還討論了如何根據您的資料庫和資料以最佳化執行計劃和資料庫存取方法。最後，您學到了如何使用提示來指定一個特定的執行計劃或資料存取方法。在第 36 章中，您學習的主題將發展為效能問題以及如何解決效能問題。