Database Tuning Advisor recommending too many Indexes on a Table??

Question

Database Tuning Advisor recommending too many Indexes on a Table??

neotokyo

SSCertifiable

Points: 7239
More actions
May 4, 2007 at 3:15 pm

#117087

Has any once come across a situation where
the DTA has recommended too many indexes
on the Same table which has not undergone
any Schema changes??
Each time I run a workload file there is
always yet another Index, and another
and another.
Also; what are the risks of 100% dependency
on the DTA?
I'm greatful for any input on this.
Thanks.
_________________________

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

I can't say that I'm using DTA a lot, but the DTA will always try to optimize indexes for the current workload. So different workload files can result in different indexes and as long as you select the option "keep existing indexes", the chance is big that DTA recommends to add new ones.

I suggest concentrate on the querie which run most and the longest ones. It makes no sense tuning for a query which has acceptable performance but runs only very few times.

Implementing too many indexes can slow down any writing activities like insert, updates and deletes. Aslo index maintenance will take longer.

Markus

[font="Verdana"]Markus Bohse[/font]

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

100% reliance on DTA is not a good idea. You need to use your common sense as a DBA to evaluate whether the indexes it suggests are really appropriate for your system.

---------------------------------------
elsasoft.org

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

The method I use is to capture at least three different workloads, and look at the index recommendations from each one. Then I create only the indexes that were recommended by all three.

After that, I repeat the process until the recommendations for three different workloads do not produce any indexes recommended by all three.

neotokyo SSCertifiable Points: 7239 More actions · Answer 4

you mentioned this...

"Then I create only the indexes that were recommended by all three."

you mean you ran 3 different workload files, and only chose

the index which was recommended by all 3, and not every index

recommendation per workload file?

_________________________

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

When I have used DTA, I have also only implemented part of the recommendations. I tend to ignore the statistics it tells me to add, and concentrate on the indexes. I also look at the indexes suggested, and have sometimes only implemented one or two of the recommendations, usually one at a time, while testing the process(es) in question on our test system to be sure that it actually helps before putting them into production.