發行項
04/02/2012

設計和實作計畫指南

當您無法或不想要直接變更查詢的文字時，就可以使用計畫指南來最佳化查詢的效能。計畫指南是將查詢提示或固定的查詢計畫附加至查詢，藉以影響查詢的最佳化。您可以建立計畫指南來搭配在下列內容中執行的查詢：

OBJECT 計畫指南可搭配在 Transact-SQL 預存程序、使用者定義純量函數、多個陳述式使用者定義資料表值函式以及 DML 觸發程序內容中執行的查詢。
SQL 計畫指南可搭配在不屬於資料庫物件的獨立 Transact-SQL 陳述式與批次內容中執行的查詢。以 SQL 為基礎的計畫指南可用以搭配參數化為指定形式的查詢。
TEMPLATE 計畫指南可搭配參數化為指定形式的獨立查詢。這些計畫指南可用以針對查詢類別，覆寫資料庫目前的 PARAMETERIZATION 資料庫 SET 選項。

如需詳細資訊，請參閱＜了解計畫指南＞。

您可以建立的計畫指南總數僅限於可用的系統資源。因此，您應該限制計畫指南，只用於可改善或穩定效能的關鍵任務查詢。計畫指南不應用來影響大部份查詢對於已部署應用程式的載入。

我們建議當您將應用程式升級至 SQL Server 的新版時，重新評估和測試計畫指南定義。效能微調需求和計畫指南符合的行為有可能會變更。雖然無效的計畫指南不會導致查詢失敗，但是系統將不會使用此計畫指南來編譯計畫。升級資料庫之後，建議您使用 sys.fn_validate_plan_guide 函數執行下列工作來驗證現有的計畫指南。或者，您也可以使用 SQL Server Profiler 中的 Plan Guide Unsuccessful 事件來監視是否有無效的計畫指南。

[!附註]

雖然計畫指南只能在 SQL Server Standard、Developer、Evaluation 和 Enterprise 版本中使用，但是計畫指南仍會顯示於所有版本中。您也可以將包含計畫指南的資料庫附加到任何版本中。當您將資料庫還原或附加至 SQL Server 的升級版本時，計畫指南仍維持不變。

將查詢提示附加至計畫指南

在計畫指南中所使用的有效查詢提示組合。當計畫指南能配合查詢時，系統會先將計畫指南之提示子句中指定的 OPTION 子句加入查詢中，然後再編譯和最佳化查詢。如果配合計畫指南的查詢已經有 OPTION 子句，在計畫指南中所指定的查詢提示將會取代查詢中的提示。不過，若要讓計畫指南配合已經有 OPTION 子句的查詢，您必須在指定查詢文字以符合 sp_create_plan_guide 陳述式時，包含查詢的 OPTION 子句。如果您要將計畫指南中所指定的提示加入查詢中已存在的提示，您不應該取代它們，而是必須在計畫指南的 OPTION 子句中同時指定原始提示和其他提示。

注意
不當使用查詢提示的計畫指南可能會造成編譯、執行或效能上的問題。計畫指南應該只能由資深的開發人員與資料庫管理員使用。

計畫指南中常用的查詢提示

可從計畫指南獲益的查詢通常是以參數為基礎，而且有可能執行的效果很差，因為它們使用快取的查詢計畫，這些參數值並不代表最糟榚的情況值或最具代表性的狀況值。OPTIMIZE FOR 與 RECOMPILE 查詢提示可用以處理此問題。OPTIMIZE FOR 指示 SQL Server 在最佳化查詢時使用參數的特定值。RECOMPILE 指示伺服器在執行後捨棄查詢計畫，以強制查詢最佳化工具在下次執行相同的查詢時，重新編譯新的查詢計畫。如需範例，請參閱＜了解計畫指南＞。

此外，您可以指定 INDEX 和 FORCESEEK 資料表提示當做查詢提示。將這些提示指定為查詢提示時，其行為模式就與內嵌資料表或檢視提示相同。INDEX 提示會強制查詢最佳化工具只使用指定的索引來存取參考之資料表或檢視內的資料。FORCESEEK 提示會強制最佳化工具只使用索引搜尋作業，以存取參考之資料表或檢視內的資料。這些提示提供額外的計畫指南功能，並讓您對於使用此計畫指南的查詢最佳化有更大的影響。如需範例，請參閱＜在計畫指南中使用 INDEX 和 FORCESEEK 查詢提示＞。

將查詢計畫附加至計畫指南

當您注意到現有執行計畫的效能比最佳化工具針對特定查詢所選取的計畫更好時，套用固定查詢計畫的計畫指南就很有用。請注意，將固定計畫套用至查詢是指，查詢最佳化工具將無法再改寫查詢計畫以配合統計資料和索引的變更。當您考慮使用固定查詢計畫的計畫指南時，請務必比較套用固定計畫的優點以及無法在資料散發和可用索引變更時自動改寫計畫之差異。

您可以在 sp_create_plan_guide 陳述式的 xml_showplan 參數中指定計畫的 XML 執行程序表或在 sp_create_plan_guide_from_handle 陳述式中指定快取計畫的計畫控制代碼，藉以將特定查詢計畫附加至計畫指南。這兩種方法都會將固定查詢計畫套用至目標查詢。

計畫指南配合需求

計畫指南的範圍僅限於建立它們的資料庫。因此，當查詢執行時，只有目前資料庫中所存在的計畫指南可以配合查詢。例如，如果 AdventureWorks2008R2 是目前的資料庫且執行下列查詢：

SELECT FirstName, LastName FROM Person.Person;

只有在 AdventureWorks2008R2 資料庫中的計畫指南能夠配合此查詢。

不過，如果 AdventureWorks2008R2 是目前的資料庫且執行下列陳述式：

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

只有在 DB1 中的計畫指南能夠配合查詢，因為查詢是在 DB1 中執行。

對於以 SQL 或 TEMPLATE 為基礎的計畫指南而言，SQL Server 會逐字元比較查詢的 @module_or_batch 和 @params 引數值，使兩個值相符。這表示您必須提供與 SQL Server 在實際批次中所收到的文字完全相符的文字。

當 @type = 'SQL' 而且 @module_or_batch 設定為 NULL 時，@module_or_batch 的值就會設定為 @stmt 的值。這表示，提供的 statement_text 值必須與提交給 SQL Server 的值採用完全相同的格式 (逐字元)。不會執行內部轉換來促成這個相符項。

一般而言，您應該使用 SQL Server Profiler 來測試計畫指南，以驗證查詢是否與計畫指南相符。從 SQL Server Management Studio 執行批次來測試以 SQL 或 TEMPLATE 為基礎的計畫指南時，可能會產生非預期的結果。如需詳細資訊，請參閱＜使用 SQL Server Profiler 建立及測試計畫指南＞。