|
|
Posted by Erland Sommarskog on 06/17/05 01:03
(talfano@ncpsolutions.com) writes:
> 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_draft 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_draft where fk_value = 1001201
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@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|