Posted by Roy Harvey (SQL Server MVP) on 11/15/07 12:21

On Thu, 15 Nov 2007 03:24:18 -0800 (PST), codefragment@googlemail.com
wrote:

>Hi
> I've heard 2 things recently, can I confirm if their true/false?
>
>(1) If you have a stored procedure and you want to optimise it you can
>call exec proc1,
>you could also use define/set for each of the variables and copy the
>code into query analyser,
>this then makes it easier to tune. However the optimiser works
>differently for these variables than it does for variables passed into
>the query via exec and will produce a less optimal
>plan

There is some truth here. When the value of the parameters is
available to the optimizer at compile time it can often choose an
optimal plan for THOSE parameters. When an execution plan is
generated it is cached for future use. If the plan is for a stored
procedure is very likely to be used with more than one set of
parameters. Since it was optimized with specific parameters but run
with a variety of parameters the execution plan might not be optimal
for all parameters.

>(2) There is a different optimiser used in query analyser than that
>used otherwise? A colleague
>had a problem where a stored procedure called from dotnet code was
>running slowly but
>one run from query analyser via exec, with exactly the same arguments,
>was running quickly

There is only one optimizer. Under some circumstances multiple
execution plans can be cached for the same stored procedure. The
different plans can have different execution plans, and give different
performance. One "bad" plan can hang around and give one user bad
performance while another "good" plan gives another user good
performance.

Roy Harvey
Beacon Falls, CT

[Back to original message]