Posted by Erland Sommarskog on 11/15/07 22:19

(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]