Enable or Disable a Plan Guide

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

You can disable and enable plan guides in SQL Server by using SQL Server Management Studio or Transact-SQL. Either a single plan guides or all plan guides in a database can be enabled or disabled.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To disable and enable plan guides, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• 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. Always check for dependencies before dropping or modifying any of the objects listed above.

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

Security

Permissions

Disabling or enabling an OBJECT plan guide requires ALTER permission on the object (for example: function, stored procedure) that is referenced by the plan guide. All other plan guides require ALTER DATABASE permission.

Using SQL Server Management Studio

To disable or enable a plan guide

1. Click the plus sign to expand the database in which you want to disable or enable a plan guide, and then click the plus sign to expand the Programmability folder.

2. Click the plus sign to expand the Plan Guides folder.

3. Right-click the plan guide you want to disable or enable and select either Disable or Enable.

4. In either the Disable Plan Guide or Enable Plan Guide dialog box, verify that the chosen action was successful and then click Close.

To disable or enable all plan guides in a database

1. Click the plus sign to expand the database in which you want to disable or enable a plan guide, and then click the plus sign to expand the Programmability folder.

2. Right-click the Plan Guides folder and then select either Enable All or Disable All.

3. In either the Disable all Plan Guides or Enable all Plan Guides dialog box, verify that the chosen action was successful and then click Close.

Using Transact-SQL

To disable or enable a plan guide

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   --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''))';  
   --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  
   
   

To disable or enable all plan guides in a database

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   --Disable all plan guides in the database.  
   EXEC sp_control_plan_guide N'DISABLE ALL';  
   GO  
   --Enable all plan guides in the database.  
   EXEC sp_control_plan_guide N'ENABLE ALL';  
   GO  
   
   

For more information, see sp_control_plan_guide (Transact-SQL).