Understanding Plan Forcing

SQL Server 2005 introduces the USE PLAN query hint. USE PLAN can be used to force the query optimizer to use a specified query plan for a query. The USE PLAN query hint works by accepting the query plan that you want to use in XML format as an argument. USE PLAN can be used for queries whose plans cause slow execution times, but for which you know better plans exist. A common scenario may involve queries that executed well in an earlier version of SQL Server, but perform poorly under an upgraded version, whether this upgrade is a service pack or a full version upgrade. Most of the time, an upgrade leads to equal or better performance in most query execution times; however, there might be some exceptions. The USE PLAN query hint is available to handle those cases when a query plan that is selected by the query optimizer in an earlier product version is preferred over the one selected after the upgrade.

The USE PLAN query hint can be used with plan guides when you are troubleshooting poor performance of queries in deployed applications. Plan guides are used to apply query hints to queries when you cannot or do not want to directly change the application. For more information about plan guides, see Optimizing Queries in Deployed Applications by Using Plan Guides. For more detailed scenarios that show how the USE PLAN query hint can be applied in plan guides, see Plan Forcing Scenario: Create a Plan Guide That Uses a USE PLAN Query Hint and Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query.

Plan forcing can be used for most types of SELECT queries. These include queries against tables, clustered and nonclustered indexes, indexed views, and partitioned tables and indexes. USE PLAN cannot be specified with INSERT, UPDATE, or DELETE statements.

Query plans generated with the USE PLAN query hint are cached just like other query plans.

Limitations of Plan Forcing

Plan forcing limits a query to a single, static execution plan. Plan forcing removes the ability of the query optimizer to adapt to changing data sizes and distributions, new indexes, and other variables. Therefore, plan forcing, if misused, can cause performance problems. Plan forcing should be used only after fully exploring other options for improving query performance, such as using fresh, accurate statistics and optimized indexes. Plan forcing should be used sparingly and only by experienced database administrators and developers who are knowledgeable about performance issues and the changing environment of their database and applications.

When you are using the USE PLAN query hint to influence the compilation of a query in a deployed application, the hint should be used inside a plan guide instead of embedded directly in the application. By doing this, you can address the following pitfalls:

• You can change or remove the hint without modifying or recompiling the application. In limited cases, a USE PLAN hint that applied in one version of SQL Server may not apply in a future service pack or version release. Therefore, you may have to change or remove a USE PLAN hint after an application has been deployed.

• Using a large hint directly in the query can be avoided. This makes the query easier to read.

  Important

  When creating a plan guide that uses USE PLAN, you should verify during testing whether the query whose plan you want to force is typically submitted with a cursor request. Query plans for cursor-based queries differ from those for noncursor queries. Therefore, to create a plan guide specifying a USE PLAN hint that applies to a query submitted with a cursor request, the plan guide must specify a cursor plan. For more information, see Using the USE PLAN Query Hint on Queries with Cursors.

Plan forcing is not supported for the following types of queries in SQL Server 2005:

• Queries with dynamic, keyset-driven and forward-only cursors. Static and fast-forward-only cursors are supported. For more information, see Using the USE PLAN Query Hint on Queries with Cursors.
• Distributed queries.
• Full-text queries.
• Insert, delete, and update statements.

See Also

Concepts

Specifying Query Plans with Plan Forcing

Other Resources

Query Performance

Help and Information

Getting SQL Server 2005 Assistance

Change History