瞭解計劃指南

SQL Server 2005 新推出 sp_create_plan_guide 系統預存程序以建立計劃指南來最佳化查詢的效能。當您無法或不想直接變更查詢的文字時，就可以使用此程序。當從協力廠商所部署的資料庫應用程式中查詢的小部分子集未依預期執行時，計劃指南就可以發揮其效用。計劃指南是將查詢提示附加至查詢來影響查詢的最佳化。在 sp_create_plan_guide 陳述式中，您指定您要最佳化的查詢，以及包含您要使用的查詢提示以最佳化查詢。當查詢執行時，SQL Server 會將查詢與計劃指南對應，並在執行階段將 OPTION 子句附加至查詢。

附註：
計劃指南只能在 SQL Server 2005 Standard、Developer、Evaluation 與 Enterprise 版本中建立和使用。在所有的版本中都可以卸除計劃指南。

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

另一個搭配計劃指南使用的一般提示是 USE PLAN 查詢提示。當您注意到現有的計劃指南可用以替代最佳化工具針對特定查詢所選取的計劃時，就適用此查詢提示，因為您知道它能夠執行地更好。USE PLAN 在執行查詢時，於提示語法中會明確指定強制 SQL Server 使用特定的查詢計劃。重寫查詢以強制聯結順序、使用聯結提示或使用索引提示，是取得查詢良好執行計劃最便利的方式時，適用 USE PLAN 查詢提示的計劃指南將特別有用。如需詳細資訊，請參閱＜計劃強制實例：建立計劃指南以強制從重寫查詢取得計劃＞。

如需 RECOMPILE、OPTIMIZE FOR、USE PLAN 以及其他查詢提示的詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

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

您可以建立計劃指南來搭配在下列內容中執行的查詢：

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

OBJECT 計劃指南

假設下列在資料庫應用程式中使用 @Country 參數的預存程序，是針對 AdventureWorks 資料庫所部署：

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

您注意到此預存程序已針對 @Country = N'AU' (澳大利亞) 編譯及最佳化。然而，有相當少的銷售訂單源自於澳大利亞。當查詢在多個銷售訂單上使用國家 (地區) 的參數值執行時，效能就會降低。由於大部分的銷售訂單的國家 (地區) 都是源自於美國，所以針對 @Country=N'US' 所產生的查詢計劃可能會比 @Country 參數所有可能的值執行的更好。

您可以修改預存程序並將 OPTIMIZE FOR 查詢提示加入查詢以處理此問題。不過，因為預存程序是在已部署的應用程式中，所以您無法直接修改應用程式的程式碼。您只能在 AdventureWorks 資料庫中建立下列計劃指南。

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'

當執行指定在 sp_create_plan_guide 陳述式中的查詢時，在最佳化前會先修改查詢以同時指定 OPTIMIZE FOR (@Country = N''US'') 子句。

SQL 計劃指南

SQL 計劃指南適用於應用程式使用 sp_executesql 系統預存程序來經常提交的陳述式和批次。例如，請考慮下列獨立批次：

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

若要防此在查詢上產生平行執行計劃，請建立下列計劃指南：

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

附註：
在包含要建立計劃指南的陳述式之批次中，將無法包含 USE database 陳述式。

重要事項：
針對 sp_create_plan guide 陳述式的 @module_or_batch 與 @params 引數所提供的值，必須完全符合在實際查詢中所提交的對應文字。如需詳細資訊，請參閱＜sp_create_plan_guide (Transact-SQL)＞與＜使用 SQL Server Profiler 建立及測試計劃指南＞。

當 PARAMETERIZATION 資料庫選項 SET 為 FORCED 時，或是當建立 TEMPLATE 計劃指南以指定要參數化的查詢類別時，SQL 計劃指南也可在參數化為相同形式的查詢上建立 SQL 計劃指南。如需詳細資訊，請參閱＜設計參數化查詢的計劃指南＞。

TEMPLATE 計劃指南

TEMPLATE 計劃指南可用以覆寫特定查詢形式的參數化行為。您可以在下列其中一種情況下建立 TEMPLATE 計劃指南：

PARAMETERIZATION 資料庫選項設定成 FORCED，但有一些要根據簡單參數化規則編譯的查詢。
PARAMETERIZATION 資料庫選項設定成 SIMPLE (預設值)，但是要在某個查詢類別嘗試強制參數化。

如需詳細資訊，請參閱＜使用計劃指南指定查詢參數化行為＞。

您也可以同時使用 TEMPLATE 計劃指南與 SQL 計劃指南。例如，您可以建立 TEMPLATE 計劃指南以確定參數化查詢類別。接著就可以在該查詢的參數化形式上建立 SQL 計劃指南。

請參閱

Last updated on 2008-12-15

共用方式為

瞭解計劃指南

OBJECT 計劃指南

SQL 計劃指南

TEMPLATE 計劃指南

請參閱

概念

其他資源

說明及資訊

其他資源