delete-a-plan-guide.md

title	Delete a Plan Guide | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.technology	performance
ms.topic	conceptual
helpviewer_keywords	plan guides [SQL Server], deleting
ms.assetid	aa4d3188-6927-43de-a3e3-90fc16eeaca7
author	MikeRayMSFT
ms.author	mikeray
manager	craigg

Delete a Plan Guide

You can delete (drop) a plan guide in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. Using [!INCLUDEtsql], 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.

Using SQL Server Management Studio

To delete a plan guide

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

2. Click the plus sign to expand the Plan Guides folder.

3. Right-click the plan guide you want to delete and select Delete.

4. In the Delete Object dialog box, ensure that the correct plan guide is selected and then click OK.

Using Transact-SQL

To delete a single plan guide

1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

2. On the Standard bar, click New Query.

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

1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

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

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