計畫指南

文章
05/04/2023

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

當您無法或不想要在 SQL Server 中直接變更實際查詢的文字時，您可以使用計畫指南來最佳化查詢的效能。計畫指南是將查詢提示或固定的查詢計畫附加至查詢，藉以影響查詢的最佳化。當協力廠商所提供的資料庫應用程式中有少量查詢子集的執行情況不如預期時，使用計畫指南會非常有用。在計畫指南中，指定您要最佳化的 Transact-SQL 陳述式以及包含您想要使用之查詢提示的 OPTION 子句，或者是您想要用來將查詢進行最佳化的特定查詢計畫。執行查詢時，SQL Server 會比對 Transact-SQL 陳述式與計畫指南，然後在執行階段中，將 OPTION 子句附加至查詢或使用指定的查詢計畫。由於 SQL Server 查詢最佳化工具通常會選取最好的查詢執行計劃，因此，我們建議資深開發人員和資料庫管理員將計劃指南當作最後的解決辦法。

注意

查詢存放區提示讓您使用上更便利，不需要變更應用程式程式碼即可讓查詢計劃成形。查詢存放區提示比計劃指南更簡單。查詢存放區提示適用於 Azure SQL Database 和 Azure SQL 受控執行個體，以及 SQL Server 2022 (16.x) 和更新版本。

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

這項功能所強制產生的執行計劃將會與強制計劃相同或類似。由於產生的計劃可能與計劃指南所指定的計劃不相同，因此計劃效能可能會有所不同。罕見情況下，效能差異可能十分顯著且負面；在此情況下，系統管理員必須移除該強制計劃。

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

計畫指南的類型

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

OBJECT 計畫指南

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

假設下列採用 @Country_region 參數的預存程序位於針對 AdventureWorks2022 資料庫所部署的資料庫應用程式中：

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 查詢提示加入查詢以處理此問題。不過，因為預存程序是在已部署的應用程式中，所以您無法直接修改應用程式的程式碼。您只能在 AdventureWorks2022 資料庫中建立下列計畫指南。

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

另舉一例，請考慮使用 sp_executesql 提交的下列 SQL 陳述式。

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

若要為每次執行此查詢建立唯一的計畫，請建立下列計畫指南，並在 @hints 參數中使用 OPTION (RECOMPILE) 查詢提示。

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

重要

針對 @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 (預設值)，但是您要在某個查詢類別嘗試強制參數化。

計畫指南比對需求

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

SELECT FirstName, LastName FROM Person.Person;

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

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)

分享方式：

計畫指南

計畫指南的類型

OBJECT 計畫指南

SQL 計畫指南

TEMPLATE 計畫指南

計畫指南比對需求

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

另請參閱

意見反映

更多資源

分享方式：

計畫指南

計畫指南的類型

OBJECT 計畫指南

SQL 計畫指南

TEMPLATE 計畫指南

計畫指南比對需求

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

相關工作

另請參閱

意見反映

更多資源