You can set the optimization goal at the server, session, or query level. The server-level optimization goal is overridden at the session level, which is overridden at the query level—which means you can set a different optimization goal at each level.
To set the optimization goal at the server level, you can:
Use the sp_configure command
Modify the optimization goal configuration parameter in the Adaptive Server configuration file
For example, to set the optimization level for the server to fastfirstrow, enter:
sp_configure "optimization goal", 0, "fastfirstrow"
To set the optimization goal at the session level, use set plan optgoal. For example, to modify the optimization goal for the session to allrows, enter:
set plan optgoal allrows_oltp
To verify the current optimization goal at the session level, enter:
select @@optgoal
To set the optimization goal at the query level, use the select or other DML command. For example, to change the optimization goal to allrows_oltp for the current query, enter:
select * from A order by A.a plan "(use optgoal allrows_oltp)"
At the query level only, you can specify the number of rows that Adaptive Server quickly returns when you set fastfirstrow as the optimization goal. For example, enter:
select * from A order by A.a plan "(use optgoal fastfirstrow 5)"
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 select ... insert statements.
fastfirstrow is relevant only for select statements; it incurs an error when used with other DML statements.
