Delete a Plan Guide
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
You can delete (drop) a plan guide in SQL Server 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:
To delete a plan guide, using:
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
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.
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.
USE AdventureWorks2022; GO EXEC sp_control_plan_guide N'DROP ALL'; GO
For more information, see sp_control_plan_guide (Transact-SQL).
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for