two indexes give exact same execution plan but optimizer always picks 1. why?

Question

two indexes give exact same execution plan but optimizer always picks 1. why?

winston Smith

SSCoach

Points: 19500
More actions
January 12, 2009 at 7:19 am

#195859

I have a table with two indexes on it. The table and two indexes are:
create table member
(
)
create index FirstNameCovering on Member(firstname)
include(member_no, region_no)
create index FirstNameCovering2 on member(FirstName,Region_no,Member_no)
THe query im running is this:
SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (INDEX (FirstNameCovering))--heap
WHERE m.FirstName LIKE 'K%'
AND m.Region_No > 6
AND m.Member_No < 5000
each time i let the optimizer chose, it picks teh FirstNameCovering index, however when i force the query to use one index or the other, the IO is equal for both, and execution plan shows both each as being 50% of the batch, meaning they are even.
what pushes the optimizer toward one index over the other if both are providing equal results?

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

winston Smith (1/12/2009)
what pushes the optimizer toward one index over the other if both are providing equal results?

Probably it thinks that the index that has the include columns will have fewer pages at the non-leaf levels and hence will be very slightly cheaper. How big's the table you're testing on?

Remember that because of the inequalities it can only seek on one column, so having the other two in the key provides no benefit over having them in the include.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

If the two indexes really provide exactly the same execution plan, then the optimizer just picks one. Probably the first one. All other things being equal, it has to just pick one.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

winston Smith SSCoach Points: 19500 More actions · Answer 3

GilaMonster (1/12/2009)
winston Smith (1/12/2009)
what pushes the optimizer toward one index over the other if both are providing equal results?
Probably it thinks that the index that has the include columns will have fewer pages at the non-leaf levels and hence will be very slightly cheaper. How big's the table you're testing on?
Remember that because of the inequalities it can only seek on one column, so having the other two in the key provides no benefit over having them in the include.

table is small, 10000 rows, 1160kb of data.

Thanks, this answers my question.Thanks GSquared also. your answer makes sense also.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

Remember that because of the inequalities it can only seek on one column, so having the other two in the key provides no benefit over having them in the include.

I'm interested to know how do you differenciate an inequality from an equality.

How does a like react?

How does a greater then react?

Is there a difference between greater then and lesser then? Does it depend on the order of the index (asc or desc)?

I'm pretty sure the only equality case you can have is a straight equal, am I right?

Thanks in advance

Cheers,

J-F

Cheers,

J-F

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

With that row count, the noncluster's probably only 2 levels deep. As the table gets larger, you'll probably be able to see a small IO count difference between the two indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Jean-François Bergeron (1/12/2009)
How does a like react?

If there's a wildcard, it's treated the same way as a between.

LIKE 'A%' becomes >= Scalar Operator('9þ') AND < Scalar Operator('B')

How does a greater then react?

Not sure what you're after as an answer here.

Is there a difference between greater then and lesser then?

Don't think so. Test it?

I'm pretty sure the only equality case you can have is a straight equal, am I right?

Or a like without a wildcard, or an IN clause (which translates to a series of equalities with ORs)

There was a whole long discussion of inequalities as they relate to indexes in another thread that Winston started. I'll see if I can find the link.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

Nice, I did not know the like reacted as a between when there is a wildcard in it.

But, if it works as a between, how do write this expression as a between? :

Like 'ABC%DEF'.

Thanks in advance,

Cheers,

J-F

Cheers,

J-F

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Try it and see.

If you have a table with an index on a string column, you run a query of that form and check the execution plan, you'll see exactly what SQL does to the inequality. Check the seek predicate of the index seek operator

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 9

For the next query, here is what happens. Seems the optimizer does not use the last part of the query 's'

SELECT *

FROM Translation

WHERE objid LIKE 'A/P%s'

Seek Predicate:

Start Range: [Enterprise].[dbo].[Translation].ObjID >= Scalar Operator(N'A/P'), End Range: [Enterprise].[dbo].[Translation].ObjID < Scalar Operator(N'A/Q')

I'll attach a print Screen of the plan.

From what I read in the plan, the only predicate it uses is the first Part : Like 'A/P%', how about the 's' that is after?

Cheers,

J-F

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

Jean-François Bergeron (1/12/2009)
From what I read in the plan, the only predicate it uses is the first Part : Like 'A/P%', how about the 's' that is after?

There should be a seek predicate and a predicate. The seek predicate is what it can do as an index seek operation, the predicate is the removal of non-matching rows immediately thereafter.

Have a look at this thread. There's a mention of predicates and seek predicates on page 2

http://www.sqlservercentral.com/Forums/Topic631509-360-1.aspx

p.s. Please don't post bmp images. They're absolutely huge. Best way to post an exec plan from SQL 2005 is to save it as a .sqlplan file, zip it and attach.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 11

Thanks Gail, this post helped me understand how the indexes work on inequality columns! I'm very grateful you took some time to show me how it works in details,

Thanks again,

Cheers,

J-F

Cheers,

J-F