Re: Index vs. table scans in statspack reports

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:1tpfsvkgetd66e4368p0q9aggldebobthq_at_4ax.com...
> pharfromhome_at_hotmail.com (Geomancer) wrote:
>
> >Do you have "missing" indexes?
>
> I don't care about missing indexes, I don't write applications. I care
> for *unused* indexes (which I remove), or indexes that need
> rebuilding.
>

Hi Rick,

That's an interesting comment.

The "cost" of having an *unused* index is the storage that it wastes and the resources it consumes (such as additional I/Os, CPU, wasted memory buffers, latch contention, etc.) by it being unnecessarily maintained. Although certainly not recommended, such unused indexes may not actually have a substantial or noticeable impact on your system/applications. Let's say it causes on average a 0.1 sec response time performance hit for affected applications.

I won't bite too much regarding the need to rebuild indexes but lets say it's not something I would care too much about with the majority of indexes, as rebuilding indexes generally has zip noticeable effect. Let's say it causes on average a 0.0001 sec response time performance hit for affected applications.

However a *missing* index (meaning that such an index would be used by the CBO in a beneficial manner) could result in such applications being severely impacted. Not only the application, but the entire database could be severely impacted as a result of diabolical SQL performance due to a missing index. Just one missing index could cause the entire database to run like a constipate slug. Quite realistically, 10 hour response times could be cut down to sub-seconds as a result or let's say it causes a 35999.99 sec response time performance hit for affected applications.

The above may sound it but it's not necessarily an exaggeration ...

If I *cared* about performance, I know what I would *care* most about in my environment whether I write the applications or whether each line of (say) Java reads like some Egyptian lost tomb encryption.

Rick, learn to care about "missing" indexes ...

Cheers ;)

Richard Received on Tue Dec 02 2003 - 05:16:37 CST