Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Covering Indexes Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:21 AM
Points: 8, Visits: 181
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!
Post #1545159
Posted Tuesday, February 25, 2014 4:29 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,115, Visits: 3,239
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,
SQL Server developer at Seavus
www.seavus.com
Post #1545162
Posted Tuesday, February 25, 2014 4:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
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.

Post #1545165
Posted Tuesday, February 25, 2014 4:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303
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.
Post #1545167
Posted Tuesday, February 25, 2014 4:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,115, Visits: 3,239
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,
SQL Server developer at Seavus
www.seavus.com
Post #1545168
Posted Tuesday, February 25, 2014 5:35 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545183
Posted Wednesday, February 26, 2014 10:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1545491
Posted Wednesday, February 26, 2014 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:21 AM
Points: 8, Visits: 181
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.
Post #1545530
Posted Wednesday, February 26, 2014 12:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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



Post #1545543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse