Enable or Disable a Plan Guide
You can disable and enable plan guides in SQL Server 2012 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:
-
To disable and enable plan guides, using:
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
To disable or enable a plan guide
-
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.
-
Click the plus sign to expand the Plan Guides folder.
-
Right-click the plan guide you want to disable or enable and select either Disable or Enable.
-
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
-
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.
-
Right-click the Plan Guides folder and then select either Enable All or Disable All.
-
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.
To disable or enable a plan guide
-
In Object Explorer, connect to an instance of Database Engine.
-
On the Standard bar, click New Query.
-
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
-
In Object Explorer, connect to an instance of Database Engine.
-
On the Standard bar, click New Query.
-
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).
