Using the USE PLAN Query Hint
The USE PLAN query hint takes an xml_plan as an argument. xml_plan is a string literal derived from the XML-formatted query plan that is produced for the query. The USE PLAN query hint can be specified as a query hint in a stand-alone SQL statement, or specified in the @hints parameter of a plan guide. To attach a query plan to a plan guide, we recommend that you use the xml_showplan parameter in sp_create_plan_guide or the sp_create_plan_guide_from_handle stored procedure.
Important
You should always indicate xml_plan as a Unicode literal by specifying the N prefix, as in N'xml_plan'. Doing this makes sure that any characters in the plan specific to the Unicode standard are not lost when the SQL Server Database Engine interprets the string.
In SQL Server, XML-formatted query plans can be produced through the following ways:
-
Important
When you are generating query plans by using SET SHOWPLAN_XML, quotation marks (') that appear in the plan must be escaped by a second quotation mark before using the plan with the USE PLAN query hint. For example, a plan that contains WHERE A.varchar = 'This is a string' must be escaped by modifying the code to WHERE A.varchar = ''This is a string''.
Querying the query_plan column of the sys.dm_exec_query_plan dynamic management function.
The SQL Server Profiler Showplan XML, Showplan XML Statistics Profile, and Showplan XML For Query Compile event classes.
For more information about producing and analyzing query plans, see Analyzing a Query.
The XML-formatted query plan specified in xml_plan must validate against the XSD schema Showplanxml.xsd in the SQL Server installation directory. Additionally, under the path that contains the <ShowPlanXML> <BatchSequence> <Batch> <Statements> elements, one of the following must appear:
One or more <StmtSimple> elements, exactly one of which contains a <QueryPlan> sub-element.
One <StmtCursor> element that has exactly one <CursorPlan> sub-element.
One or more <StmtSimple> elements without a <QueryPlan> sub-element, and one <StmtCursor> element that has one <CursorPlan> sub-element.
You can change the plan before you use it by using USE PLAN, such as by changing join orders and operators, and adjusting scans and seeks. However, format of the plan must still match Showplanxml.xsd. You may not be able to force a plan that has been changed. An error occurs if you use a plan in a USE PLAN hint when the plan is not one of the plans that SQL Server would typically consider for the query during optimization.
Query plans generated with the USE PLAN query hint are cached just like other query plans.
Limitations of the USE PLAN Query Hint
Database changes, such as dropping indexes, may invalidate a query plan specified by USE PLAN. A query plan can become obsolete even if a dropped object is not directly referenced in the plan. For example, a unique index may not be referenced explicitly in a query plan, but the index nevertheless enforces a uniqueness constraint on the data. A query plan that is referenced by USE PLAN can use this constraint to avoid using certain operators to enforce distinctness.
Sometimes, installing a service pack or a new release of SQL Server may prevent you from forcing a plan produced by an earlier version. Therefore, all USE PLAN hints should be tested whenever the server is upgraded.
Using the USE PLAN hint in a query overrides all join hints and index hints used in the same query.
USE PLAN cannot be used with the FORCE ORDER, EXPAND VIEWS, GROUP, UNION, or JOIN query hints, or when SET FORCEPLAN is set to ON.
Only query plans that can otherwise be found by the typical search strategy of the query optimizer can be forced by using USE PLAN. These plans generally specify that one child of each join be at the leaf level. Using USE PLAN to force other types of queries will cause an error.
Forced Query Plan Elements
Not all elements of the XML-formatted query plan are forced with the USE PLAN hint. Elements that compute scalar expressions are ignored, and so are some relational expressions. The query plan is forced for the following types of elements:
Plan tree structure and order of evaluation.
Execution algorithms such as join types, sorting, and unions.
Index operations such as scans, seeks, intersections, and unions.
Objects referenced explicitly such as other tables, indexes, and functions.
In particular, SQL Server forces the LogicalOp, PhysicalOp, and NodeID items found under the <RelOp> element, and also any sub-elements that pertain to the <PhysicalOp> operator. Other content under the <RelOp> element is not considered by USE PLAN.
Important
Information about cardinality estimates dictated by the <EstimateRows> element is not enforced by the USE PLAN query hint. Because the query optimizer uses cardinality estimation to determine the amount of memory to devote to running a query, you should maintain accurate statistics, even when you are using USE PLAN. For more information, see Using Statistics to Improve Query Performance.
The following table lists the relational operator values that are forced with the USE PLAN query hint for both the PhysicalOp and LogicalOp items, and any sub-elements that are required for each PhysicalOp value. The table also includes additional information that is required for each operator in the form of XPath-style paths relative to the subelement.
PhysicalOp |
LogicalOp |
Subelement |
Additional information1 |
|---|---|---|---|
Concatenation |
Concatenation Async Concat |
Concat |
Not applicable |
Constant Scan |
Constant Scan |
ConstantScan |
Not applicable |
Deleted Scan |
Deleted Scan |
DeletedScan |
Object/@Table |
UDX |
UDX |
Extension |
@UDXName |
Hash Match |
Inner Join Left Outer Join Right Outer Join Full Outer Join Left Semi Join Left Anti Semi Join Right Semi Join Right Anti Semi Join Aggregate Partial Aggregate Flow Distinct Union |
Hash |
Not applicable |
RID Lookup |
RID Lookup |
IndexScan |
Object/@Database, Object/@Schema, Object/@Table |
Index Scan Clustered Index Scan |
Index Scan Clustered Index Scan |
IndexScan |
Object/@Database, Object/@Schema, Object/@Table Object/@Index |
Index Seek Clustered Index Seek |
Index Seek Clustered Index Seek |
IndexScan |
Object/@Database, Object/@Schema, Object/@Table Object/@Index |
Inserted Scan |
Inserted Scan |
InsertedScan |
Object/@Table |
Log Row Scan |
Log Row Scan |
LogRowScan |
Not applicable |
Merge Join |
Inner Join Left Outer Join Right Outer Join Full Outer Join Left Semi Join Left Anti Semi Join Right Semi Join Right Anti Semi Join Cross Join Concatenation Union |
Merge |
Not applicable |
Merge Interval |
Merge Interval |
MergeInterval |
Not applicable |
Nested Loops |
Inner Join Left Outer Join Right Outer Join Full Outer Join Left Semi Join Left Anti Semi Join Right Semi Join Right Anti Semi Join Cross Join |
NestedLoops |
Not applicable |
Parallelism |
Gather Streams Repartition Streams Distribute Streams |
Parallelism |
Not applicable |
Row Count Spool |
Eager Spool Lazy Spool |
RowCountSpool2 |
Not applicable |
Segment |
Segment |
Segment |
Not applicable |
Sequence |
Sequence |
Sequence |
Not applicable |
Sequence Project |
Compute Scalar |
SequenceProject |
Not applicable |
Sort |
Sort Distinct Sort |
Sort |
Not applicable |
Table Spool Index Spool |
Eager Spool Lazy Spool |
Spool2 |
@PrimaryNodeId (for secondary spools only) ../RelOp/@NodeId (for RelOps representing primary spools only) |
Stream Aggregate |
Aggregate |
StreamAggregate |
Not applicable |
Switch |
Switch |
Switch |
Not applicable |
Table Scan |
Table Scan |
TableScan |
Object/@Database, Object/@Schema, Object/@Table |
Table-valued function |
Table-valued function |
TableValuedFunction |
Object/@Database, Object/@Schema, Object/@Table (table-valued function name is Object/@Table) |
Top |
Top |
Top |
Not applicable |
Sort |
Sort |
Sort |
Not applicable |
Top Sort |
TopN Sort |
TopSort |
Not applicable |
Table Insert |
Insert |
Update |
Object/@Table |
1 The number and order of these inputs for each relational operator must appear as shown in the table to force a plan with USE PLAN.
2 The ability to force a plan is limited in that if the plan contains a <RowCountSpool> sub-element, it may appear in a forced plan as either a <RowCountSpool> or a <Spool> sub-element. Similarly, if the plan contains a <Spool> sub-element, it may appear in a forced plan as a <Spool> or <RowCountSpool> sub-element.
The Assert, Bitmap, ComputeScalar, and PrintDataFlow operators are ignored by USE PLAN. The Filter operator is considered by USE PLAN, but its exact location in the plan cannot be forced.
For more information about the logical and physical operators used in query plans, see Logical and Physical Operators Reference.
Cursor Support
You can use the USE PLAN query hint together with queries that specify static or fast-forward-only cursors, whether requested through Transact-SQL or an API cursor function. Transact-SQL static cursors with a forward-only option are supported. Dynamic, keyset-driven and forward-only cursors are not supported.
For more information, see Using the USE PLAN Query Hint on Queries with Cursors.