Optimization goals

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:

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)”