predicates and output lists and indexes

Question

predicates and output lists and indexes

TroyG

SSCrazy

Points: 2633
More actions
June 10, 2007 at 11:02 pm

#372123

Hi there,
On my execution plan, I have had a number of places which have highlighted a couple of indexes which is fine, but I am hitting a brick wall with this one - and cannot seem to formulate an index which works for it.
Basically the information that I get is:
Object:[tdstest].[dbo].[VMF_TRIP_COST_HIST].[vmfTripCostHist_dateTripid] [VTCH]
Predicate:[tdstest].[dbo].[VMF_TRIP_COST_HIST].[VTCH_ID] as [VTCH].[VTCH_ID]=[tdstest].[dbo].[VMF_TRIP_COST_HIST].[VTCH_ID] as [VTCH].[VTCH_ID]
Output List:[tdstest].[dbo].[VMF_TRIP_COST_HIST].VTCH_DATE, [tdstest].[dbo].[VMF_TRIP_COST_HIST].TRIP_ID
I may have been wrong, but what I have been doing is creating an index on the table where the object is, and then having the index defined with the first columns being the fields in the "predicate" area (in this case above VTCH_ID) and then having the fields in the output list in the included columns (to try and make use of the covered index facility)
Now there is a PK index on this table which is defined as the VTCH_ID - so would have expected it to use this.
Can someone/everyone point out where I am going wrong - hopefully I have given you enough info (let me know if I have not)
Thanks in advance
Troy

Viewing 8 posts - 1 through 7 (of 7 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

umm no sorry it doesn't make sense to me. If you're asking about index use then it's also down to io cost not just are all the columns in the index, and for a covered index EVERY column used in the query must be in the index, you'll maybe have to decide the best order of columns too as just beacuse you've created a covered index doesn't mean the optimiser will use it, especially if a scan will do the job better.

Hope this helps.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

To add some clarity to covered indexes.

SQL 2005 has the nice feature called "include" which should be used.

An index should have, in the index itself, columns used in the join and where clause.

However, any columns used in the select do not need to be and slow things down as they affect the ordering.

Columns in the select should rather be in the INCLUDE part of the index as they are then kept as unordered data.

CREATE INDEX ix_XXX ON MyTable(Col1, COl2) INCLUDE (COl3, Col4)

Col3 and Col4 will stop a bookmark lookup or a double table join, but do not change the order of the index.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

TroyG SSCrazy Points: 2633 More actions · Answer 3

thanks guys for the feedback - I understand both your points and can appreciate it is hard to say "this is the solution" when it comes to indexing...the part which confused me, was the information in the predicate section as it has the fields but then has a qualifyer (ie "="):

[tdstest].[dbo].[VMF_TRIP_COST_HIST].[VTCH_ID] as [VTCH].[VTCH_ID]=[tdstest].[dbo].[VMF_TRIP_COST_HIST].[VTCH_ID] as [VTCH].[VTCH_ID]

So I suppose I was just having trouble trying to understand why the predicate would be referencing itself ie field1 = field1 like in my example?

Maybe this is just one of those things that doesn't need explaining and I am just trying to dig deeper than need be...but it just puzzled me.

Thanks again.

Cheers

Troy

Crispin Proctor SSCoach Points: 18161 More actions · Answer 4

Most probably, and you would have to look at the execution plan (The pretty picture one I like :blush

In SQL 2000, if you sort of had a good index, it would use it and the do a bookmark lookup to get the remaining columns. In SQL 2005, if you have an index (NC) and a clustered index, SQL will not do a bookmark lookup but rather open up the NC index, use it as a filter, join onto the clustered and return the rest of the columns.

I suppose it is almost like a bookmark lookup but being a loop join between the two, it's quicker.

I can only think that the reference you are seeing is this happening. Might be mistaken though.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

TroyG SSCrazy Points: 2633 More actions · Answer 5

Beauty - that could very well be the case!!! thanks for the explaination Crispin.

Cheers

Troy

Crispin Proctor SSCoach Points: 18161 More actions · Answer 6

Glad I could help. It's a hint that you should look at improving the index, bearing in mind inserts / updates etc.

Look at using the INCLUDE features of index. Less overhead than a "normal" covering index.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

TroyG SSCrazy Points: 2633 More actions · Answer 7

Okay cool - it is good just getting an understanding of what these things mean and how they come about.

I am already using the INCLUDE feature across a number of other indexes, but the context of this was just confusing me.

You can go to the top of the glass with gold star - thanks again!