共用方式為

  • 發行項

規劃強制執行案例：建立指定查詢計畫的計畫指南

當您使用 sp_create_plan_guide 系統預存程序建立計畫指南，並針對 @hints 參數中的查詢指定 XML 執行程序表格式的查詢計畫時，可以強制查詢計畫。當您無法或不想直接變更應用程式時，可用計畫指南將查詢提示或查詢計畫套用至部署之應用程式中的查詢。如需有關計畫指南的詳細資訊，請參閱＜使用計畫指南對已部署應用程式中的查詢進行最佳化＞。在此狀況下，您會將特定的查詢計畫附加至計畫指南。

假設應用程式包含下列預存程序：

USE AdventureWorks;

GO

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))

AS

BEGIN

SELECT h.SalesOrderID, h.OrderDate, h.Comment

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 = @CountryRegion;

END;

GO

假設執行此程序的大多數查詢的效能很差，因為此查詢計畫未針對 @CountryRegion 參數的代表值或最差狀況的值進行最佳化。您想要強制這個預存程序來使用已針對特定國家或地區最佳化的特定查詢計畫。然而，您無法直接變更應用程式中的預存程序，因為您是從獨立的軟體廠商購買該應用程式。替代的作法是建立查詢的計畫指南，並在計畫指南中指定已針對代表性值最佳化的查詢計畫。

若要將查詢計畫附加到計畫指南，您必須先針對預存程序中的查詢取得最佳化查詢計畫。您的作法是執行預存程序中所定義的查詢，以代表值或最差狀況的常數值來取代 @CountryRegion 常數。然後，您查詢 sys.dm_exec_query_stats 動態管理檢視來從計畫快取中取得查詢計畫。建議將 XML 執行程序表指派給變數；否則，您必須在單引號前加上另一個單引號，以逸出 XML 執行程序表中的所有單引號。最後，您必須在 @hints 參數中指定 XML 執行程序表來建立計畫指南。

範例

下列程式碼範例將示範針對 Sales.GetSalesOrderByCountryRegion 預存程序取得最佳化查詢計畫，並將它附加到計畫指南所需的步驟。在執行此預存程序時，程序中所定義的查詢會符合計畫指南，而且查詢最佳化工具會使用計畫指南中所指定的查詢計畫。

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
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 = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
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 = N''US'';%');

EXEC sp_create_plan_guide 
    @name = N'Guide_for_GetSalesByCountryRegion',
    @stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
    @params = NULL,
    @hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');