bad query executionplan

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 s
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
LutzPlease 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.jahnke@.nord-com.net> wrote in message
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 s
> 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
>|||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.jahnke@.nord-com.net wrote:
> 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 s
> 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|||(lutz.jahnke@.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 s
> 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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp