I have a fairly simple query

SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
1211370000'

That always does a clustered index scan, instead of using an index that has
keys ABS_END, ABS_Start, RESID. When I put in an index hint, the query
runs much faster, but unfortunately, the above SQL is generated by a 3rd
party application so putting an index hint in there is not a viable option.

I've updated statistics on the ABS_END column as well as the actual
non-clustered index with FULLSCAN and it still chooses to do the clustered
index scan.

Does anyone have any ideas on how to get the optimizer to chose the correct
plan?

Thanks!

Let me add that I am using SQL 2000 SP3 (8.00.818)

"Steven Kong" <sal@ap.org> wrote in message
news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>I have a fairly simple query
>
> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
> 1211370000'
>
> That always does a clustered index scan, instead of using an index that
> has keys ABS_END, ABS_Start, RESID. When I put in an index hint, the
> query runs much faster, but unfortunately, the above SQL is generated by a
> 3rd party application so putting an index hint in there is not a viable
> option.
>
> I've updated statistics on the ABS_END column as well as the actual
> non-clustered index with FULLSCAN and it still chooses to do the clustered
> index scan.
>
> Does anyone have any ideas on how to get the optimizer to chose the
> correct plan?
>
> Thanks!
>

Steven

What if you run instead of SELECT * , but SELECT ABS_END, ABS_START
FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
> 1211370000'








"Steven Kong" <sal@ap.org> wrote in message
news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>I have a fairly simple query
>
> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
> 1211370000'
>
> That always does a clustered index scan, instead of using an index that
> has keys ABS_END, ABS_Start, RESID. When I put in an index hint, the
> query runs much faster, but unfortunately, the above SQL is generated by a
> 3rd party application so putting an index hint in there is not a viable
> option.
>
> I've updated statistics on the ABS_END column as well as the actual
> non-clustered index with FULLSCAN and it still chooses to do the clustered
> index scan.
>
> Does anyone have any ideas on how to get the optimizer to chose the
> correct plan?
>
> Thanks!
>

On May 21, 8:33 am, "Steven Kong" <s...@ap.org> wrote:
> I have a fairly simple query
>
> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
> 1211370000'
>
> That always does a clustered index scan, instead of using an index that has
> keys ABS_END, ABS_Start, RESID. When I put in an index hint, the query
> runs much faster, but unfortunately, the above SQL is generated by a 3rd
> party application so putting an index hint in there is not a viable option.
>
> I've updated statistics on the ABS_END column as well as the actual
> non-clustered index with FULLSCAN and it still chooses to do the clustered
> index scan.
>
> Does anyone have any ideas on how to get the optimizer to chose the correct
> plan?
>
> Thanks!

you can create an index on(ABS_END, ABS_Start, RESID, <all the other
columns>) - it will cover your query

What datatypes do you have in the table for below columns?
ABS_END
ABS_START

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Steven Kong" <sal@ap.org> wrote in message news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>I have a fairly simple query
>
> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < ' 1211370000'
>
> That always does a clustered index scan, instead of using an index that has keys ABS_END,
> ABS_Start, RESID. When I put in an index hint, the query runs much faster, but unfortunately,
> the above SQL is generated by a 3rd party application so putting an index hint in there is not a
> viable option.
>
> I've updated statistics on the ABS_END column as well as the actual non-clustered index with
> FULLSCAN and it still chooses to do the clustered index scan.
>
> Does anyone have any ideas on how to get the optimizer to chose the correct plan?
>
> Thanks!
>

That does choose the correct index, but that would involve getting the
vendor to change the app, plus they may need the results of more than just
those fields.




"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23vrcbo0uIHA.2068@TK2MSFTNGP05.phx.gbl...
> Steven
>
> What if you run instead of SELECT * , but SELECT ABS_END, ABS_START
> FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>> 1211370000'
>
>
>
>
>
>
>
>
> "Steven Kong" <sal@ap.org> wrote in message
> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>I have a fairly simple query
>>
>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>> 1211370000'
>>
>> That always does a clustered index scan, instead of using an index that
>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint, the
>> query runs much faster, but unfortunately, the above SQL is generated by
>> a 3rd party application so putting an index hint in there is not a viable
>> option.
>>
>> I've updated statistics on the ABS_END column as well as the actual
>> non-clustered index with FULLSCAN and it still chooses to do the
>> clustered index scan.
>>
>> Does anyone have any ideas on how to get the optimizer to chose the
>> correct plan?
>>
>> Thanks!
>>
>
>

The Character Types are Char(11) for both


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
> What datatypes do you have in the table for below columns?
> ABS_END
> ABS_START
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Steven Kong" <sal@ap.org> wrote in message
> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>I have a fairly simple query
>>
>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>> 1211370000'
>>
>> That always does a clustered index scan, instead of using an index that
>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint, the
>> query runs much faster, but unfortunately, the above SQL is generated by
>> a 3rd party application so putting an index hint in there is not a viable
>> option.
>>
>> I've updated statistics on the ABS_END column as well as the actual
>> non-clustered index with FULLSCAN and it still chooses to do the
>> clustered index scan.
>>
>> Does anyone have any ideas on how to get the optimizer to chose the
>> correct plan?
>>
>> Thanks!
>>
>
>

Steven
Who told you that live is so easy :-))))

Ih they use SS20005 I'd suggest to look at INCLUDE feature to cover SELECT
statetemnt

CREATE NONCLUSTERED INDEX IX_Blalalalalalalalal

ON dbo.SEVT (ABS_END, ABS_START)

INCLUDE (col1,col2,col3.....);








"Steven Kong" <sal@ap.org> wrote in message
news:OCkpau0uIHA.3484@TK2MSFTNGP06.phx.gbl...
> That does choose the correct index, but that would involve getting the
> vendor to change the app, plus they may need the results of more than just
> those fields.
>
>
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:%23vrcbo0uIHA.2068@TK2MSFTNGP05.phx.gbl...
>> Steven
>>
>> What if you run instead of SELECT * , but SELECT ABS_END, ABS_START
>> FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>> 1211370000'
>>
>>
>>
>>
>>
>>
>>
>>
>> "Steven Kong" <sal@ap.org> wrote in message
>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>I have a fairly simple query
>>>
>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>> 1211370000'
>>>
>>> That always does a clustered index scan, instead of using an index that
>>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint, the
>>> query runs much faster, but unfortunately, the above SQL is generated by
>>> a 3rd party application so putting an index hint in there is not a
>>> viable option.
>>>
>>> I've updated statistics on the ABS_END column as well as the actual
>>> non-clustered index with FULLSCAN and it still chooses to do the
>>> clustered index scan.
>>>
>>> Does anyone have any ideas on how to get the optimizer to chose the
>>> correct plan?
>>>
>>> Thanks!
>>>
>>
>>
>
>

Sorry I mean
Who told you that Life is so easy :-))))


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uoCk2x0uIHA.3968@TK2MSFTNGP04.phx.gbl...
> Steven
> Who told you that live is so easy :-))))
>
> Ih they use SS20005 I'd suggest to look at INCLUDE feature to cover SELECT
> statetemnt
>
> CREATE NONCLUSTERED INDEX IX_Blalalalalalalalal
>
> ON dbo.SEVT (ABS_END, ABS_START)
>
> INCLUDE (col1,col2,col3.....);
>
>
>
>
>
>
>
>
> "Steven Kong" <sal@ap.org> wrote in message
> news:OCkpau0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>> That does choose the correct index, but that would involve getting the
>> vendor to change the app, plus they may need the results of more than
>> just those fields.
>>
>>
>>
>>
>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>> news:%23vrcbo0uIHA.2068@TK2MSFTNGP05.phx.gbl...
>>> Steven
>>>
>>> What if you run instead of SELECT * , but SELECT ABS_END, ABS_START
>>> FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>>> 1211370000'
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Steven Kong" <sal@ap.org> wrote in message
>>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>>I have a fairly simple query
>>>>
>>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>>> 1211370000'
>>>>
>>>> That always does a clustered index scan, instead of using an index that
>>>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint,
>>>> the query runs much faster, but unfortunately, the above SQL is
>>>> generated by a 3rd party application so putting an index hint in there
>>>> is not a viable option.
>>>>
>>>> I've updated statistics on the ABS_END column as well as the actual
>>>> non-clustered index with FULLSCAN and it still chooses to do the
>>>> clustered index scan.
>>>>
>>>> Does anyone have any ideas on how to get the optimizer to chose the
>>>> correct plan?
>>>>
>>>> Thanks!
>>>>
>>>
>>>
>>
>>
>
>

Actually, I restored the DB to SQL 2005 and it chooses the correct index.
We are definitely going to upgrade but it wasn't planned for a few more
months... and then this happened. I will probably test it with SP4


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:O1tSp00uIHA.3792@TK2MSFTNGP02.phx.gbl...
> Sorry I mean
> Who told you that Life is so easy :-))))
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:uoCk2x0uIHA.3968@TK2MSFTNGP04.phx.gbl...
>> Steven
>> Who told you that live is so easy :-))))
>>
>> Ih they use SS20005 I'd suggest to look at INCLUDE feature to cover
>> SELECT statetemnt
>>
>> CREATE NONCLUSTERED INDEX IX_Blalalalalalalalal
>>
>> ON dbo.SEVT (ABS_END, ABS_START)
>>
>> INCLUDE (col1,col2,col3.....);
>>
>>
>>
>>
>>
>>
>>
>>
>> "Steven Kong" <sal@ap.org> wrote in message
>> news:OCkpau0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>> That does choose the correct index, but that would involve getting the
>>> vendor to change the app, plus they may need the results of more than
>>> just those fields.
>>>
>>>
>>>
>>>
>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>> news:%23vrcbo0uIHA.2068@TK2MSFTNGP05.phx.gbl...
>>>> Steven
>>>>
>>>> What if you run instead of SELECT * , but SELECT ABS_END, ABS_START
>>>> FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>>>> 1211370000'
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Steven Kong" <sal@ap.org> wrote in message
>>>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>>>I have a fairly simple query
>>>>>
>>>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>>>> 1211370000'
>>>>>
>>>>> That always does a clustered index scan, instead of using an index
>>>>> that has keys ABS_END, ABS_Start, RESID. When I put in an index
>>>>> hint, the query runs much faster, but unfortunately, the above SQL is
>>>>> generated by a 3rd party application so putting an index hint in there
>>>>> is not a viable option.
>>>>>
>>>>> I've updated statistics on the ABS_END column as well as the actual
>>>>> non-clustered index with FULLSCAN and it still chooses to do the
>>>>> clustered index scan.
>>>>>
>>>>> Does anyone have any ideas on how to get the optimizer to chose the
>>>>> correct plan?
>>>>>
>>>>> Thanks!
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

OK. You have two conditions:

ABS_END >= ' 1211369400'
AND
ABS_START < ' 1211370000'

Can you say how many rows qualify for each condition? Also, how many rows in the table?

As for your current index:
You say you have an index on (ABS_END, ABS_Start, RESID). does that reflect the column order in the
index? If so, that index can be used to seek for the rows that qualifies for below condition:
ABS_END >= ' 1211369400'

But you don't have an index to with the other condition. If the above condition isn't
restrictive enough, then it is possible that SQL Server won't use that index. Consider creating an
index on the ABS_START column (with that column as the first column in the index). Perhaps SQL
server will use both indexes, intersect them assuming the intersection of the conditions are
restrictive enough.


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Steven Kong" <sal@ap.org> wrote in message news:%23UZ9Jw0uIHA.4772@TK2MSFTNGP03.phx.gbl...
> The Character Types are Char(11) for both
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
>> What datatypes do you have in the table for below columns?
>> ABS_END
>> ABS_START
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Steven Kong" <sal@ap.org> wrote in message news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>I have a fairly simple query
>>>
>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < ' 1211370000'
>>>
>>> That always does a clustered index scan, instead of using an index that has keys ABS_END,
>>> ABS_Start, RESID. When I put in an index hint, the query runs much faster, but
>>> unfortunately, the above SQL is generated by a 3rd party application so putting an index hint in
>>> there is not a viable option.
>>>
>>> I've updated statistics on the ABS_END column as well as the actual non-clustered index with
>>> FULLSCAN and it still chooses to do the clustered index scan.
>>>
>>> Does anyone have any ideas on how to get the optimizer to chose the correct plan?
>>>
>>> Thanks!
>>>
>>
>>
>
>

The whole query only returns 41 rows. The first condition has 1041 rows.
THe second condition has 7637664 rows.

The columns in the index are stated in the correct order.

But regarding the index. Since this an AND clause, wouldn't it just use
the first index to determine it has 1041 rows, and then see which of those
1041 would qualify for the second condition. And since ABS_Start is the 2nd
column in the index, it wouldn't even need to scan all 1041 rows? And then
it would use a bookmark lookup to return the data?

By the way there are other indexes on this table, but not on the ABS_START
column. - don't know if that's important or not.

Thanks for the !
STeven

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:eiteaB1uIHA.1328@TK2MSFTNGP03.phx.gbl...
> OK. You have two conditions:
>
> ABS_END >= ' 1211369400'
> AND
> ABS_START < ' 1211370000'
>
> Can you say how many rows qualify for each condition? Also, how many rows
> in the table?
>
> As for your current index:
> You say you have an index on (ABS_END, ABS_Start, RESID). does that
> reflect the column order in the index? If so, that index can be used to
> seek for the rows that qualifies for below condition:
> ABS_END >= ' 1211369400'
>
> But you don't have an index to with the other condition. If the above
> condition isn't restrictive enough, then it is possible that SQL Server
> won't use that index. Consider creating an index on the ABS_START column
> (with that column as the first column in the index). Perhaps SQL server
> will use both indexes, intersect them assuming the intersection of the
> conditions are restrictive enough.
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Steven Kong" <sal@ap.org> wrote in message
> news:%23UZ9Jw0uIHA.4772@TK2MSFTNGP03.phx.gbl...
>> The Character Types are Char(11) for both
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>> in message news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
>>> What datatypes do you have in the table for below columns?
>>> ABS_END
>>> ABS_START
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Steven Kong" <sal@ap.org> wrote in message
>>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>>I have a fairly simple query
>>>>
>>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>>> 1211370000'
>>>>
>>>> That always does a clustered index scan, instead of using an index that
>>>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint,
>>>> the query runs much faster, but unfortunately, the above SQL is
>>>> generated by a 3rd party application so putting an index hint in there
>>>> is not a viable option.
>>>>
>>>> I've updated statistics on the ABS_END column as well as the actual
>>>> non-clustered index with FULLSCAN and it still chooses to do the
>>>> clustered index scan.
>>>>
>>>> Does anyone have any ideas on how to get the optimizer to chose the
>>>> correct plan?
>>>>
>>>> Thanks!
>>>>
>>>
>>>
>>
>>
>
>

