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 two types of optimization goals, which you can specify at three levels: server, session, and query.
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_oltp – this goal attempts to reduce any query processing behavior changes when upgrading from pre-15.0 releases.
allrows_mix – the default goal, and the most useful goal in a mixed-query environment. This goal balances the needs of OLTP and DSS query environments.
allrows_dss – the most useful goal for operational DSS queries of medium-to-high complexity.
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 the select or other DML command. For example:
select * from A order by A.a plan “(use optgoal allrows_dss)”
