May 19, 2008 at 7:33 am
I have a database with customerinformations, and in that database I have a table called Customers. Let's say that there is 200.000 rows.
I have an non-clustered index, called A, that has the fields Salesperson, customername.
Now another vendor has created another index called B and it is also nonclustered and has the fields salesperson, customername.
They are 100% identical. We could delete one of them, but more on a teoretical basis: What are the consequenses of this situation? Will it slow down performance, will it demand more space????
Thanks in advance.
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
May 19, 2008 at 7:46 am
More space. It's a second copy of the index. It will also mean more overhead on insert/update/delete as both indexes will have to be updated, rather than just one.
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 19, 2008 at 8:47 am
I've debated this a bit and there doesn't seem to be a reason to do this. It's likely poor evaluation of the indexes in place and just adding another one.
My feeling is the same as Gail's above. Delete the dup.
May 19, 2008 at 9:11 am
Ok - I'll bite. If you put the two indexes on different file groups, and then put the different file groups on different physical drives, and then ran queries that were covered entirely by each index, and finally specified the individual index to use - you could avoid read contention and make your two queries run faster.
Of course, this seems like a really bad idea.
May 19, 2008 at 11:02 am
But even the read contention is only halved unless the index is covering. If it's not covering it still has to go to the table or the cluster to get the rest of the data. You've only saved the, presuming a good index, trivial reads against the index and not the more costly reads against the data.
I'm with Gail & Steve.
"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 19, 2008 at 11:23 am
Grant Fritchey (5/19/2008)
But even the read contention is only halved unless the index is covering. If it's not covering it still has to go to the table or the cluster to get the rest of the data. You've only saved the, presuming a good index, trivial reads against the index and not the more costly reads against the data.I'm with Gail & Steve.
Definitely - kill one of the two.
As a small aside - would this kind of setup run any chance of confusing the optimizer? two indexes possible to use, both being "equally good", etc...? How would the optimizer pick?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 11:24 am
Michael Earl (5/19/2008)
Ok - I'll bite. If you put the two indexes on different file groups, and then put the different file groups on different physical drives, and then ran queries that were covered entirely by each index, and finally specified the individual index to use - you could avoid read contention and make your two queries run faster.Of course, this seems like a really bad idea.
On 200-thousand rows? Not worth the overhead. If it was 200-million rows, and duplicate covering indexes could be maintained, it might be worth it. Might. But on 200-thousand, no way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2008 at 12:45 pm
From what I've seen in the past with duplicate indexes, the optimizer will just pick one. To realize the use of two, you'd have to have an index hint forcing some of your reads to the other index but then you might get contention of that's the one that the optimizer picks.
"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 19, 2008 at 12:50 pm
Hey - don't attack me, I said it was a bad idea, I just figured I would give an example in which it could actually help something.
As far as the optimizer getting confused - it is ok. It will in fact, only use one of the indexes, so you will not get some crazy parallel process that uses both indexes unless you individually specify both index names in a single query - which is an interesting thing to try at a later date. Which index it picks should not make any difference unless one happens to be fragmented.
Now there is a thought - if it were a covering index - if you do an offline defrag of one of the indexes, would the optimizer use the remaining index while the defrag is happening? This is a bit academic at this point, at least for this situation, but I would be interested in the results of someone trying that - I will when I have some free time.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply