This topic has not yet been rated - Rate this topic

Delete a Plan Guide

SQL Server 2012

You can delete (drop) a plan guide in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Using Transact-SQL, you can also delete all of the plan guides in a database.

In This Topic

Before you begin:

Security
To delete a plan guide, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

Security

Permissions

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

Arrow icon used with Back to Top link [Top]

Using SQL Server Management Studio

To delete a plan guide

Click the plus sign to expand the database in which you want to delete 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 delete and select Delete.
In the Delete Object dialog box, ensure that the correct plan guide is selected and then click OK.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To delete a single 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''))';
GO
--Drop the plan guide.
EXEC sp_control_plan_guide N'DROP', N'Guide3';
GO

To delete 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.
Copy
USE AdventureWorks2012; GO EXEC sp_control_plan_guide N'DROP ALL'; GO

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

Arrow icon used with Back to Top link [Top]

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD