Query Optimizer choice between Table Scan and Index Seek

Question

Query Optimizer choice between Table Scan and Index Seek

Derek Holland

Grasshopper

Points: 18
More actions
August 2, 2006 at 1:03 pm

#169901

My query is regarding the optimizer's choices between table scanning and index seeking.
We have a table definition with 15 columns. The table has approximately 7 million rows.
The table has a number of indexes, one of which is defined using the first 7 columns in the table.
(the index is non-clustered)
The related application may generate queries where values for 6 of 7 columns
are used in the WHERE clause.
e.g.
select *
from   The_TABLE
where Column1 = 'ABC' and
         Column2 = '1234' and
         Column3 = '01-Jan-2006' and
         Column4 = ' 1' and
         Column5 = '9999' and
         Column6 = 'ZZZ'
... when we look at the estimated execution plan,it reveals costs of ...
Select (0%) - Paralellism/Gather Streams (8%) - Table Scan (92%)...
yet when we check the estimation using only the first 5 columns the optimizer estimates...
Select (0%) - Bookmark Lookup(8%) - Table Scan (92% - using the index defined with 7 columns)
Using only 4 of the columns ...
Select (0%) - Bookmark Lookup(99%) - Table Scan (1% - using the index defined with 7 columns),
I would have imagined that the estimated execution would try to use the index in each scenario ?
For completeness, forcing the query to use the index gives an estimation of...
Select (0%) - Bookmark Lookup(100%) - Table Scan (0% - using the index defined with 7 columns)
Forgive my ignorance, I have looked at fragmentation which looks to be well below any levels
which might give cause for concern and reorganizing of data and indexes occurs regularly ...
Can anyone explain why I am seeing the above estimations and results?
Thanks and regards
- Derek

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

The optimizer makes decisions based on data distribution in the index and whether the index is 'selective' enough. It can't perform this reliably if statistics are not up to date on the table. See the topic "UPDATE STATISTICS" in books online.

Derek Holland Grasshopper Points: 18 More actions · Answer 2

Apologies PW.

Forgot to mention that we had tried UPDATE STATISTICS on the table and specific index

and this did not help matters

- D

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Try this article:

http://www.sql-server-performance.com/nonclustered_indexes.asp

Check the section on selectivity, and order of columns in a composite index.

Derek Holland Grasshopper Points: 18 More actions · Answer 4

Thanks for your assistance PW.

That has given me a few things to consider and test.

- Derek