View Source

This tab displays any existing plan guides on the server for the statements being tuned. This can be useful when trying to determine whether a plan guide is, or should be, used for a query, or conversely, finding plan guides that have become obsolete.

DB Optimizer 18.9.x > Using the Plan Guides tab (SQL Server) > worddav70fa97f97fc79773c668dfcf57e55fb7.png

In addition to the Enabled and Drop controls, each plan guide entry on the tab shows columns corresponding to the sp_create_plan_guide call arguments the plan guide was created with.

Column	Description
Name	The name given the plan guide when it was created.
Statement	The text of the Transact-SQL statement
Type	The type of entity in which the statement occurs: OBJECT. In the context of a stored procedure, scalar function, multi-statement table- valued function, or DML trigger in the current database. SQL. In the context of a stand-alone statement or batch. TEMPLATE. Specifies that this plan guide applies to any query that parameterizes to the specified statement form. Only the `PARAMETERIZATION { FORCED \| SIMPLE }` hint can be specified if a Type of TEMPLATE is specified.
Module/Batch	Specifies either the name of the object in which the statement occurs or the batch text in which the statement appears.
Parameters	Specifies the definitions of all parameters in the statement.
Hints	Can be one of the following: An OPTION clause containing any valid sequence of query hints A query plan in XML format to be applied as a hint NULL

For a detailed understanding of the Parameters and Hints values, particularly when using the edit plan guide functionality described below, see the Microsoft SQL Server documentation.

You can modify any plan guide listed on the Plan Guides tab. You can enable or disable the plan guide, drop it, or specify parameters or hints.

To drop a plan guide

Select the Drop check box for that plan guide entry and then click the Apply button () on the far right of the tab.

To enable or disable a plan guide

Enable by selecting the Enabled check box (or deselect Enabled to disable) for the plan guide entry and then click the Apply button () on the far right of the tab.

To edit a plan guide

Right-click a plan guide entry and select Edit to open the Edit Plan Guide.
In the Parameters field, provide a valid sp_create_plan_guide stored procedure call
@params = argument
In the Hints field, provide a valid sp_create_plan_guide stored procedure call
@hints = argument
Click Next to preview the generated stored procedure calls.
Click Finish.

| | | | | | |