Also, I just restored on SQL 2005 and it uses the correct index. And on an
older backup of the database, which has 7.7 million rows in the table as
opposed to 8.4 million rows, it also chooses the correct query plan.

Argh!

Thanks for your

Steven

P.S. the previous post was also by me, I was using a different account
"Sam" <Steve.sam@hotmail.com> wrote in message
news:uTxLON1uIHA.1220@TK2MSFTNGP04.phx.gbl...
> The whole query only returns 41 rows. The first condition has 1041 rows.
> THe second condition has 7637664 rows.
>
> The columns in the index are stated in the correct order.
>
> But regarding the index. Since this an AND clause, wouldn't it just use
> the first index to determine it has 1041 rows, and then see which of those
> 1041 would qualify for the second condition. And since ABS_Start is the
> 2nd column in the index, it wouldn't even need to scan all 1041 rows? And
> then it would use a bookmark lookup to return the data?
>
> By the way there are other indexes on this table, but not on the ABS_START
> column. - don't know if that's important or not.
>
> Thanks for the !
> STeven
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
> in message news:eiteaB1uIHA.1328@TK2MSFTNGP03.phx.gbl...
>> OK. You have two conditions:
>>
>> ABS_END >= ' 1211369400'
>> AND
>> ABS_START < ' 1211370000'
>>
>> Can you say how many rows qualify for each condition? Also, how many rows
>> in the table?
>>
>> As for your current index:
>> You say you have an index on (ABS_END, ABS_Start, RESID). does that
>> reflect the column order in the index? If so, that index can be used to
>> seek for the rows that qualifies for below condition:
>> ABS_END >= ' 1211369400'
>>
>> But you don't have an index to with the other condition. If the
>> above condition isn't restrictive enough, then it is possible that SQL
>> Server won't use that index. Consider creating an index on the ABS_START
>> column (with that column as the first column in the index). Perhaps SQL
>> server will use both indexes, intersect them assuming the intersection of
>> the conditions are restrictive enough.
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Steven Kong" <sal@ap.org> wrote in message
>> news:%23UZ9Jw0uIHA.4772@TK2MSFTNGP03.phx.gbl...
>>> The Character Types are Char(11) for both
>>>
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>>> in message news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
>>>> What datatypes do you have in the table for below columns?
>>>> ABS_END
>>>> ABS_START
>>>>
>>>> --
>>>> Tibor Karaszi, SQL Server MVP
>>>> http://www.karaszi.com/sqlserver/default.asp
>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>
>>>>
>>>> "Steven Kong" <sal@ap.org> wrote in message
>>>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>>>I have a fairly simple query
>>>>>
>>>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
>>>>> 1211370000'
>>>>>
>>>>> That always does a clustered index scan, instead of using an index
>>>>> that has keys ABS_END, ABS_Start, RESID. When I put in an index
>>>>> hint, the query runs much faster, but unfortunately, the above SQL is
>>>>> generated by a 3rd party application so putting an index hint in there
>>>>> is not a viable option.
>>>>>
>>>>> I've updated statistics on the ABS_END column as well as the actual
>>>>> non-clustered index with FULLSCAN and it still chooses to do the
>>>>> clustered index scan.
>>>>>
>>>>> Does anyone have any ideas on how to get the optimizer to chose the
>>>>> correct plan?
>>>>>
>>>>> Thanks!
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

- How are you testing it?
- Are you cleaning the proc cache before executing the statement, in order
to avoid reusing a previous plan? -- DO NOT DO THIS ON A PROD SERVER
- Can you post the result of statistics io for both plans?
- Can you post the estructure of the table, including constraints and indexes?

AMB

"Steven Kong" wrote:

> Let me add that I am using SQL 2000 SP3 (8.00.818)
>
> "Steven Kong" <sal@ap.org> wrote in message
> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
> >I have a fairly simple query
> >
> > SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
> > 1211370000'
> >
> > That always does a clustered index scan, instead of using an index that
> > has keys ABS_END, ABS_Start, RESID. When I put in an index hint, the
> > query runs much faster, but unfortunately, the above SQL is generated by a
> > 3rd party application so putting an index hint in there is not a viable
> > option.
> >
> > I've updated statistics on the ABS_END column as well as the actual
> > non-clustered index with FULLSCAN and it still chooses to do the clustered
> > index scan.
> >
> > Does anyone have any ideas on how to get the optimizer to chose the
> > correct plan?
> >
> > Thanks!
> >
>
>
>

