Optimization goals are a convenient way to match query demands with the best optimization techniques, thus ensuring optimal use of the optimizer’s time and resources. The query optimizer allows you to configure three types of optimization goals, which you can specify at three tiers: server level, session level, and query level.
Set the optimization goal at the desired level. The server-level optimization goal is overridden at the session level, which is overridden at the query level.
These optimization goals allow you to choose an optimization strategy that best fits your query environment:
allrows_mix – the default goal, and the most useful goal in a mixed-query environment. allows_mix balances the needs of OLTP and DSS query environments.
allrows_dss – the most useful goal for operational DSS queries of medium to high complexity. Currently, this goal is provided on an experimental basis.
allrows_oltp – the optimizer considers only nested-loop joins.
At the server level, use sp_configure. For example:
sp_configure "optimization goal", 0, "allrows_mix"
At the session level, use set plan optgoal. For example:
set plan optgoal allrows_dss
At the query level, use a select or other DML command. For example:
select * from A order by A.a plan "(use optgoal allrows_dss)"
In general, you can set query-level optimization goals using select, update, and delete statements. However, you cannot set query-level optimization goals in pure insert statements, although you can set optimization goals in insert…select statements.
