Export (0) Print
Expand All

View Plan Guide Properties

You can view the properties of plan guides in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL

In This Topic

Security

Permissions

The visibility of the metadata in catalog views is limited to securables that either a user owns or on which the user has been granted some permission.

Arrow icon used with Back to Top link [Top]

To view the properties of a plan guide

  1. Click the plus sign to expand the database in which you want to view the properties of 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 of which you want to view the properties and select Properties.

    The following properties show in the Plan Guide Properties dialog box.

    Hints

    Displays the query hints or query plan to be applied to the Transact-SQL statement. When a query plan is specified as a hint, the XML Showplan output for the plan is displayed.

    Is disabled

    Displays the status of the plan guide. Possible values are True and False.

    Name

    Displays the name of the plan guide.

    Parameters

    When the scope type is SQL or TEMPLATE, displays the name and data type of all parameters that are embedded in the Transact-SQL statement.

    Scope batch

    Displays the batch text in which the Transact-SQL statement appears.

    Scope object name

    When the scope type is OBJECT, displays the name of the Transact-SQL stored procedure, user-defined scalar function, multistatement table-valued function, or DML trigger in which the Transact-SQL statement appears.

    Scope schema name

    When the scope type is OBJECT, displays the name of the schema in which the object is contained.

    Scope type

    Displays the type of entity in which the Transact-SQL statement appears. This specifies the context for matching the Transact-SQL statement to the plan guide. Possible values are OBJECT, SQL, and TEMPLATE.

    Statement

    Displays the Transact-SQL statement against which the plan guide is applied.

  4. Click OK.

Arrow icon used with Back to Top link [Top]

To view the properties of 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.

    -- If a plan guide named “Guide1” already exists in the AdventureWorks2012 database, delete it.
    USE AdventureWorks2012;
    GO
    IF OBJECT_ID(N'Guide1') IS NOT NULL
       EXEC sp_control_plan_guide N'DROP', N'Guide1';
    GO
    -- creates a plan guide named Guide1 based on a SQL statement
    EXEC sp_create_plan_guide 
        @name = N'Guide1', 
        @stmt = N'SELECT TOP 1 * 
                  FROM Sales.SalesOrderHeader 
                  ORDER BY OrderDate DESC', 
        @type = N'SQL',
        @module_or_batch = NULL, 
        @params = NULL, 
        @hints = N'OPTION (MAXDOP 1)';
    GO
    -- Gets the name, created date, and all other relevant property information on the plan guide created above. 
    SELECT name AS plan_guide_name,
       create_date,
       query_text,
       scope_type_desc,
       OBJECT_NAME(scope_object_id) AS scope_object_name,
       scope_batch,
       parameters,
       hints,
       is_disabled
    FROM sys.plan_guides
    WHERE name = N’Guide1’;
    GO
    

For more information, see sys.plan_guides (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