Setting Optgoal for a session

[Amal]

Hello,

Just a curious question for which I haven't got a clear answer. If I change
the optimization goal for a session, are the subsequently executed stored
procedures recompiled before execution. Is it dependent on - if the stored
procs are already in procedure cache or not.

Thanks
Amal

[Mark A. Parsons]

Should be relatively easy to see for yourself ...

- write a proc with a single query, a query you know will compile differently under different optgoals

- set showplan on

- run proc a few times under one optgoal, querying monCachedProcedures to make sure the CompileDate and PlanID values do
not change (ie, proc is compiled once and re-used multiple times) [also make note of the associated query plan]

- change the session's optgoal

- run proc a few times under the new optgoal, querying monCachedProcedures to see what happens for the first (and
subsequent) runs under the new optgoal (eg, does the CompileDate and PlanID change for the first run?) [also make note
of any changes in the associated query plan]

NOTE: if running your tests in quick succession you'll probably want to do something like
'convert(varchar,CompileDate,109)' in order to see if the secs/msecs change

Sooooo ... "When in doubt, try it out!"

Post by Amal
Hello,
Just a curious question for which I haven't got a clear answer. If I change
the optimization goal for a session, are the subsequently executed stored
procedures recompiled before execution. Is it dependent on - if the stored
procs are already in procedure cache or not.
Thanks
Amal

[Kristian]

Hello,

I tried this with 15.5 earlier this year and noticed that you will have to
clear the cache manually, otherwise it will use the cached queryplans.

-Kristian

Post by Amal
Hello,
Just a curious question for which I haven't got a clear answer. If I
change the optimization goal for a session, are the subsequently executed
stored procedures recompiled before execution. Is it dependent on - if the
stored procs are already in procedure cache or not.
Thanks
Amal