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.

In SQL Server 2005, XML-formatted query plans can be produced through the following ways:

• SET SHOWPLAN_XML

  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''.

• SET STATISTICS XML

• 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 2005 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> subelement.
• One <StmtCursor> element that has exactly one <CursorPlan> subelement.
• One or more <StmtSimple> elements without a <QueryPlan> subelement, and one <StmtCursor> element that has one <CursorPlan> subelement.

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 subelements that pertain to the <PhysicalOp> operator. Other content under the <RelOp> element is not considered by USE PLAN.

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 subelements 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.

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> subelement, it may appear in a forced plan as either a <RowCountSpool> or a <Spool> subelement. Similarly, if the plan contains a <Spool> subelement, it may appear in a forced plan as a <Spool> or <RowCountSpool> subelement.

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.

See Also

Concepts

Specifying Query Plans with Plan Forcing

Other Resources

Query Performance

Help and Information

Getting SQL Server 2005 Assistance

Change History