Sam,

SQL Server optimizes for the worst case situation, based on estimations.
The best estimate the optimizer has, is that the query will return some
1000 rows (the lower value of 1041 and 7637664). If this was the actual
number of rows returned, then in a cold cache situation, scanning the
clustered index might easily be the best choice.

However, if you have a large part of the table in the Buffer Cache (a
hot cache), and/or if the actual row count is significantly lower, then
things are different. I assume the table has some 8 million rows. If the
table data is entirely in cache, then partially scanning a narrow index
and looking up 0.01% rows outperforms a clustered index scan in orders
of magnitude.

Unfortunately, the buffer cache hit ratio is not taken into
consideration when determining the execution plan. Also, there are no
query hints to achieve such an effect.

Have you tried updating the statistics WITH FULLSCAN?

If you do not have the luxury to add an index hint to the query, or to
remove columns from the Selection List that are not needed, then you can
only tinker with the indexes.

One way to do this (as suggested by Tibor and Alex) is to add a covering
nonclustered index, where the first column can be used for the most
restrictive predicate. So in this case on ABS_END, followed by all other
columns of the table.

Another way is to change the clustered index to ABS_END, or some unique
combination of columns starting with ABS_END.

Note that both approaches can have negative performance effects on
Inserts/Updates/Deletes and can increase blocking.

HTH,
Gert-Jan
SQL Server MVP


Sam wrote:
>
> The whole query only returns 41 rows. The first condition has 1041 rows.
> THe second condition has 7637664 rows.
>
> The columns in the index are stated in the correct order.
>
> But regarding the index. Since this an AND clause, wouldn't it just use
> the first index to determine it has 1041 rows, and then see which of those
> 1041 would qualify for the second condition. And since ABS_Start is the 2nd
> column in the index, it wouldn't even need to scan all 1041 rows? And then
> it would use a bookmark lookup to return the data?
>
> By the way there are other indexes on this table, but not on the ABS_START
> column. - don't know if that's important or not.
>
> Thanks for the !
> STeven
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
> message news:eiteaB1uIHA.1328@TK2MSFTNGP03.phx.gbl...
> > OK. You have two conditions:
> >
> > ABS_END >= ' 1211369400'
> > AND
> > ABS_START < ' 1211370000'
> >
> > Can you say how many rows qualify for each condition? Also, how many rows
> > in the table?
> >
> > As for your current index:
> > You say you have an index on (ABS_END, ABS_Start, RESID). does that
> > reflect the column order in the index? If so, that index can be used to
> > seek for the rows that qualifies for below condition:
> > ABS_END >= ' 1211369400'
> >
> > But you don't have an index to with the other condition. If the above
> > condition isn't restrictive enough, then it is possible that SQL Server
> > won't use that index. Consider creating an index on the ABS_START column
> > (with that column as the first column in the index). Perhaps SQL server
> > will use both indexes, intersect them assuming the intersection of the
> > conditions are restrictive enough.
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Steven Kong" <sal@ap.org> wrote in message
> > news:%23UZ9Jw0uIHA.4772@TK2MSFTNGP03.phx.gbl...
> >> The Character Types are Char(11) for both
> >>
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
> >> in message news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
> >>> What datatypes do you have in the table for below columns?
> >>> ABS_END
> >>> ABS_START
> >>>
> >>> --
> >>> Tibor Karaszi, SQL Server MVP
> >>> http://www.karaszi.com/sqlserver/default.asp
> >>> http://sqlblog.com/blogs/tibor_karaszi
> >>>
> >>>
> >>> "Steven Kong" <sal@ap.org> wrote in message
> >>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
> >>>>I have a fairly simple query
> >>>>
> >>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START < '
> >>>> 1211370000'
> >>>>
> >>>> That always does a clustered index scan, instead of using an index that
> >>>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint,
> >>>> the query runs much faster, but unfortunately, the above SQL is
> >>>> generated by a 3rd party application so putting an index hint in there
> >>>> is not a viable option.
> >>>>
> >>>> I've updated statistics on the ABS_END column as well as the actual
> >>>> non-clustered index with FULLSCAN and it still chooses to do the
> >>>> clustered index scan.
> >>>>
> >>>> Does anyone have any ideas on how to get the optimizer to chose the
> >>>> correct plan?
> >>>>
> >>>> Thanks!
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >

Thanks Gert-Jan,

The query actually returns about 41 rows only. I have tried updating stats
with FULLSCAN on the ABS_END column, ABS_START column and the actual non
clustered index that has these columns - all to no avail. I will try to run
a FULLSCAN for all columns and indexes tonight, but I think that will take a
VERY long time. In addition, this is a 24/7 database and the performance hit
while running may also be unacceptable. But I guess I have no choice at
this point.



"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
news:48347F91.EBC80CB2@toomuchspamalready.nl...
> Sam,
>
> SQL Server optimizes for the worst case situation, based on estimations.
> The best estimate the optimizer has, is that the query will return some
> 1000 rows (the lower value of 1041 and 7637664). If this was the actual
> number of rows returned, then in a cold cache situation, scanning the
> clustered index might easily be the best choice.
>
> However, if you have a large part of the table in the Buffer Cache (a
> hot cache), and/or if the actual row count is significantly lower, then
> things are different. I assume the table has some 8 million rows. If the
> table data is entirely in cache, then partially scanning a narrow index
> and looking up 0.01% rows outperforms a clustered index scan in orders
> of magnitude.
>
> Unfortunately, the buffer cache hit ratio is not taken into
> consideration when determining the execution plan. Also, there are no
> query hints to achieve such an effect.
>
> Have you tried updating the statistics WITH FULLSCAN?
>
> If you do not have the luxury to add an index hint to the query, or to
> remove columns from the Selection List that are not needed, then you can
> only tinker with the indexes.
>
> One way to do this (as suggested by Tibor and Alex) is to add a covering
> nonclustered index, where the first column can be used for the most
> restrictive predicate. So in this case on ABS_END, followed by all other
> columns of the table.
>
> Another way is to change the clustered index to ABS_END, or some unique
> combination of columns starting with ABS_END.
>
> Note that both approaches can have negative performance effects on
> Inserts/Updates/Deletes and can increase blocking.
>
> HTH,
> Gert-Jan
> SQL Server MVP
>
>
> Sam wrote:
>>
>> The whole query only returns 41 rows. The first condition has 1041
>> rows.
>> THe second condition has 7637664 rows.
>>
>> The columns in the index are stated in the correct order.
>>
>> But regarding the index. Since this an AND clause, wouldn't it just use
>> the first index to determine it has 1041 rows, and then see which of
>> those
>> 1041 would qualify for the second condition. And since ABS_Start is the
>> 2nd
>> column in the index, it wouldn't even need to scan all 1041 rows? And
>> then
>> it would use a bookmark lookup to return the data?
>>
>> By the way there are other indexes on this table, but not on the
>> ABS_START
>> column. - don't know if that's important or not.
>>
>> Thanks for the !
>> STeven
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>> in
>> message news:eiteaB1uIHA.1328@TK2MSFTNGP03.phx.gbl...
>> > OK. You have two conditions:
>> >
>> > ABS_END >= ' 1211369400'
>> > AND
>> > ABS_START < ' 1211370000'
>> >
>> > Can you say how many rows qualify for each condition? Also, how many
>> > rows
>> > in the table?
>> >
>> > As for your current index:
>> > You say you have an index on (ABS_END, ABS_Start, RESID). does that
>> > reflect the column order in the index? If so, that index can be used to
>> > seek for the rows that qualifies for below condition:
>> > ABS_END >= ' 1211369400'
>> >
>> > But you don't have an index to with the other condition. If the
>> > above
>> > condition isn't restrictive enough, then it is possible that SQL Server
>> > won't use that index. Consider creating an index on the ABS_START
>> > column
>> > (with that column as the first column in the index). Perhaps SQL server
>> > will use both indexes, intersect them assuming the intersection of the
>> > conditions are restrictive enough.
>> >
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Steven Kong" <sal@ap.org> wrote in message
>> > news:%23UZ9Jw0uIHA.4772@TK2MSFTNGP03.phx.gbl...
>> >> The Character Types are Char(11) for both
>> >>
>> >>
>> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m>
>> >> wrote
>> >> in message news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
>> >>> What datatypes do you have in the table for below columns?
>> >>> ABS_END
>> >>> ABS_START
>> >>>
>> >>> --
>> >>> Tibor Karaszi, SQL Server MVP
>> >>> http://www.karaszi.com/sqlserver/default.asp
>> >>> http://sqlblog.com/blogs/tibor_karaszi
>> >>>
>> >>>
>> >>> "Steven Kong" <sal@ap.org> wrote in message
>> >>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>> >>>>I have a fairly simple query
>> >>>>
>> >>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START <
>> >>>> '
>> >>>> 1211370000'
>> >>>>
>> >>>> That always does a clustered index scan, instead of using an index
>> >>>> that
>> >>>> has keys ABS_END, ABS_Start, RESID. When I put in an index hint,
>> >>>> the query runs much faster, but unfortunately, the above SQL is
>> >>>> generated by a 3rd party application so putting an index hint in
>> >>>> there
>> >>>> is not a viable option.
>> >>>>
>> >>>> I've updated statistics on the ABS_END column as well as the actual
>> >>>> non-clustered index with FULLSCAN and it still chooses to do the
>> >>>> clustered index scan.
>> >>>>
>> >>>> Does anyone have any ideas on how to get the optimizer to chose the
>> >>>> correct plan?
>> >>>>
>> >>>> Thanks!
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >

