Behavior of OPTION

2 Sep 2005 6:39 AM

parasada

we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
now there is a complaint that these procs are running quite slow. while
trying to figure out the problem for the sluggishness
of the procedure, i noticed that commenting this line produced different
plan while uncommenting this line produced quite a
different one. Also, the usage of indexes varied drastically between these
plans (commented v/s uncommented OPTION clause)
can anybody throw some light on why using or NOT using OPTION would change
the execution plan?
thx in advance

2 Sep 2005 7:00 AM

John Bell

Hi

I prefer to remove hints when upgrading significant versions as things like
changes to the Query Optimizer may mean you are not using specific features
and as you have found they may be slower.

It is also a good idea to benchmark the system before and after the upgrade
to make sure that it is performing better!

It also usually a good time to review the code for general "good practices"
such as declaring temporary tables a the start of a procedure, reducing the
use of unnecessary temporary tables, owner prefixes for tables and stored
procedures, correct error and transaction handling etc...

Make sure that indexes and statistics are in place and rebuilt.

You can then look at the code and the query plans for the slower procedures.

John

Show quote

"parasada" wrote:

>
> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>

2 Sep 2005 7:06 AM

Uri Dimant

parasda
Firstly , BOL has a pretty good explanation about all options that you can
use

Actually ,FORCE ORDER option specifies that the join order indicated by the
query syntax is preserved during query optimization. In fact , query
optimizer is free to decide in what order (join) ( believe me, it is smart
enough) to execute the query im more efficient way ,so by using this option
you limit the optimizer to create an efficient execution plan

Show quote

"parasada" <paras***@discussions.microsoft.com> wrote in message
news:12F9AE7A-C7F4-4228-88C1-C1C5E35FA47B@microsoft.com...
>
> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>