Plan Forcing Scenario: Create a Plan Guide That Uses a USE PLAN Query Hint
You can force a query plan when you are creating a plan guide by using the sp_create_plan_guide system stored procedure. Plan guides are used to apply query hints 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 the USE PLAN query hint to the plan guide.
Suppose the application contains the following stored procedure:
CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader h, Sales.Customer c,
Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country;
END;
By using the USE PLAN query hint, you want to attach a query plan to this stored procedure that is based on a representative or "worst-case" value for the @Country parameter, and up-to-date statistics about the data in the database at the end of a month. 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 the USE PLAN query hint in the plan guide.
Like any other scenario when you are using the USE PLAN query hint, you must obtain an XML plan for the query in the stored procedure that is appropriate for forcing with USE PLAN. In this case, at the end of a month, you can update statistics as you want, and then copy the query inside the stored procedure, substituting a representative, or "worst-case," constant value in place of the @Country parameter. Then, execute the query with SHOWPLAN_XML enabled.
SET SHOWPLAN_XML ON;
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader h, Sales.Customer c,
Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = N'US';
GO
SET SHOWPLAN_XML OFF;
GO
Another way to obtain an XML plan for the query in the stored procedure is by applying a trace event on the query by using SQL Server Profiler.
To obtain an XML-formatted query plan by using SQL Server Profiler
Start a new SQL Server Profiler trace event and select the Showplan XML event (located under the Performance node).
Issue a command to cause the query to compile, such as running the stored procedure for the first time.
Select the Showplan XML event in the trace that corresponds to the query.
Right-click the selected event and select Extract Event Data. You will be prompted to save the XML plan to a file.
Description
To use the XML-formatted query plan (obtained by either of the methods previously described) in the plan guide, paste the query plan as a string literal inside the USE PLAN query hint specified in the OPTION clause of sp_create_plan_guide. Within the XML plan itself, replace each single quotation mark with four single quotation marks to correctly escape the single quotation mark, because it is nested inside two string literals. The statement that is required to create the plan guide follows.
Code
EXEC sp_create_plan_guide N'Guide1',
N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader h, Sales.Customer c,
Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country',
N'OBJECT',
N'Sales.GetSalesOrderByCountry',
NULL,
N'OPTION (USE PLAN
N''<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1275">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader h, Sales.Customer c,
 Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
 AND CountryRegionCode = N''''US''''
" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.897567" StatementEstRows="15942.8" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="30">
<RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="15942.8" EstimateIO="0" EstimateCPU="0.267441" AvgRowSize="151" EstimatedTotalSubtreeCost="0.897567" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
</HashKeysProbe>
<RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="9592.5" EstimateIO="0" EstimateCPU="0.0400967" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0751921" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
</OuterReferences>
<RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="19" EstimatedTotalSubtreeCost="0.003293" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Index="[PK_SalesTerritory_TerritoryID]" Alias="[t]" />
<Predicate>
<ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[CountryRegionCode] as [t].[CountryRegionCode]=N''''US''''">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="CountryRegionCode" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''''US''''" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1918.5" EstimateIO="0.00534722" EstimateCPU="0.00226735" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0318004" Parallel="0" EstimateRebinds="4" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Index="[IX_Customer_TerritoryID]" Alias="[c]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="TerritoryID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[TerritoryID] as [t].[TerritoryID]">
<Identifier>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="31465" EstimateIO="0.520162" EstimateCPU="0.0347685" AvgRowSize="155" EstimatedTotalSubtreeCost="0.554931" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[PK_SalesOrderHeader_SalesOrderID]" Alias="[h]" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>'')'
See Also
Concepts
Plan Forcing Scenarios and Examples
Specifying Query Plans with Plan Forcing
Other Resources
Query Performance
sp_create_plan_guide (Transact-SQL)