Index Tuning

Question

Index Tuning

tgarland

Hall of Fame

Points: 3367
More actions
January 22, 2009 at 12:55 pm

#383789

I am trying to do some index maintenance on a few tables using the dm_db_missing_index_detail dynamic management view. For one of the tables, it recommends the following indexes:
equality_columns inequality_columns included_columns
JobID
JobID Match
JobID City, State, UID
JobID Parcel_Num, UID
I have thought about combining those into one index that looks like the following but I am not sure if that would make the index less valuable:
create nonclustered index IX_TableName_JobID on dbo.TableName
(JobID, Match)
include (City, State, UID, Parcel_Num)
Would this decrease the value of the index? Should I create the 4 separate indexes? What would you recommend?

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

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

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

tgarland (1/22/2009)
I have thought about combining those into one index that looks like the following but I am not sure if that would make the index less valuable:

Looks fine.

This is one of the things with the missing index DMV. It doesn't consider a similar index existing or already suggested

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

The combined index should be fine.

- 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

tgarland Hall of Fame Points: 3367 More actions · Answer 3

Thank you. I have been combining thinking it would be ok. I just didn't know if I was going to cause a query not to use the index by combining them. Indexing is a fine art.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You can always combine include columns without an effect

You can usually combine the inequalities without breaking anything, not always

You usually can't combine equality columns without causing a problem.

I blogged earlier this week on index column order for equalities.

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

tgarland Hall of Fame Points: 3367 More actions · Answer 5

GilaMonster (1/22/2009)
You can always combine include columns without an effect
You can usually combine the inequalities without breaking anything, not always
You usually can't combine equality columns without causing a problem.
I blogged earlier this week on index column order for equalities.

This is some good information. I have also been combining equality columns as long as the initial column was the same. I may need to go back and separate those.