Share via


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:

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.