計畫指南

發行項
06/25/2013

當您無法或不想要在 SQL Server 2012 中直接變更實際查詢的文字時，您可以使用計畫指南來最佳化查詢的效能。計畫指南是將查詢提示或固定的查詢計畫附加至查詢，藉以影響查詢的最佳化。當協力廠商所提供的資料庫應用程式中有少量查詢子集的執行情況不如預期時，使用計畫指南會非常有用。在計畫指南中，指定您要最佳化的 Transact-SQL 陳述式以及包含您想要使用之查詢提示的 OPTION 子句，或者是您想要用來將查詢進行最佳化的特定查詢計畫。執行查詢時，SQL Server 會比對 Transact-SQL 陳述式與計畫指南，然後在執行階段中，將 OPTION 子句附加至查詢或使用指定的查詢計畫。

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

[!附註]

並非每個 Microsoft SQL Server 版本都可使用計畫指南。如需 SQL Server 版本所支援功能的清單，請參閱＜SQL Server 2012 版本支援的功能＞。在任何版本中都可以看到計畫指南。您也可以將包含計畫指南的資料庫附加到任何版本中。當您將資料庫還原或附加至 SQL Server 的升級版本時，計畫指南仍維持不變。

計畫指南的類型

您可以建立下列類型的計畫指南。

OBJECT 計畫指南
OBJECT 計畫指南可搭配在 Transact-SQL 預存程序、純量使用者定義函數、多個陳述式資料表值使用者定義函數以及 DML 觸發程序內容中執行的查詢。

假設下列採用 @Country\_region 參數的預存程序位於針對 AdventureWorks2012 資料庫所部署的資料庫應用程式中：
```
CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
        Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country_region
END;
```
假設此預存程序已針對 @Country\_region = N'AU' (澳洲) 編譯及最佳化。不過，由於來自澳洲的銷售訂單相當少，因此當查詢在多個銷售訂單上使用國家 (地區) 的參數值執行時，效能就會降低。由於大部分的銷售訂單都是來自美國，所以針對 @Country\_region = N'US' 所產生的查詢計畫可能會比 @Country\_region 參數的所有可能值具有更好的執行效能。

您可以修改預存程序並將 OPTIMIZE FOR 查詢提示加入查詢以處理此問題。不過，因為預存程序是在已部署的應用程式中，所以您無法直接修改應用程式的程式碼。您只能在 AdventureWorks2012 資料庫中建立下列計畫指南。
```
sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';
```
執行在 sp_create_plan_guide 陳述式中指定的查詢時，在最佳化前會先修改查詢以包含 OPTIMIZE FOR (@Country = N''US'') 子句。

SQL 計畫指南
SQL 計畫指南可搭配在不屬於資料庫物件的獨立 Transact-SQL 陳述式與批次內容中執行的查詢。以 SQL 為基礎的計畫指南可用以搭配參數化為指定形式的查詢。 SQL 計畫指南會套用至獨立 Transact-SQL 陳述式和批次。這些陳述式通常是由應用程式使用 sp_executesql 系統預存程序進行提交。例如，請考慮下列獨立批次：

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

若要防止這項查詢產生平行執行計畫，請建立下列計畫指南並將 @hints 參數中的 MAXDOP 查詢提示設定為 1。

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

重要事項
針對 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 計畫指南可搭配參數化為指定形式的獨立查詢。這些計畫指南可用以針對查詢類別，覆寫資料庫目前的 PARAMETERIZATION 資料庫 SET 選項。

您可以在下列其中一種情況下建立 TEMPLATE 計畫指南：
- PARAMETERIZATION 資料庫選項設定成 FORCED，但有一些要根據簡單參數化規則編譯的查詢。
- PARAMETERIZATION 資料庫選項設定成 SIMPLE (預設值)，但是您要在某個查詢類別嘗試強制參數化。

計畫指南配合需求

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

SELECT FirstName, LastName FROM Person.Person;

只有在 AdventureWorks2012 資料庫中的計畫指南能夠配合此查詢。不過，如果 AdventureWorks2012 是目前的資料庫且執行下列陳述式：

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 或 OBJECT) 計畫指南和 TEMPLATE 計畫指南都適用於陳述式時，只會使用一般計畫指南。

[!附註]

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

計畫指南對於計畫快取的影響

針對某個模組建立計畫指南時，就會從計畫快取中移除該模組的查詢計畫。針對某個批次建立 OBJECT 或 SQL 類型的計畫指南時，就會移除具有相同雜湊值之批次的查詢計畫。建立 TEMPLATE 類型的計畫指南時，就會從該資料庫內部的計畫快取中移除所有單一陳述式批次。

工作	主題
描述如何建立計畫指南。	建立新的計畫指南
描述如何建立參數化查詢的計畫指南。	建立參數化查詢的計畫指南
描述如何使用計畫指南控制查詢參數化行為。	使用計畫指南指定查詢參數化行為
描述如何將固定的查詢計畫併入計畫指南。	將固定的查詢計畫套用至計畫指南
描述如何在計畫指南中指定查詢提示。	將查詢提示附加至計畫指南
描述如何檢視計畫指南屬性。	檢視計畫指南屬性
描述如何使用 SQL Server Profiler 建立和測試計畫指南。	使用 SQL Server Profiler 建立及測試計畫指南
描述如何驗證計畫指南。	升級之後驗證計畫指南

請參閱

參考

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

共用方式為

計畫指南

計畫指南的類型

計畫指南配合需求

計畫指南對於計畫快取的影響

相關工作

請參閱

參考

其他資源