April 25, 2008 at 8:45 am
Steve Jones - Editor (4/25/2008)
Keith,I think you're right, but that's why we have compatibility mode. If it's < 10 or 11, then allow them, but in new versions don't.
But that would shut you out from using ANY new features that depends on that version.
Is it really big enough issue that it is legal to do this for MS even to spend time on looking into it?
My guess is that it might be required for the Database Tuning Advisor to be able to work.....
April 25, 2008 at 8:57 am
Steve,
You asked for a "Good" reason to allow duplicate indexes.
Oracle doesn't allow them. 🙂
April 25, 2008 at 9:33 am
Thanks Steve this is a great question. And the discussion is excellent.
A few random thoughts -
Duplicates are not a great solution when you index an high Insert environment. however if you are warehousing a large data collection and want to split the two indices over two drives as stated earlier then you may gain efficiency in the reading of data. If SQL Server is smart enough it could manage traffic on the indices and avoid hot spots etc.
Also from what I have learned of Microsoft over the last 20 years is that they are not always forthright with answers. It is highly likely that they use duplicate indices in Analysis Services or other software for say the processing and execution of of cube technology or similar activity. Plus they are not going to tell us the strategy of how they do it since it is corporate knowledge they do not want out.
Rest assured that Microsoft uses it to their advantage.
Not all gray hairs are Dinosaurs!
April 25, 2008 at 10:21 am
You might get a concurrent select improvement if you had duplicate indexes on different drives/arrays and each select used a different one. Haven't tried that, don't really have a server set up in such a way that I can test it.
- 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
April 25, 2008 at 10:55 am
The separate server/scale out type approaches make sense, but only if the optimizer can actually respect this. And then how can you determine which two (or more) of a connection uses which index? Use separate queries/procs for sets of users?
I'm not sure it makes sense.
Anders, great point in compatibility, but if this is a sticking point, would it not make some sense to force the change before you can upgrade to level 10 or 11?
April 25, 2008 at 11:06 am
Steve Jones - Editor (4/25/2008)
Anders, great point in compatibility, but if this is a sticking point, would it not make some sense to force the change before you can upgrade to level 10 or 11?
Yes it does, but not always practical. I've been in lots of places where the expertise in the tool used to develop software might not necessarily be there anymore, or they simply do not have time to go trough all the code to find where they used index hints.
What we really need is a database setting to ignore index hints completely!
Personally I prefer my tools to do what I tell them, and let me decide if it's a good way to use them or not. I don't like tools that force me to do things one way only all that much...
April 25, 2008 at 11:36 am
There is always the philosophy that says: "Not only do we allow the customer to shoot themselves in the foot but we will gladly sell them an aiming device." :hehe:
I have seen some interesting arguments in this thread but no real tests. Can one put forth a good reason to have a duplicate index and back it up with repeatable tests? I'm leaving the Oracle crack out of this. I like the covering index on another spindle thought. I like having my data and logs on different spindles. Even better on different controllers.
So here is your "it's the weekend and there are no important games on" assignment.
(1) Come up with a scenario where a duplicate (either CA,CB = CA,CB or CA,CB = CB,CA) index is advantage. This scenario must be tested by another person who can verify your work.
(2) Come up with a query, or the like, that spots duplicate indexes. Oh wait. Jeff Weisbecker already wrote one[/url].
ATBCharles Kincaid
April 25, 2008 at 11:40 am
I think there is some management montra here - if at first you don't succeed, try, try again!
The question is - do you take away any sharp instruments from DBA's - so they don't hurt themselves. If it took more effort to allow dups, then to disallow dups, then hopefully MS had some good reason for it. However I can't see any advantage.
April 25, 2008 at 1:00 pm
So here is your "it's the weekend and there are no important games on" assignment.
Sorry, but being from Pittsburgh and a hockey fan, I disagree with that comment. In fact, I will be leaving in about 2 hours to enjoy those $8 beers. 🙁
April 25, 2008 at 4:11 pm
There's a Connect item on this open, and the feedback from Microsoft says there are no advantages and potential disadvantages, but it's in there for backwards compatibility. Personally I'm not sure why you need this for compatability. If you removed duplicates, wouldn't the remaining index be used? Could you just ignore some index hint if the index was gone?
What if the indexes were named explicitly and the app software explicitly references both of those names?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 25, 2008 at 5:00 pm
As an applications programmer of too many years, I've never come across a scenario where an index has been defined as having duplicates. Everything always points to something else, so how do you navigate?
But then I realized where I work has several tables with no indexes at all - does this constitute a duplicates allowed scenario? I dug under the hood and the app ensures no duplicates, based on vital columns. Then talked to the creator - 'small table don't need indexes - whole lot's in memory anyway" mmm.
That got me thinking about small reference tables in my mainframe days - we stored the data with the index. Where's a good site to find out more about defining indexes?
Thanks
April 25, 2008 at 5:27 pm
Even small tables should have a clustered index. The optimizers expect to have a clustered index, and I think it's worth doing as a general practice.
April 28, 2008 at 6:48 am
So here is something interesting. One day i says to meself... "Self, you should let sql server tell you what indexes are missing according to MS on this dev db and see what happens"
well... I created all of the indexes it recomended. Today i ran the script mentioned above for identifying dup indexes both by exact match and by starting column match and guess what. MS recomended duplicates. Doesn't mean they all get used. Doesn't mean they are the best indexes... but MS created dup indexes of the other indexes they created. Not just dups of mine but actually dups of their 'missing_index...'
Interesting.
April 28, 2008 at 8:09 am
I think it's built with the "do as I say, not as I do" philosophy :hehe:
April 28, 2008 at 8:15 am
One of the most world renowned parenting methods.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply