How to enable, disable or drop all or specific plan guides?

Problem: How to enable, disable or drop all or specific plan guides?

Solution: sp_control_plan_guide

This procedure controls or allows us to do few things

·         Drop one or all plan guides.

·         Disable one or all plan guides.

·         Enable one or all plan guides.

We can right click on the plan guides from a SSMS to enable or disable but it does not have a drop option. You can see the below screenshot on where to find the enable or disable option.

What is a Plan Guide:

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.

If you want to learn in detail on how to create a plan guide, please go through this article. How to create a plan guide.

Syntax:

sp_control_plan_guide [ @operation = ] N'<control_option>' 

  [ , [ @name = ] N'plan_guide_name' ] 

<control_option>::= 

{  

    DROP  

  | DROP ALL 

  | DISABLE 

  | DISABLE ALL 

  | ENABLE  

  | ENABLE ALL 

} 

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 and then disable, enable and drop the plan guides once after creation.

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

--Disable the plan guide. 

EXEC sp_control_plan_guide N'DISABLE', N'[maxdop plan guide on Address table]'; 

GO 

--Enable the plan guide. 

EXEC sp_control_plan_guide N'ENABLE', N'[maxdop plan guide on Address table]'; 

GO 

--Drop the plan guide. 

EXEC sp_control_plan_guide N'DROP', N'[maxdop plan guide on Address table]';

Example 2: In this example, I would like to stop all custom plan guides, if in case the database has changed a lot we might want to drop the plan guides or disable the plan guides. As they longer remain to perform the same. Below are few snippets to drop, disable and enable all plan guides.

--Disable all plan guides

USE DB; 

GO 

EXEC sp_control_plan_guide N'DISABLE ALL'; 

--Enable all plan guides

USE DB; 

GO 

EXEC sp_control_plan_guide N'ENABLE ALL'; 

--DROP all plan guides

USE DB; 

GO 

EXEC sp_control_plan_guide N'DROP ALL';