tuning stored procedure, variables and different optimisers

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

(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

ta

Re: tuning stored procedure, variables and different optimisers

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

Re: tuning stored procedure, variables and different optimisers

> There is some truth here. When the value of the parameters is
> available to the optimizer at compile time

but what I mean is not the parameters, but something like this

declare @var int
set @var = 1

select * from table where somecolumn=@var


as opposed to a stored procedure where @var is a parameter

This info came from this:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/ 0735621969/ref=sr_1_1?ie=UTF8&s=books&qid=1195162743&sr=8-1

p217 (dug this out), it just seems very odd though, I can't understand
why this would be the case so I could
not be understanding this correctly. I'd be interested in knowing what
Oracle\mySQL do.


> There is only one optimizer. Under some circumstances multiple
> execution plans can be cached for the same stored procedure.

what sort of circumstances?

thanks for the reply, appreciated

Re: tuning stored procedure, variables and different optimisers

(codefragment@googlemail.com) writes:
>> There is some truth here. When the value of the parameters is
>> available to the optimizer at compile time
>
> but what I mean is not the parameters, but something like this
>
> declare @var int
> set @var = 1
>
> select * from table where somecolumn=@var
>
> as opposed to a stored procedure where @var is a parameter
>...
> p217 (dug this out), it just seems very odd though, I can't understand
> why this would be the case so I could not be understanding this
> correctly.

It's not very odd once you know how that the optimizer builds the
plan for entire procedure or a batch. Thus when it builds the plan,
it has no idea of what value @var will have at run time. Yes, it is
pretty obvious in your example, but the optimizer does not perform
flow analysis of the code.

This is in differences to parameters where the optimizer has knowledge
about the values for the call that invoked the optimizer. Although it does
not know if subsequent calls will be using similar values. And, indeed,
this strategy backfires sometimes.

>> There is only one optimizer. Under some circumstances multiple
>> execution plans can be cached for the same stored procedure.
>
> what sort of circumstances?

An entry in the plan cache has a number of attributes associated with it.
The most important the setting of SET options and the default schema.

The typical reason why you experience "slow in application quick in
QA" is that Query Analyzer by default runs with SET ARITHABORT ON,
whereas no client API turns this option on by default. This setting
is an attribute of the plan, so different settings of ARITHABORT will
give different cache entries. As for one plan may be fast and one may
be slow there are two possible reasons:

1) The application first invoked the procedure with an odd set of
parameters, which called for a different plan than the common case.
2) The procedure could benefit from an existing indexed view or an
indexed computed column, but on SQL 2000, these are only considered
if ARITHABORT is ON.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: tuning stored procedure, variables and different optimisers

On Thu, 15 Nov 2007 13:42:36 -0800 (PST), codefragment@googlemail.com
wrote:

>> There is some truth here. When the value of the parameters is
>> available to the optimizer at compile time
>
>but what I mean is not the parameters, but something like this
>
>declare @var int
>set @var = 1
>
>select * from table where somecolumn=@var
>
>
>as opposed to a stored procedure where @var is a parameter

Hi,

If @var is a locally declared variable, its value is not yet known at
execution time (since the whole batch is compiled at once, before
execution starts). So the optimization will be based on general
statistics on the distribution of somecolumn.

If @var is a parameter to a stored procedure, its value is known at
execution time. The proc is compiled the first time it is called, and at
that time the value for the parameter is known. The optimizer will
create a plan that is optimized for the specific value. Note that in a
large majority of cases, the end result will be the same plan - but not
always.

In both cases, the plan is retained in the procedure cache, and reused
when an identical batch is executed in the first case, or when the same
proc is executed in the second case. The former is not a problem, the
latter *usually* neither - but in some cases, the execution plan that is
optimal for @var = 1 might be very slow for @var = 2. In those cases,
you'll see extremely slow execution if the proc happens to be called
with @var = 1 first, and with @var = 2 later.

This feature is called parameter sniffing. You'll find lots more info
when you search for it on google. And though I doon't have my copy of
Inside SQL Server at hand, I think that this is what you are referring
to.

> I'd be interested in knowing what
>Oracle\mySQL do.

I guess you'll have to ask that in another group.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis