Which is redundant index in the below example in SQL Server

0
votes

I have listed some indexes below

  • IndexA(Column1,Column2)
  • IndexB(Column1,Column2,Column3,Column4,Column5)
  • IndexC(Column2,Column3)
  • IndexD(Column1,Column3)
  • IndexE(Column1,Column4)

Here, I am thinking that the duplicate index is IndexA. But after reading some blogs in internet, I found that that IndexD, IndexE along with IndexA are redundant.

3
sql-servertsqldatabase-indexes
You are misreading the blocks or do not understand them. – Gordon Linoff
Only IndexA is redundant, and I'm not even sure about that. – Zohar Peled
@GordonLinoff ,why i said IndexD & IndexE as redundant because IndexD & IndexE columns already included in IndexB. May be i am wrong ...i am just trying to learn. – user1469712
@user1469712 the optimizer could pick IndexA if it didn't want columns 3-5 because it would use less IO and memory to load the relevant pages. Why the question? If you want to find unused indexes in a DB, you can use a query to find them. I think there's even a SSMS report about this – Panagiotis Kanavos
Is this homework? Depends on you definition of redundant. If it is a scan versus a seek is it redundant. Load up some data and test. – paparazzo

3 Answers

2
votes

It depends on your query patterns.

If you only check for equality on col1,col2,index A is redundant..Of course i can remove Index A,since all the queries it can satisfy are all satisfied by Index B..

There are few cases where Index B can be helpfull,below is an example 

 select col1,col2 from tbl where
   col1=someval and col2>someval

Rest of the indexes work better for few types of queries.Also you can't have generic indexes, you have to take a query and see which index is helpfull for that query and create index based on that

1
votes
  • IndexA: if not unique
  • IndexD, IndexE: possibly

If any index is unique, then it can't subsumed into a wider overlapping index because uniqueness is lost.

If any index is filtered, then it probably can't be subsumed.

In your example, only IndexA is probably overlapping unless being used for uniqueness.

IndexD and IndexE might be, it depends on selectivity and query pattern
If might have these as (Column3, Column1) and (Column4, Column1), or one combined index, or remove them and them use sp_blitzindex to find out more

There are more options too. For example, re-ordering IndexB or working out what could be a simple INCLUDE

0
votes

Depending on the selectivity of Column1 and Column2, it maybe that you can make IndexB redundant by adding Column3, Column4 and Column5 as included columns on IndexA. In some instances, particulary where your index will have a large number of non-leaf pages, this will offer better performance as the included columns will only be stored on the leaf pages.