Optimizer not using the good index

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
DonDon wrote:
> 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
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
David Gugick
Imceda Software
www.imceda.com|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don

>--Original Message--
>Don wrote:
of[vbcol=seagreen]
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>|||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|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:

> 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 bette
r,
> 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...
>
>|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> 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.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!sql