sp_optgoal

Creates a user-defined optimization goal, and defines the set of active criteria included in the goal.

sp_optgoal 'goal_name’, action

action: action for sp_optgoal to perform. One of:

show | null | no action – displays the contents of the goal.

save – creates new goal or updates and existing goal.

delete – deletes the goal.

If you set these goals for the current session:

SET PLAN OPTLEVEL ase_current
SET PLAN  OPTGOAL allrows_mix
SET HASH_JOIN 1

This command saves these settings in a goal named goal_1:

sp_optgoal 'goal_1', 'save'

Use this command to use the settings for goal_1 for the current session:

set plan optgoal goal_1

Deletes goal_1:

sp_optgoal 'goal_1', 'delete'

goal_name cannot be longer than 12 characters.
sp_optgoal with no parameters displays a list of all user-defined optimizer goals.

You must be the system administrator or system security officer to create or drop goals. However, once created, all users can use the goal.

Any user can run sp_optgoal 'show'.

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect