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:
>> 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
>.
>|||"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...
>> 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
>
>.
>|||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 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
>
>|||"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!|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WWTPZmsEHA.1604@.TK2MSFTNGP15.phx.gbl...
> 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.
The graphical plan shows both stats, too. Hover over a step and you'll see a
line for Row Count, then another line (at the bottom) for Estimated Row
Count.
> Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Sounds great!|||On Wed, 13 Oct 2004 15:02:27 -0700, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>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
The ISNULL() function may be confusing the optimizer.
Or the data.
If there are actually 100k rows in the join with WO.Cmd_No=814352 but
they do have a null or 0 WOTransit.Charge_ref and so are not selected,
then maybe the optimizer doesn't know that. Is there an index on
Charge_ref? I'm not sure what the threshhold is for a scan (so I
guessed 100k), but once the optimizer wants to scan, it would just use
the clustered index, whatever it is. Actually, it's using no index.
I wonder what happens if you recode around the ISNULL?
J.|||Hi every one
Sorry for the delay,
I found that there were statitics (auto created) on table
WOTANSIT
I dropped them and the execution plan went back to normal.
I didn't go further to find a explanation. but for shure i
will never use sp_updatestats again. And i will
uncheck "Auto create statistics".
Does any one can explain why i had to drop these auto
created stats ?
Thanks !!!
>--Original Message--
><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
>
>.
>|||This help but i don't want to rewrite the query.
See my other post and solution on 2004/10/18
thx
>--Original Message--
>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 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
>>
>.
>|||Effectively,
Isnull() was confusing the optimizer.
See my post on 2004/10/18 to see what i had to do to fix
the problem
Ttx
>The ISNULL() function may be confusing the optimizer.
>Or the data.
>If there are actually 100k rows in the join with
WO.Cmd_No=814352 but
>they do have a null or 0 WOTransit.Charge_ref and so are
not selected,
>then maybe the optimizer doesn't know that. Is there an
index on
>Charge_ref? I'm not sure what the threshhold is for a
scan (so I
>guessed 100k), but once the optimizer wants to scan, it
would just use
>the clustered index, whatever it is. Actually, it's
using no index.
>I wonder what happens if you recode around the ISNULL?
>J.
>.
>