Post reply

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

  • 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.

    _________________________

  • May 5, 2007 at 8:15 am

    #704504

    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]

  • May 5, 2007 at 9:37 am

    #704510

    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

  • May 5, 2007 at 6:07 pm

    #704524

    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.

     

     

  • May 7, 2007 at 9:06 am

    #704673

    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?

    _________________________

  • May 7, 2007 at 10:51 am

    #704719

    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.

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

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