Re: Index strategy
Date: Thu, 3 Jun 2010 10:06:49 -0700 (PDT)
Message-ID: <2fb127b5-8846-408f-a9d7-6b4f76ee5e78_at_n20g2000prh.googlegroups.com>
On Jun 2, 5:23 pm, galen_bo..._at_yahoo.com wrote:
> Helma <helma.vi..._at_hotmail.com> writes:
> > I now work at a shop where the developers over the years have added
> > many (random) indexes on the database, and (still) having problems
> > with performance. I see many sql-statements coming by that use
> > suboptimal indexes, I think it would be a good idea to mass-review the
> > sql and indexes, increasing the quality of the indexes and reduce the
> > 'overindexing' of the DB. Seniors! I assume you have encountered this
> > situation before. How to efficiently deal with such situation?
>
> At the end of the day, what is "over-indexing" a database? If you don't
> see a slowdown because of the indexes, it sounds like you should be fine
> there. So what if an index isn't used or one index would solve what 5
> indexes are currently solving. If something is hitting you because of
> too many indexes, then indentify those indexes and fix that problem.
> Other than that, start putting good practices going forward and keep a
> look out for problems caused by over-indexing.
>
I may be wrong, but from the tone of the OP I gather they are pretty close to this situation already, and asking for the next step.
I don't think there is any efficient way to accomplish it, and I'm not sure that just using the type of business justification an external consultant would use - "is there anything slowing down currently because of this?" - is the correct response. I'd advise getting Tom's Effective Oracle By Design book and comparing past and current programming practices to that. I think with the internal knowledge of a specific environment one could come up with a "top 10 past goofs we oughta fix" list. Slow-by-slow processing, leftover RBO assumptions (this could lead to over-indexing), there may be other low-hanging design fruit the developers suspect once they understand the issues, possibly even point out with some pattern matching on the code.
This is fixing accumulated deferred maintenance, so cost justification is difficult. It takes some good strategic planning to not hit a sudden-needed-upgrade wall. Sometimes, fixing a lot of small problems that currently aren't a big deal can avoid big problems in the future. Sometimes, you really do run into the limits of a system and ought to upgrade. Then you have to watch out for newer systems not being as efficient as the old.
I would be more paranoid than Mark about dropping unused indices, unless they've been tracked for a year, at least. Unless, say, you get overtime for tracking performance problems on New Years Eve.
jg
-- _at_home.com is bogus. Who designed your database? http://www.signonsandiego.com/news/2010/jun/02/u-t-editorial-failure-leadership/Received on Thu Jun 03 2010 - 12:06:49 CDT