Re: Optimizer not using the good index

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Peter Yeoh (nospam_at_nospam.com)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 11:16:43 +0800

As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.

To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot. 

-- 
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files?  Use MiniSQLBackup Lite, free!
"Don" <anonymous@discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@phx.gbl...
> Please,
>
> Using SQL 7.0
>
> The optimizer is not using the good index for this query
>
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
>
> I did reindex but it's still the same.   It looks to
> happen every monday after i run sp_updatestats
>
> What should i do or i should not do
>
> SELECT 0
> FROM WOTransit (readuncommitted)
>      INNER JOIN WO (readuncommitted)  On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE  WO.Cmd_No=814352
> AND    ISNULL(WOTransit.Charge_ref,0) <> 0
>
> thanks
>
> Don

Relevant Pages

  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: Planning multiple queries
    ... > so it seems to me a new plan should be prepared for the second query, ... If you submit them all in one batch, SQL Server generates a query plan ... If no statistics get updated as result of the query, ...
    (comp.databases.ms-sqlserver)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: WHERE clause question
    ... > no reason that select * with where should run slower. ... There are two parts to executing a query. ... SQL Server can start spooling rows as soon as it finds matches and has the ... if the statistics is outdated, ...
    (microsoft.public.sqlserver.programming)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... What you said was what I first planned to say, but then I read the Books Online which implies that the batch is indeed reused, so I held back from saying that. ... the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ...
    (microsoft.public.sqlserver.tools)