This time, I chose just the ABS_END, ABS_START and one more column and it
still chose the Clustered Index, rather than the non clustered index that
consists of ABS_END, ABS_START.
I did an update statistics with FULLSCAN on all 3 columns.

This has got to be a bug, no?

"SKOng" <Sykong@hotmail.com> wrote in message
news:eCamxg4uIHA.3968@TK2MSFTNGP04.phx.gbl...
> Thanks Gert-Jan,
>
> The query actually returns about 41 rows only. I have tried updating
> stats with FULLSCAN on the ABS_END column, ABS_START column and the actual
> non clustered index that has these columns - all to no avail. I will try
> to run a FULLSCAN for all columns and indexes tonight, but I think that
> will take a VERY long time. In addition, this is a 24/7 database and the
> performance hit while running may also be unacceptable. But I guess I
> have no choice at this point.
>
>
>
> "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
> news:48347F91.EBC80CB2@toomuchspamalready.nl...
>> Sam,
>>
>> SQL Server optimizes for the worst case situation, based on estimations.
>> The best estimate the optimizer has, is that the query will return some
>> 1000 rows (the lower value of 1041 and 7637664). If this was the actual
>> number of rows returned, then in a cold cache situation, scanning the
>> clustered index might easily be the best choice.
>>
>> However, if you have a large part of the table in the Buffer Cache (a
>> hot cache), and/or if the actual row count is significantly lower, then
>> things are different. I assume the table has some 8 million rows. If the
>> table data is entirely in cache, then partially scanning a narrow index
>> and looking up 0.01% rows outperforms a clustered index scan in orders
>> of magnitude.
>>
>> Unfortunately, the buffer cache hit ratio is not taken into
>> consideration when determining the execution plan. Also, there are no
>> query hints to achieve such an effect.
>>
>> Have you tried updating the statistics WITH FULLSCAN?
>>
>> If you do not have the luxury to add an index hint to the query, or to
>> remove columns from the Selection List that are not needed, then you can
>> only tinker with the indexes.
>>
>> One way to do this (as suggested by Tibor and Alex) is to add a covering
>> nonclustered index, where the first column can be used for the most
>> restrictive predicate. So in this case on ABS_END, followed by all other
>> columns of the table.
>>
>> Another way is to change the clustered index to ABS_END, or some unique
>> combination of columns starting with ABS_END.
>>
>> Note that both approaches can have negative performance effects on
>> Inserts/Updates/Deletes and can increase blocking.
>>
>> HTH,
>> Gert-Jan
>> SQL Server MVP
>>
>>
>> Sam wrote:
>>>
>>> The whole query only returns 41 rows. The first condition has 1041
>>> rows.
>>> THe second condition has 7637664 rows.
>>>
>>> The columns in the index are stated in the correct order.
>>>
>>> But regarding the index. Since this an AND clause, wouldn't it just
>>> use
>>> the first index to determine it has 1041 rows, and then see which of
>>> those
>>> 1041 would qualify for the second condition. And since ABS_Start is the
>>> 2nd
>>> column in the index, it wouldn't even need to scan all 1041 rows? And
>>> then
>>> it would use a bookmark lookup to return the data?
>>>
>>> By the way there are other indexes on this table, but not on the
>>> ABS_START
>>> column. - don't know if that's important or not.
>>>
>>> Thanks for the !
>>> STeven
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>>> in
>>> message news:eiteaB1uIHA.1328@TK2MSFTNGP03.phx.gbl...
>>> > OK. You have two conditions:
>>> >
>>> > ABS_END >= ' 1211369400'
>>> > AND
>>> > ABS_START < ' 1211370000'
>>> >
>>> > Can you say how many rows qualify for each condition? Also, how many
>>> > rows
>>> > in the table?
>>> >
>>> > As for your current index:
>>> > You say you have an index on (ABS_END, ABS_Start, RESID). does that
>>> > reflect the column order in the index? If so, that index can be used
>>> > to
>>> > seek for the rows that qualifies for below condition:
>>> > ABS_END >= ' 1211369400'
>>> >
>>> > But you don't have an index to with the other condition. If the
>>> > above
>>> > condition isn't restrictive enough, then it is possible that SQL
>>> > Server
>>> > won't use that index. Consider creating an index on the ABS_START
>>> > column
>>> > (with that column as the first column in the index). Perhaps SQL
>>> > server
>>> > will use both indexes, intersect them assuming the intersection of the
>>> > conditions are restrictive enough.
>>> >
>>> >
>>> > --
>>> > Tibor Karaszi, SQL Server MVP
>>> > http://www.karaszi.com/sqlserver/default.asp
>>> > http://sqlblog.com/blogs/tibor_karaszi
>>> >
>>> >
>>> > "Steven Kong" <sal@ap.org> wrote in message
>>> > news:%23UZ9Jw0uIHA.4772@TK2MSFTNGP03.phx.gbl...
>>> >> The Character Types are Char(11) for both
>>> >>
>>> >>
>>> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m>
>>> >> wrote
>>> >> in message news:uw4LEt0uIHA.5288@TK2MSFTNGP06.phx.gbl...
>>> >>> What datatypes do you have in the table for below columns?
>>> >>> ABS_END
>>> >>> ABS_START
>>> >>>
>>> >>> --
>>> >>> Tibor Karaszi, SQL Server MVP
>>> >>> http://www.karaszi.com/sqlserver/default.asp
>>> >>> http://sqlblog.com/blogs/tibor_karaszi
>>> >>>
>>> >>>
>>> >>> "Steven Kong" <sal@ap.org> wrote in message
>>> >>> news:euGBCd0uIHA.3484@TK2MSFTNGP06.phx.gbl...
>>> >>>>I have a fairly simple query
>>> >>>>
>>> >>>> SELECT * FROM SEVT WHERE ABS_END >= ' 1211369400' AND ABS_START <
>>> >>>> '
>>> >>>> 1211370000'
>>> >>>>
>>> >>>> That always does a clustered index scan, instead of using an index
>>> >>>> that
>>> >>>> has keys ABS_END, ABS_Start, RESID. When I put in an index
>>> >>>> hint,
>>> >>>> the query runs much faster, but unfortunately, the above SQL is
>>> >>>> generated by a 3rd party application so putting an index hint in
>>> >>>> there
>>> >>>> is not a viable option.
>>> >>>>
>>> >>>> I've updated statistics on the ABS_END column as well as the actual
>>> >>>> non-clustered index with FULLSCAN and it still chooses to do the
>>> >>>> clustered index scan.
>>> >>>>
>>> >>>> Does anyone have any ideas on how to get the optimizer to chose the
>>> >>>> correct plan?
>>> >>>>
>>> >>>> Thanks!
>>> >>>>
>>> >>>
>>> >>>
>>> >>
>>> >>
>>> >
>>> >
>
>

