X hits on this document

127 views

0 shares

0 downloads

0 comments

44 / 48

678

CHAPTER 28 PERFORMANCE TUNING

Table 28-7. sp_create_plan_guide Arguments

Argument plan_guide_name

Description The name of the new plan guide.

statement_text TEMPLATE

The SQL text identified for optimization.OBJECT | SQL | When OBJECT is selected, the plan guide will apply to the

statement text found within a specific stored procedure, function, or DML trigger. When SQL is selected, the plan guide will apply to statement text found in a stand-alone statement or batch. The TEMPLATE option is used to either enable or disable parameterization for a SQL statement. Recall from Chapter 22, in the topic “Modify Database Parameterization Behavior” that the PARAMETERIZATION option, when set to FORCED, increases the chance that a query will become param- eterized, allowing it to form a reusable query execution plan. SIMPLE parameterization, however, affects a smaller amount of queries (at SQL Server’s discretion). The TEMPLATE option is used to override either a database’s SIMPLE or FORCED parame- terization option. If a database is using SIMPLE parameterization, you can force a specific query statement to be parameterized. If a database is using FORCED parameterization, you can force a specific query statement to not be parameterized.

N ' [ s c h e m a _ n a m e . ] o b j e c t _ n a m e ' N'batch_text' | NULL |

Specifies either the name of the object the SQL text will be in, the batch text, or NULL, when TEMPLATE is selected.

N'@parameter_name data_type [,...n ]' | NULL

The name of the parameters to be used for either SQL or TEMPLATE type plan guides.

N'OPTION ( query_hint [,...n ] )' | NULL

The query hint or hints to be applied to the statement.

To remove or disable a plan guide, use the sp_control_plan_guide system-stored procedure:

sp_control_plan_guide [ @operation = ]

|

DROP ALL

|

DISABLE

|

DISABLE ALL

|

ENABLE

|

ENABLE ALL ]',

N'[

DROP

[ @name = ] N'plan_guide_name' ] The arguments of this command are described in Table 28-8.

Table 28-8. sp_control_plan_guide Arguments

Argument

Description

DROP DROP ALL DISABLE

The DROP operation removes the plan guide from the database. DROP ALL drops all plan guides from the database.

DISABLE disables the plan guide, but doesn’t remove it from the database.

DISABLE ALL ENABLE | EN

ABLE ALL

DISABLE ALL disables all plan guides in the database. ENABLE enables a disabled plan guide, and ENABLE ALL does so for all

p l a n _ g u i d e _ n a m e

disabled plan guides in the database. The name of the plan guide to perform the operation on.

Document info
Document views127
Page views127
Page last viewedSat Dec 10 17:22:57 UTC 2016
Pages48
Paragraphs987
Words15822

Comments