Multiple Covering Indexes

Question

Post reply

Multiple Covering Indexes

Spork

SSC-Addicted

Points: 460
More actions
February 25, 2014 at 4:00 pm

#284003

After doing some performance research, it appears that covering index(es) may be beneficial for a couple transactional tables in my database.
My question is this:
Is there ever a benefit to create multiple indexes with different nonkey columns when the key columns are the same?
For instance, if I have the following scenario:
For table 123:
Index key column A
nonkey columns B,C,D,E,F,G,H
Would it ever make sense to:
CREATE INDEX IX_1
ON 123 (A)
INCLUDE (B,C,D)
CREATE INDEX IX_2
ON 123 (A)
INCLUDE (E,F,G,H)
vs one:
CREATE INDEX IX_1
ON 123 (A)
INCLUDE (B,C,D,E,F,G,H)
Thanks!

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi

The total space of

CREATE INDEX IX_1

ON 123 (A)

INCLUDE (B,C,D)

and

CREATE INDEX IX_2

ON 123 (A)

INCLUDE (E,F,G,H)

will take more space than

CREATE INDEX IX_1

ON 123 (A)

INCLUDE (B,C,D,E,F,G,H)

The selectivity of each index is same. You don't need two indexes in this case.

My suggestion is to go with the second option, i.e. the index covering all columns (B,C,D,E,F,G,H).

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

EricEyster SSCrazy Points: 2974 More actions · Answer 2

Is the query scanning or seeking on the index? The optimizer will always list the perfect index, but in many cases a close index is good enough. If you are selecting a single record, having the index with just the key column, no includes, will still perform well. Yes, it will do a second lookup to get all the other columns, but if this comes at a cost on in insert/updates as well.

dedicatedtosql Ten Centuries Points: 1147 More actions · Answer 3

CREATE INDEX IX_1

ON 123 (A)

INCLUDE (B,C,D,E,F,G,H)

Having one Index as quoted above will make much sense.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

EricEyster (2/25/2014)
Is the query scanning or seeking on the index? The optimizer will always list the perfect index, but in many cases a close index is good enough. If you are selecting a single record, having the index with just the key column, no includes, will still perform well. Yes, it will do a second lookup to get all the other columns, but if this comes at a cost on in insert/updates as well.

In both cases in the execution plan you'll see indexes' seeks. The difference is: two seeks if you have the two indexes in cases when queries request columns from the both indexes, and then a merge join; and just one seek if you have one index.

Moreover for the insert/updates, only one index will be updated in the second case. Again the second option is better, depending on your situation, maybe slightly better, but better.

Igor Micev,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Also remember that indexes are simply a sorted duplication of data that will affect nightly maintenance, backup times, backup sizes, restore times, insert times, delete times, and possibly update times. Make sure the index is actually worth it whether it's a covering index or not.

And, the index consolidation that Igor Micev posted should be strongly considered when making covering indexes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

kimreyj (2/25/2014)
After doing some performance research, it appears that covering index(es) may be beneficial for a couple transactional tables in my database.
My question is this:
Is there ever a benefit to create multiple indexes with different nonkey columns when the key columns are the same?
For instance, if I have the following scenario:
For table 123:
Index key column A
nonkey columns B,C,D,E,F,G,H
Would it ever make sense to:
CREATE INDEX IX_1
ON 123 (A)
INCLUDE (B,C,D)
CREATE INDEX IX_2
ON 123 (A)
INCLUDE (E,F,G,H)
vs one:
CREATE INDEX IX_1
ON 123 (A)
INCLUDE (B,C,D,E,F,G,H)
Thanks!

Yeah, one index is better. But how did you come up with these indexes ..

Like do you have any slow running queries that are missing these indexes or did you just ran a generic missing indexes report and trying to create indexes that would improve the performance ..

--

SQLBuddy

Spork SSC-Addicted Points: 460 More actions · Answer 7

sqlbuddy123 - I originally ran a report to get the suggested missing indexes.

At this point I am going to make the changes in our dev environment and test performance against a baseline (before indexes) to determine the impact, taking into consideration all the items that Jeff Moden mentioned above...

Thanks.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

Yeah, I agree with Jeff and others. But make sure to discuss the same with your application team\developers.

When you run the generic report, you should be very careful in choosing the indexes. Otherwise it may add to the additional maintenance overhead and slow down the performance too ..

--

SQLBuddy