Overlapping indexes

Question

Overlapping indexes

NewBeeSQL

SSCrazy

Points: 2170
More actions
May 4, 2010 at 3:22 am

#222094

I was given a task to find out overlapping indexes and disable from my db....can any one show me the path on how to find overlapping indexes??
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

NewBeeSQL (5/4/2010)
I was given a task to find out overlapping indexes and disable from my db....can any one show me the path on how to find overlapping indexes??

what do you mean by overlapping indexes? i think you require list of indexes which have not been usd for a long time.right ?

Additionally it is not recommended to remove any index.unless you are 100% sure that its removal will not give any bad imapct on performance.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

Nope..you misunderstood..

overlapping indexes are the indexes which overlapped over the columns...for example..if i have 3 indexes..

index 1 is on Table1 on col1,col2,col3

index 2 is on Table1 on col1,col2

index 3 is on table1 on col3,col2

now, which index to be removed and which ones to keep is the question??

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

NewBeeSQL (5/4/2010)
Nope..you misunderstood..
overlapping indexes are the indexes which overlapped over the columns...for example..if i have 3 indexes..
index 1 is on Table1 on col1,col2,col3
index 2 is on Table1 on col1,col2
index 3 is on table1 on col3,col2
now, which index to be removed and which ones to keep is the question??

which are clustered which are non clus. ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

krypto69 SSChampion Points: 13604 More actions · Answer 4

it's not always bad to have overlapping indexes. They won't be used if they aren't needed.

But they will take up space.

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

NewBeeSQL (5/4/2010)
Nope..you misunderstood..
overlapping indexes are the indexes which overlapped over the columns...for example..if i have 3 indexes..
index 1 is on Table1 on col1,col2,col3
index 2 is on Table1 on col1,col2
index 3 is on table1 on col3,col2
now, which index to be removed and which ones to keep is the question??

Index 2 can be removed, because it's keys are a left-based subset of another index (in this case, Index 1). That makes it a duplicate index.

Index 3 can not be removed. It is not redundant, it is not a left-based subset of another index.

If a query filters only on col2, col3, it can only see on Index 3, not on index 1.

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

http://www.sqlservercentral.com/articles/Indexing/68636/

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

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 6

Thanks Gail...its answered my problem....

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I have done this evolution for numerous clients. I use sp_helpindex3 to generate the information on all indexes and then use my Mark I calibrated eyeballs and Excel to find the ones that can be removed. While I am at it I also check index usage metrics for unnecessary indexes as well.

http://www.mssqltips.com/tip.asp?tip=1003

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service