計畫指南

發行項
06/13/2017

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

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

注意

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

計畫指南的類型

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

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

假設下列採用 _region 參數的預存程式@Country位於針對 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' (澳洲) 編譯及最佳化。不過，由於來自澳洲的銷售訂單相當少，因此當查詢在多個銷售訂單上使用國家/地區的參數值執行時，效能就會降低。因為大部分的銷售訂單都源自美國，所以針對 @Countryregion = N'US' _ 產生的查詢計劃可能會對 _region 參數的所有可能值@Country執行得更好。

您可以修改預存程序並將 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;

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

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)';

重要

針對 @module_or_batch 陳述式的 @params 與 sp_create_plan guide 引數所提供的值必須符合在實際查詢中所提交的對應文字。如需詳細資訊，請參閱 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 類型的計畫指南時，就會從該資料庫內部的計畫快取中移除所有單一陳述式批次。

Task	主題
描述如何建立計畫指南。	建立新的計畫指南
描述如何建立參數化查詢的計畫指南。	建立參數化查詢的計畫指南
描述如何使用計畫指南控制查詢參數化行為。	使用計畫指南指定查詢參數化行為
描述如何將固定的查詢計畫併入計畫指南。	將固定的查詢計畫套用至計畫指南
描述如何在計畫指南中指定查詢提示。	將查詢提示附加至計畫指南
描述如何檢視計畫指南屬性。	檢視計畫指南屬性
描述如何使用 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)

共用方式為

計畫指南

計畫指南的類型

計畫指南比對需求

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

另請參閱

其他資源

共用方式為

計畫指南

計畫指南的類型

計畫指南比對需求

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

相關工作

另請參閱

其他資源