On May 21, 7:16 pm, "SKOng" <Syk...@hotmail.com> wrote:
> This time, I chose just the ABS_END, ABS_START and one more column and it
> still chose the Clustered Index, rather than the non clustered index that
> consists of ABS_END, ABS_START.
> I did an update statistics with FULLSCAN on all 3 columns.
>
> This has got to be a bug, no?
>
> "SKOng" <Syk...@hotmail.com> wrote in message
>
> news:eCamxg4uIHA.3968@TK2MSFTNGP04.phx.gbl...
>
> > Thanks Gert-Jan,
>
> > The query actually returns about 41 rows only. I have tried updating
> > stats with FULLSCAN on the ABS_END column, ABS_START column and the actual
> > non clustered index that has these columns - all to no avail. I will try
> > to run a FULLSCAN for all columns and indexes tonight, but I think that
> > will take a VERY long time. In addition, this is a 24/7 database and the
> > performance hit while running may also be unacceptable. But I guess I
> > have no choice at this point.
>
> > "Gert-Jan Strik" <so...@toomuchspamalready.nl> wrote in message
> >news:48347F91.EBC80CB2@toomuchspamalready.nl...
> >> Sam,
>
> >> SQL Server optimizes for the worst case situation, based on estimations.
> >> The best estimate the optimizer has, is that the query will return some
> >> 1000 rows (the lower value of 1041 and 7637664). If this was the actual
> >> number of rows returned, then in a cold cache situation, scanning the
> >> clustered index might easily be the best choice.
>
> >> However, if you have a large part of the table in the Buffer Cache (a
> >> hot cache), and/or if the actual row count is significantly lower, then
> >> things are different. I assume the table has some 8 million rows. If the
> >> table data is entirely in cache, then partially scanning a narrow index
> >> and looking up 0.01% rows outperforms a clustered index scan in orders
> >> of magnitude.
>
> >> Unfortunately, the buffer cache hit ratio is not taken into
> >> consideration when determining the execution plan. Also, there are no
> >> query hints to achieve such an effect.
>
> >> Have you tried updating the statistics WITH FULLSCAN?
>
> >> If you do not have the luxury to add an index hint to the query, or to
> >> remove columns from the Selection List that are not needed, then you can
> >> only tinker with the indexes.
>
> >> One way to do this (as suggested by Tibor and Alex) is to add a covering
> >> nonclustered index, where the first column can be used fo