Query optimizer not acting as I'd expect

Question

Query optimizer not acting as I'd expect

mrpolecat

SSCertifiable

Points: 7014
More actions
November 1, 2007 at 2:30 pm

#62486

I have a simple query
select * from table1 where field1 = 'xxx'
There is a clustered index on the PK and a non clustered index on field1
The execution plan uses an index scan on the PK clustered index.
I hint at the index on field1 and it uses an index seek on the field1 index.
Neither query takes a significant amount of time but I would think that it would use the seek where available.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

mrpolecat (11/1/2007)
I have a simple query
select * from table1 where field1 = 'xxx'
There is a clustered index on the PK and a non clustered index on field1
The execution plan uses an index scan on the PK clustered index.
I hint at the index on field1 and it uses an index seek on the field1 index.
Neither query takes a significant amount of time but I would think that it would use the seek where available.

What percent of rows in your table have field1 = 'xxx'?

SELECT * requires to read lines from clustered index (where data is actually stored) anyway.

So, optimizer have 2 options:

1) seek the value 'xxx' in the index and perform bookmark lookup to retrieve all values from other columns for selected rows;

2) scan clustered index and filter the rows with 'xxx' in field 1.

Probably current statistics show that 2nd option is faster.

Populate the table with 100k of rows and leave < 10 of them having field1 = 'xxx'. You'll see optimizer going another way.

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

Thanks Sergiy, I've alway just looked at the fields I am querying when building the indexes and not the data in the fields. I'll have to pay more attention to that in the future. Is there a ballpark percentage I should be looking for that the index on the field will be used? 'xxx' made up 25% of the rows but I tested on a value that had 6% and it still used the clustered index. I then tried one at 2.6% and it used the field index.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

I'm not aware about any fixed number.

And I don't think there should be one.

It's a job of optimizer to make an estimation for each available options and choose the best one.

BTW, it does not always make right decisions. But it's another story.

_____________
Code for TallyGenerator