Duplicate indexes and indexes with differect included columns

Question

Duplicate indexes and indexes with differect included columns

ashokeie

Newbie

Points: 7
More actions
July 3, 2012 at 4:45 am

#258289

Hi,
I am working on SQL server 2005. I am trying to improve the performance of the DB by removing the duplicate indexes. Please find the 2 cases below. Is it possible to remove any index to improve the performance?
Case 1: Index keys Col1 and Col2 are present in both the indexes. But Col3 is present only in NCI_IND_1
Index name Type Index keys
NCI_IND_1n-CLCol1, Col2, Col3
NCI_IND_2n-CLCol1, Col2
Will there be any difference if I delete NCI_IND_2 index?
Case 2: Same index keys but with different included columns
Index name Type Index keys
IND_1 n-CLId, temNo (Included columns: Col4,Col5)
IND_2 n-CLId, ItemNo (Included columns: Col3,Col4)
In this case, same index keys are used in two indexes but the indluded columns are different.
Can i make this a one index and include all the columns in Included columns?
Any help on removing duplicate indexes or articles on performance tuning will be helpful.
Thanks
Ashok

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Tuning indexes is quite tricky and can be daunting if you aren't familiar with the standard tools. Glenn Berry provides one of the best toolkits around and has written numerous articles on the subject - here[/url]'s one of them.

Study the results of the queries carefully and compare the recommendations with what you've already got. Most of all, take your time and don't do too much at once. Get it right and it's very rewarding; rush it and you may as well update your resume.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

ashokeie (7/3/2012)
Case 1: Index keys Col1 and Col2 are present in both the indexes. But Col3 is present only in NCI_IND_1
Index name Type Index keys
NCI_IND_1n-CLCol1, Col2, Col3
NCI_IND_2n-CLCol1, Col2
Will there be any difference if I delete NCI_IND_2 index?

In the vast majority of cases there will be no noticable difference.

Case 2: Same index keys but with different included columns
Index name Type Index keys
IND_1 n-CLId, temNo (Included columns: Col4,Col5)
IND_2 n-CLId, ItemNo (Included columns: Col3,Col4)
In this case, same index keys are used in two indexes but the indluded columns are different.
Can i make this a one index and include all the columns in Included columns?

In the vast majority of cases, yes you can.

Why it's the vast majority... In both cases you're making one index wider or dropping the narrower one. That makes queries that use that index do more work. In the vast majority of cases that very minor increase in work will be fine. In specific cases where specific queries absolutely have to execute as fast as possible and even a millisecond slower is a major crisis you wouldn't want to consolidate indexes.

http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/

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