sp_control_plan_guide (Transact-SQL)

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.

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 Features Supported by the Editions of SQL Server 2012. However, you can execute sp_control_plan_guide with the DROP or DROP ALL option in any edition of SQL Server.

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.

A. Enabling, disabling and dropping a plan guide

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

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

Reference

Concepts