Sdílet prostřednictvím

  • Článek

Understanding Plan Forcing

The USE PLAN query hint 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 Specifies a Query Plan 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 queries. These include queries against tables, clustered and nonclustered indexes, indexed views, and partitioned tables and indexes. USE PLAN can be specified with the data manipulation language (DML) statements INSERT, UPDATE, MERGE, or DELETE. Note that changes to the database schema, such as adding or dropping an index, can invalidate the plan specified in the USE PLAN hint. When USE PLAN is specified directly in a query, an invalid plan causes the query to fail. When USE PLAN is specified in a plan guide, an invalid plan will not cause a query to fail; however, the plan is compiled without using the hint and may not be the best choice. When the USE PLAN hint is used inside a plan guide, you can use the sys.fn_validate_plan_guide function to verify the validity of the plan. Based on the results of the function, you might decide to drop the plan guide and retune the query or modify the database design. For example, you might re-create the index specified in the plan guide.

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: