sp_control_plan_guide (Transact-SQL)

Drops, enables, or disables a plan guide.

Transact-SQL Syntax Conventions

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.

You can execute sp_control_plan_guide with the DROP or DROP ALL option in any edition of Microsoft SQL Server 2005; however, all other options are available only in the Standard and Enterprise editions.

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 AdventureWorks database.

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

See Also

Reference

Database Engine Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)
sp_create_plan_guide (Transact-SQL)
sys.plan_guides

Other Resources

Optimizing Queries in Deployed Applications by Using Plan Guides

Help and Information

Getting SQL Server 2005 Assistance