sp_optgoal

This system procedure contains the functionality to make optimization goals that are run and saved into global optimization levels in the server using sp_configure. You can use this at the session level using the set command, or globally via sp_configure.

Syntax

sp_optgoal 'goal_name’, action

Parameters

goal_name – name of the goal you are creating. goal_name cannot be longer than 12 characters.
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.

Examples

Example 1 – 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'
```
Either of these allow you to use the settings for goal_1 for the current session:
- Using the set command:
```
set plan optgoal goal_1
```
- Using sp_configure:
```
sp_configure "optimization goal", 1, "goal_1"
```
Example 2 – Deletes goal_1:
```
sp_optgoal 'goal_1', 'delete'
```

Usage

sp_optgoal with no parameters displays a list of all user-defined optimizer goals.

Permissions

The permission checks for sp_optgoal differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, users with sa_role and sa_serverprivs_role must have `manage opt goal` privilege to create or delete a goal. By default, sa_role and sa_serverprivs_role are granted the `manage opt goal` privilege. Once created, all users can use the goal. Any user can run sp_optgoal 'show'.
Disabled	With granular permissions disabled, you must be a user with sa_role to create or delete a goal. However, once created, all users can use the goal. Any user can run sp_optgoal 'show'.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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