sp_control_plan_guide (Transact-SQL)

Drops, enables, or disables a plan guide.

Transact-SQL Syntax Conventions

Składnia

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.

Uwagi

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.

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

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

Zobacz także

Odwołanie

Database Engine Stored Procedures (Transact-SQL)

System Stored Procedures (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

Koncepcje

Plan Guides