Partilhar via

Understanding Plan Guides

  • Artigo

SQL Server 2005 introduces the sp_create_plan_guide system stored procedure for creating plan guides to optimize the performance of queries. This procedure can be used when you cannot or do not want to change the text of the query directly. Plan guides can be useful when a small subset of queries in a database application deployed from a third-party vendor are not performing as expected. Plan guides influence optimization of queries by attaching query hints to them. In the sp_create_plan_guide statement, you specify the query that you want optimized and the OPTION clause that contains the query hints you want to use to optimize the query. When the query executes, SQL Server matches the query to the plan guide and attaches the OPTION clause to the query at run time.

Note

Plan guides can be created and used only in the SQL Server 2005 Standard, Developer, Evaluation, and Enterprise editions. Plan guides can be dropped in all editions.

Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed.

Another common hint to use with plan guides is the USE PLAN query hint. This query hint applies when you are already aware of an existing execution plan that can be substituted for the one selected by the optimizer for a particular query because you know it performs better. USE PLAN forces SQL Server to use a particular query plan, specified explicitly in the hint syntax, when executing the query. A plan guide that applies the USE PLAN query hint is especially useful when it is most convenient to get a good execution plan for a query by rewriting the query to force a join order, use join hints, or use index hints. For more information, see Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query.

For more information about RECOMPILE, OPTIMIZE FOR, USE PLAN, and other query hints, see Query Hint (Transact-SQL).

Warning

Plan guides that misuse query hints can cause compilation, execution, or performance problems. Plan guides should be used only by experienced developers and database administrators.

Plan guides can be created to match queries that are executed in the following contexts:

  • OBJECT plan guides match queries that execute in the context of Transact-SQL stored procedures, scalar functions, multistatement table-valued functions, and DML triggers.
  • SQL plan guides match queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object. SQL-based plan guides can also be used to match queries that parameterize to a specified form.
  • TEMPLATE plan guides match stand-alone queries that parameterize to a specified form. These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

OBJECT Plan Guides

Suppose the following stored procedure, which takes the @Country parameter, exists in a database application that is deployed against the AdventureWorks database:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

You notice that this stored procedure has been compiled and optimized for @Country = N'AU' (Australia). However, there are relatively few sales orders that originate from Australia. Performance suffers when the query executes using parameter values of countries with more sales orders. Because the country where the most sales orders originate is the United States, a query plan that is generated for @Country=N'US' would likely perform better for all possible values of the @Country parameter.

You can address this problem by modifying the stored procedure and adding the OPTIMIZE FOR query hint to the query. However, because the stored procedure is in a deployed application, you cannot directly modify the application code. Instead, you can create the following plan guide in the AdventureWorks database.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'

When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause that is also specified.

SQL Plan Guides

SQL plan guides apply to statements and batches that are frequently submitted by an application by using the sp_executesql system stored procedure. For example, consider the following stand-alone batch:

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

To prevent a parallel execution plan from being generated on this query, create the following plan guide:

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

Note

The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

Important

The values that supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text exactly as it was submitted in the actual query. For more information, see sp_create_plan_guide (Transact-SQL) and Using SQL Server Profiler to Create and Test Plan Guides.

SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a class of queries be parameterized. For more information, see Designing Plan Guides for Parameterized Queries.

TEMPLATE Plan Guides

TEMPLATE plan guides are used to override the parameterization behavior for specific query forms. You can create a TEMPLATE plan guide in either of the following situations:

  • The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of simple parameterization.
  • The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want forced parameterization to be attempted on a class of queries.

For more information, see Specifying Query Parameterization Behavior by Using Plan Guides.

You can also use TEMPLATE plan guides together with SQL plan guides. For example, you can create a TEMPLATE plan guide to make sure that a class of queries is parameterized. You can then create a SQL plan guide on the parameterized form of that query.

See Also

Concepts

Optimizing Queries in Deployed Applications by Using Plan Guides
Designing and Implementing Plan Guides

Other Resources

Query Performance
sp_create_plan_guide (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides

Help and Information

Getting SQL Server 2005 Assistance