SQLSever not using index

[talfano@ncpsolutions.com]

Hello,

We are having a very strange problem. We have a table with about 5
million rows in it. The problem is with one of the non clustered
indexes. I have noticed that sometimes in query analyzer, when doing
an execution plan, the optimizer is NOT doing an index seek, or a
bookmark lookup when the query should. It sometimes will do a full
clustered index scan on the primary key, which takes much longer. For
example:

select * from MyTable where fk_value = 1001201

the optimizer WILL NOT do an index seek or bookmark lookup and for this
query:

select * from MyTable where fk_value = 1001222

it WILL do an index seek on the non clustered index. The only
difference is the values specified for fk_value.

I have done a update statistics MyTable with full scan and it still
will not use the non clustered index for some queries. I have also
tried:

select * from MyTable (INDEX=IX_FK_VA LUE) where fk_value = 1001201

to force the optimizer to use this index, but it still DOES NOT use
this index. Its wierd because for some values it will use the index,
and some it will not. Not sure what is going on

Any help would be greatly appreciated.

TIA

[Stu]

Re: SQLSever not using index

Are there any other tables in your query that you have removed for
simplicity in posting? It may have something to do with the amount of
data that matches from the other table. Just shooting in the dark.

One thing you should do, however, is get rid of the SELECT *. You'll
have better luck at hitting a covering index if you limit your query to
only the data that you need to return.

It's a start, anyway. :)

Stu

[Mark D Powell]

Re: SQLSever not using index

talf, have you tried looking at the statistics using DBCC SHOWCONTIG
and SHOW_STATISTICS ? Perhaps they are incorrect or stale and require
updating? By any change does the non-index using version of the plan
show that the query is being ran parallell? (Though the index hint
should have disabled that).

HTH -- Mark D Powell --

[talfano@ncpsolutions.com]

Re: SQLSever not using index

just did a show statstics and 1001201 has:

RANGE ROWS: 159514.0
EQ ROWS: 154588.0
DISTINCT RANGE ROWS: 1
AVG RANGE ROWS: 159514.0

and 1001222, the one that works has:

RANGE ROWS: 7173.0
EQ ROWS: 10589.0
DISTINCT RANGE ROWS: 9
AVG RANGE ROWS: 797.0

I also noticed that this is happening on our test server as well. I
found an fk_value with a large number of rows and did an execution
plan, and it is using the clustered scan, not a bookmark or seek w/ the
correct index.

[talfano@ncpsolutions.com]

Re: SQLSever not using index

No. The query is that simple.

I don't understand why the column list in the select would make a
difference on which index is used. It seems like the where clause
would be the determining factor. At any rate, I tried your suggestion
and this is what I found:

When I select only the PK of the table ie:

select id from cps_common_draf t where fk_value = 1001201

it DOES do an index seek on the non clustered index. However, if I add
one of the address fields, it goes back to a full clustered scan:

select id, address_1 from cps_common_draf t where fk_value = 1001201

Hmmm

[talfano@ncpsolutions.com]

Re: SQLSever not using index

No. The query is that simple.

I don't understand why the column list in the select would make a
difference on which index is used. It seems like the where clause
would be the determining factor. At any rate, I tried your suggestion
and this is what I found:

When I select only the PK of the table ie:

select id from cps_common_draf t where fk_value = 1001201

it DOES do an index seek on the non clustered index. However, if I add
one of the address fields, it goes back to a full clustered scan:

select id, address_1 from cps_common_draf t where fk_value = 1001201

Hmmm

[Erland Sommarskog]

Re: SQLSever not using index

(talfano@ncpsol utions.com) writes:[color=blue]
> I don't understand why the column list in the select would make a
> difference on which index is used. It seems like the where clause
> would be the determining factor. At any rate, I tried your suggestion
> and this is what I found:
>
> When I select only the PK of the table ie:
>
> select id from cps_common_draf t where fk_value = 1001201
>
> it DOES do an index seek on the non clustered index. However, if I add
> one of the address fields, it goes back to a full clustered scan:
>
> select id, address_1 from cps_common_draf t where fk_value = 1001201[/color]

Yes, this is expected. When you do "SELECT id", the query can
be evaluated from the index alone. No need to access the data pages.
I assume here that id is in the clustered index, and recall that
non-clustered indexes as row locatotr uses the keys of the clustered
index.

When you add a column which is not in the index, the index seek must be
combined with a bookmark lookup. Now, for a narrow selection, index
seek + bookmark lookup is a good thing. But for a wide selection,
this can be a disaster.

The FK value you have problems with appear 159514 times according
to the statistics in your other posting. This means 159514 bookmark
lookups, or 159514 pages access. Your table has five million rows.
Permit me to assume a row size of 50: 50 * 5000000 / 8192 = 30518
pages to scan the table, if it's completely unfragmented. As you
can see, the clustered index scan is a lot cheaper in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp