What are plan guides and how to use a custom plan guide?

Problem: What are plan guides and how to use a custom plan guide?

Solution: sp_create_plan_guide

Plan Guides:

For every query, there will be an execution plan which is actual and estimated by the SQL Server Optimizer. Plan guides lets you change or optimize a query execution plan performance without changing the query of a procedure. A plan guide has a major influence on optimization of specific query text with the help of query hints attached to the plan guides.

You can see the below screenshot to see where a plan guide resides in a SQL Server Management studio.

Benefits of plan guides:

·         Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

·         You can disable or enable the plan guides when you think that this plan guide no longer remains implied for the queries.

How to Create a Plan guide:

In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. When the query executes, SQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

you can use gui from SSMS to create a plan guide as well, by right clicking on the plan guide folder.

Important Notes:

The total number of plan guides you can create is limited only by available system resources.

Downsides of Plan guides

Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Plan guides should not be used to influence most of the query load of a deployed application.

Syntax:

sp_create_plan_guide [ @name = ] N'plan_guide_name'  

    , [ @stmt = ] N'statement_text'  

    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  

    , [ @module_or_batch = ]  

      {   

                    N'[ schema_name. ] object_name'  

        | N'batch_text'  

        | NULL  

      }  

    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   

    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'   

                 | N'XML_showplan'  

                 | NULL }  

Example:

In this example, I would like to show we can create an explicit plan guides to reduce the degree of parallelism using MAXDOP OPTION Hint.

To explain what I am trying to achieve let me show how a query optimizer chose to return the results before I have a specific plan guide on the SQL Statement and then create a plan guide for that query and observe the difference.

Before plan guides

This is how the queries execution plan looks before I have a specific execution plan. As you can see it reached to a degree of parallelism to 8. Which means it uses 8 parallel operators to get the task done which will chew the CPU for a while.

Creating a plan guide to use attached option of MAXDOP to the query:

-- creates a plan guide  based on a SQL statement 

EXEC sp_create_plan_guide  

    @name = N'maxdop plan guide on Address table',  --name of the plan guide

    @stmt = N'select * from [dbo].[Address]

order by AddressID DESC',  --statement to improve the query optimization

    @type = N'SQL',  -- object or SQL or Template this is the scope of the Plan

    @module_or_batch = NULL,   --Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears.

    @params = NULL,  --Specifies the definitions of all parameters that are embedded in statement_text.

    @hints = N'OPTION (MAXDOP 1)';  --Specifies an OPTION clause to attach to a query that matches

After the plan guide

This is a forced execution plan as our query statement is specified on the plan guide it can choose the best optimized plan guide by reducing the parallelism.