Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: How to know when you have too many indexes?

    Hey guys,

    My indexing skills aren't very good. I pretty much rely on the Database Engine Tuning Advisor. It works good. I have read that too many indexes can be bad. I am worried that I may be adding too many indexes and decreasing the performance of some of my earlier indexes. How do I know when I have too many indexes? Thanks.

  2. #2
    Join Date
    Sep 2010
    Posts
    153
    as far as i know indexes are created for those tables which contain large amount of data so create index on those tables only so that it will increase your performance. do not create index for small and thos etables containing less data.

  3. #3
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    USED TO Be, way back when, that 7 was considered the maximum that should be used. A lot depends on the application and the size of the table. Lots of inserts/deletes means more maintenance to the indexes during that process, and the larger the table, the longer it takes. I typically add only what is necessary for the app/reporting (both utilize the same DB where I'm at). If you have a data warehouse for reporting and one for OLTP, add whats appropriate for each function on each respect server/instance. Overkill is never a good thing even if MS says you can have upto 249 nonclustered indexes....Another of those "it depends" questions.
    -- You can't be late until you show up.

  4. #4
    Join Date
    May 2009
    Posts
    56
    Ok Thanks. I am only ending up with like 6 or 7 on the higher end for some of my tables. But I didn't know if that grew to like 15-20 in the next year or so if that would start to cause problems for the earlier indexes. It sounds like I should be okay. I understand more indexes means more maintenance during insert/deletes. Currently I typically just add indexes when necessary, but I was seeing some queries that could benefit from it since they are ran so frequently. Like one query only took 10 seconds to run but was run 100+ times in a row. Indexing knocked it down to 1.5 seconds. I saw the advantage since it was run so much.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    no..if the query ran a boatload of times, the index was not used and the data was in buffer

    The answer is it depends

    You can even look up index intersection

    Look in books on line or go google it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The thing is just be smart about it. One of the things I always bring up is in a banking app they wanted an index on the check number. It does you no good, every account will eventually have that check number. Also, I'm betting if you have a customer enter a search for check number 1101, your company does not want them seeing check number 1101 for every other customer. So, there has to be more information available. If you know the check number, then you should know some if not all the rest of this info.
    What is the Account Number?
    Who is the Payor(Account Holder)
    Who is the Payee?

    Those are more likely the columns that would need to be indexed. Not the check number or the amount of the check, etc...

    Dave Nance

  7. #7
    Join Date
    May 2009
    Posts
    56
    Quote Originally Posted by Brett Kaiser View Post
    no..if the query ran a boatload of times, the index was not used and the data was in buffer

    The answer is it depends

    You can even look up index intersection

    Look in books on line or go google it
    Hmm, I see. It wasn't the EXACT same query. Different qualifier. ie

    Select name, address from table1 where id = 1;
    the next query would be
    Select name, address from table1 where id = 2;

    So would the index be helping there, assuming an index on id? I'd expect it would because it is returning different data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •