Setting the optimization level

By default, Adaptive Server does not enable some performance related optimizer settings, and you must enable them using the set command. Because these optimizer settings are not enabled, any applications already running efficiently are not effected by, nor benefit from, optimizer changes when upgrading to the latest version of Adaptive Server.

Adaptive Server allows you to set the optimization level globally (that is, the optimization levels are set across the server) and at the session level. Enabling these changes increases query performance for many applications, though Sybase recommends additional performance testing.

Use the @@optlevel global variable to determine the current optimization level settings:

select @@optlevel

The optimization settings are organized according to which optimization changes are available for each Adaptive Server release. Table 7-1 describes the settings:

**Table 7-1: Opimization level**
Parameter	Description
ase_current	Enables all optimizer changes through the current release
ase_default	Disables all optimizer changes since version 1503 ESD #1
ase1503esd2	Enables all optimizer changes through version 15.0.3 ESD #2
ase1503esd3	Enables all optimizer changes through version 15.0.3 ESD #3

These optimization level criteria are enabled by default:

**Table 7-2: Optimization criteria enabled by default**
Setting	Description
cr421607	Support `NULL=NULL` merge and hash join keys
cr467566	Allow abstract plans and statement caches to work together
cr487450	Improves distinct costing for multitable outer joins and semijoins
cr497066	Infer the nullability of isnull by observing its parameters
cr500736	Support nocase sort order columns in merge join and hash join keys
cr531199	Increase the number of useful nested loop join plans the optimizer considereds
cr534175	Compute group by worktables in nested subqueries only once, when possible
cr544485	Mark subquery join predicates with distinct view as SARGs
cr545059	Reduce buffer manager optimization sort usage
cr545180	Avoid reformating with no SARGs if a useful index exists
cr545379	Disallow reformatting on user-forced index scan
cr545585	Covered iscan CPU costing too expensive
cr545653	Avoid inner table buffer estimate starvation
cr545771	Improve multi-table outer join and semijoin costing
cr546125	Allow a nonunique index scan for implicitly updatable cursors
cr552795	Eliminate unnecessary duplicate rows during reformatting
cr562947	Allow cursor table scans
data_page_prefetch_costing	Adds clustered row bias
mru_buffer_costing	Wash size buffer limit for MRU

These optimization level criteria are enabled when you enable ase1503esd2:

**Table 7-3: Optimization criteria enabled with ase1503esd2**
Setting	Description
cr556728	Facilitate merge joins between small tables
cr559034	Avoid preferring non-covering over covered index scans
allow_wide_top_sort	Allow top sorts to exceed max row size
avoid_bmo_sorts	Avoid sorts used only for buffer manager optimization
conserve_tempdb_space	Keep estimated temporary databases below resource granularity
distinct_exists_transform	Transform distinct to semi-join
join_duplicate_estimates	Avoid overestimates of join duplicates
outer_join_costing	Outer join row counts and histogramming
search_engine_timeout_factor	Open cursor command takes a long time with a complex select statement
timeout_cart_product	Timeout queries involving cartesian product and more than 5 tables.

Table 7-4 lists optimization criteria that are enabled when you enable ase1503esd3 or ase_current

**Table 7-4: Optimization criteria enabled with ase1503esd3 and ase_current**
Setting	Description
auto_template_stats	Automatically generate statistics for temporary tables
use_mixed_dt_sarg_under_specialor	Allow special or for mixed datatype SARGs in an in or or list

These optimization criteria are off by default:

**Table 7-5: Optimization criteria that are disabled by default**
Setting	Description
full_index_filter	Eliminate noncovered full index scan strategies
no_stats_distinctness	Allow duplicate estimates without statistics

Set the optimization and criteria levels using one of these methods:

At the session level – use set plan optlevel to set the optimization level for the current session. This configures the session to use the all optimization changes up through the current version of Adaptive Server:
```
set plan optlevel ase_current
```
For individual logins – use sp_modifylogin to set the optimization level for logins. sp_modifylogin calls a user-created stored procedure the defines the optimization level. For example, if you create this stored procedure to enable the ase1503esd2 optimization level, but disable the optimization level for cr545180:
```
create proc login_proc
as
set plan optlevel ase1503esd2
set cr545180 off
go
```
You may apply these optimization settings to any login. This applies the settings from login_proc to user joe:
```
sp_modifylogin joe, 'login script', login_proc
```
Across the server – use the sp_configure “optimizer level” parameter to set the optimization level globally. This sets enables all optimizer changes up to the current version of Adaptive Server:
```
sp_configure 'optimizer level', 0, 'ase_current'
```
Within an abstract plan – use the optlevel abstract plan to set the optimizer level. This example enables all optimizer changes up to the current version of Adaptive Server for the current plan:
```
select name from sysdatabases
plan '(use optlevel ase_current)'
```
During a session with the set command – use the set command to change the optimizer criteria level for the current session. Optimizer criteria changes may improve the performance for some queries while deteriorating others. You may find better performance by applying the optimizer criteria level at a fine grain level (perhaps at the query level). Adaptive Server denotes some optimizer criteria levels by their CR numbers, while other more recent optimizer changes are specified with descriptive names. Use sp_options to view the list of available options in the current release. This enables the optimization criteria for CR 545180:
```
set CR545180 on
```
When you enable an optimization criteria, Adaptive Server retains the previous optimization level.