Export (0) Print
Expand All

Enable or Disable a Plan Guide

You can disable and enable plan guides in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Either a single plan guides or all plan guides in a database can be enabled or disabled.

In This Topic

Limitations and Restrictions

  • 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. Always check for dependencies before dropping or modifying any of the objects listed above.

  • Disabling a disabled plan guide or enabling an enabled plan guide has no effect and runs without error.

Security

Permissions

Disabling or enabling 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]

To disable or enable a plan guide

  1. Click the plus sign to expand the database in which you want to disable or enable 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 disable or enable and select either Disable or Enable.

  4. In either the Disable Plan Guide or Enable Plan Guide dialog box, verify that the chosen action was successful and then click Close.

To disable or enable all plan guides in a database

  1. Click the plus sign to expand the database in which you want to disable or enable a plan guide, and then click the plus sign to expand the Programmability folder.

  2. Right-click the Plan Guides folder and then select either Enable All or Disable All.

  3. In either the Disable all Plan Guides or Enable all Plan Guides dialog box, verify that the chosen action was successful and then click Close.

Arrow icon used with Back to Top link [Top]

To disable or enable a plan guide

  1. In Object Explorer, connect to an instance of Database Engine.

  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''))';
    --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
    
    

To disable or enable all plan guides in a database

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

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

    --Disable all plan guides in the database.
    EXEC sp_control_plan_guide N'DISABLE ALL';
    GO
    --Enable all plan guides in the database.
    EXEC sp_control_plan_guide N'ENABLE ALL';
    GO
    
    

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

Arrow icon used with Back to Top link [Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft