sp_control_plan_guide (Transact-SQL)

Article
09/28/2023

Applies to: SQL Server

Drops, enables, or disables 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  
}

Arguments

N' plan_guide_name '
Specifies the plan guide that is being dropped, enabled, or disabled. plan_guide_name is resolved to the current database. If not specified, plan_guide_name defaults to NULL.

DROP
Drops the plan guide specified by plan_guide_name. After a plan guide is dropped, future executions of a query formerly matched by the plan guide are not influenced by the plan guide.

DROP ALL
Drops all plan guides in the current database. N'plan_guide_name cannot be specified when DROP ALL is specified.

DISABLE
Disables the plan guide specified by plan_guide_name. After a plan guide is disabled, future executions of a query formerly matched by the plan guide are not influenced by the plan guide.

DISABLE ALL
Disables all plan guides in the current database. N'plan_guide_name cannot be specified when DISABLE ALL is specified.

ENABLE
Enables the plan guide specified by plan_guide_name. A plan guide can be matched with an eligible query after it is enabled. By default, plan guides are enabled at the time they are created.

ENABLE ALL
Enables all plan guides in the current database. N'plan_guide_name**'**cannot be specified when ENABLE ALL is specified.

Remarks

Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error.

Disabling a disabled plan guide or enabling an enabled plan guide has no effect and runs without error.

Plans guides are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022. However, you can execute sp_control_plan_guide with the DROP or DROP ALL option in any edition of SQL Server.

Permissions

To execute sp_control_plan_guide on a plan guide of type OBJECT (created specifying @type ='OBJECT' ) requires ALTER permission on the object that is referenced by the plan guide. All other plan guides require ALTER DATABASE permission.

Examples

A. Enabling, disabling and dropping a plan guide

The following example creates a plan guide, disables it, enables it, and drops it.

--Create a procedure on which to define the plan guide.  
IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL  
    DROP PROCEDURE Sales.GetSalesOrderByCountry;  
GO  
CREATE PROCEDURE Sales.GetSalesOrderByCountry   
    (@Country nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h   
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID  
    INNER JOIN Sales.SalesTerritory AS t ON c.TerritoryID = t.TerritoryID  
    WHERE t.CountryRegionCode = @Country;  
END  
GO  
--Create the plan guide.  
EXEC sp_create_plan_guide N'Guide3',  
    N'SELECT *  
    FROM Sales.SalesOrderHeader AS h   
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID  
    INNER JOIN Sales.SalesTerritory AS t ON c.TerritoryID = t.TerritoryID  
    WHERE t.CountryRegionCode = @Country',  
    N'OBJECT',  
    N'Sales.GetSalesOrderByCountry',  
    NULL,  
    N'OPTION (OPTIMIZE FOR (@Country = N''US''))';  
GO  
--Disable the plan guide.  
EXEC sp_control_plan_guide N'DISABLE', N'Guide3';  
GO  
--Enable the plan guide.  
EXEC sp_control_plan_guide N'ENABLE', N'Guide3';  
GO  
--Drop the plan guide.  
EXEC sp_control_plan_guide N'DROP', N'Guide3';

B. Disabling all plan guides in the current database

The following example disables all plan guides in the AdventureWorks2022 database.

USE AdventureWorks2022;  
GO  
EXEC sp_control_plan_guide N'DISABLE ALL';