bad query executionplan

26 Aug 2005 10:54 AM

lutz.jahnke

Hallo!

I'm having a problem with bad query executionplans. The optimizer won't
get the right plan to execute the query fast.

The optimizer perform cluster index scan instead of using a index seek
an also using a hash join instead of a loop join.

Now, when I set a join hint to use the loop join, the optimizer uses
the right index to.

Another methode i have found is to set forceplan to on. Then the
optimzer will also uses loop join and the correct indexes.

Anyone have an idea?

Server:
Language German
SQL 2000 SP3a and SP4
Windows 2003 SP1 and without

I already have reindexed all participating indexes (drop & create) an
drop an recreate all statistics.

Regards
Lutz

26 Aug 2005 12:25 PM

Dan Guzman

Please post your DDL (including constraints and indexes) and problem query
or stored procedure so that we can help.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<lutz.jah***@nord-com.net> wrote in message

Show quote

news:1125053676.823090.8480@g49g2000cwa.googlegroups.com...
> Hallo!
>
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
>
> The optimizer perform cluster index scan instead of using a index seek
> an also using a hash join instead of a loop join.
>
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
>
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
>
> Anyone have an idea?
>
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
>
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
>
> Regards
> Lutz
>

26 Aug 2005 2:48 PM

Gert-Jan Strik

In addition to Dan's reply: please specify if you are using a stored
procedure with parameters (which can benefit from parameter sniffing) or
running an ad-hoc query or a stored procedure with variables. Do you get
a 'good' query plan if you use literals instead of variables/parameters?

Gert-Jan

lutz.jah***@nord-com.net wrote:
Show quote

>
> Hallo!
>
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
>
> The optimizer perform cluster index scan instead of using a index seek
> an also using a hash join instead of a loop join.
>
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
>
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
>
> Anyone have an idea?
>
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
>
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
>
> Regards
> Lutz

27 Aug 2005 9:50 AM

Erland Sommarskog

(lutz.jah***@nord-com.net) writes:

> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
>
> The optimizer perform cluster index scan instead of using a index seek
> an also using a hash join instead of a loop join.
>
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
>
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
>
> Anyone have an idea?

In additions to Dan's and Gert-Jan's suggestions, try running UPDATE
STATISTICS on the involved tables. Statistics may be out of date. SQL
Server updates statistics automatically, but there is a lag and if
your search condition includes a column with monotonically growing
values, the optimizer will make incorrect estimates.

For more heavy-duty, you can add WITH FULLSCAN to the UPDATE STATISTICS
statement.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp