May 27, 2014 at 12:07 pm
If you have two indexes that are similar and only the first key column is different, the indexes will cover different queries. In that case, why would you want to drop either of them? If they're both being used, you wouldn't want to drop them. Like Grant pointed out earlier, if you have one index being used 100 times per minute, you probably don't want to drop it.
May 27, 2014 at 12:27 pm
ramana3327 (5/27/2014)
So I have to find a strong supporting point why I choose to drop that indexes over the other..
And you're considering dropping one that is being used, as shown by Grant's query? I'd have thought the fact that the query showed it in use would be a supporting reason for keeping it, not dropping it.
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
May 27, 2014 at 12:45 pm
Yes you are right. Showing that the indexes are using and can't drop when you have partially duplicates.
If we have exact duplicates and both has user seeks
one has with 120 and another has user seeks 1100. I am thinking that surely we have to drop one Index. Duplicate Indexes hurts performance right.
May 27, 2014 at 1:02 pm
If you have exact duplicate indexes, it doesn't matter which you drop, they're identical.
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
May 27, 2014 at 1:54 pm
So we don't need to consider about the user seeks here. Ok. Thank you.
May 27, 2014 at 9:40 pm
ramana3327 (5/27/2014)
They ask me to find the exact duplicate and partially duplicate Indexes. Also I need to drop the indexes that are duplicated either exactly or partially. So I have to find a strong supporting point why I choose to drop that indexes over the other..
The indications are though that those indexes are in use. I had you identify the queries so that you could figure out which query was using which index and why it was using that index. Then, you can determine if dropping the indexes is something you do. Just simply identifying similar indexes doesn't mean you can drop one unless you know it's not in use. It could be that similar indexes are used by different queries in different ways. That seems to be the case here, but you have the evidence in front of your own eyes and will need to make the determination.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2014 at 9:42 pm
ramana3327 (5/27/2014)
So we don't need to consider about the user seeks here. Ok. Thank you.
But the indexes you showed were not identical. And, if they're not identical and both are being used, then no, you shouldn't disregard the user seeks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 30, 2014 at 11:11 am
No here I am talking about the exact duplicates. I have two indexes with only one same column for both. But having one with high user seeks and one with lower user seeks.
In that case we can drop any one or just drop lower seeks index?
May 30, 2014 at 1:05 pm
If the indexes are absolutely identical, then it cannot matter which you drop because they are identical.
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
May 30, 2014 at 1:20 pm
Thanks to everyone.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply