Plan Forcing Scenario: Create a Plan Guide That Specifies a Query Plan
You can force a query plan when you are creating a plan guide by using the sp_create_plan_guide system stored procedure and specifying a query plan in XML Showplan format for the query in the @hints parameter. Plan guides are used to apply query hints or query plans to queries in deployed applications when you cannot or do not want to change the application directly. For more information about plan guides, see Optimizing Queries in Deployed Applications by Using Plan Guides. In this scenario, you are attaching a specific query plan to the plan guide.
Suppose your application contains the following stored procedure:
USE AdventureWorks2008R2;
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
Assume that most queries that execute this procedure are performing poorly because the query plan is not optimized for either a representative or "worst-case" value for the @CountryRegion parameter. You want to force this stored procedure to use a specific query plan that has been optimized for a specific country or region. However, you cannot directly change the stored procedure in the application because you purchased the application from an independent software vendor. Instead, you can create a plan guide for the query, specifying a query plan in the plan guide that has been optimized for the representative value.
To attach a query plan to a plan guide, you must first obtain an optimized query plan for the query in the stored procedure. You do this by executing the query defined in the stored procedure, substituting a representative, or "worst-case," constant value in place of the @CountryRegion parameter. Then you query the sys.dm_exec_query_stats dynamic management view to obtain the query plan from the plan cache. We recommend that you assign the XML Showplan to a variable; otherwise, you must escape any single quotation marks in the XML Showplan by preceding them with another single quotation mark. Finally, you create a plan guide specifying the XML Showplan in the @hints parameter.
Example
The following code example demonstrates the steps required to obtain an optimized query plan for the Sales.GetSalesOrderByCountryRegion stored procedure and attach it to a plan guide. When the stored procedure is executed, the query defined in the procedure is matched to the plan guide and the query optimizer uses the query plan specified in the plan guide.
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');