Script to Merger duplicate indexes

  • Hello,

    I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.

  • Simply DROP the index you no longer wish to keep.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Okay you need to explain that a bit more clearly -- an Index is an object applied to a table.

    So yes you can sort of merge two indexes into one using a script but why would you be doing that?

    Do you have like a lot of these duplicated scripts through out your database?

    Is there a particular reason you must use a script?

    Might you be actually talking about something else and simply calling it an index incorrectly?

  • EM2 wrote:

    Hello,

    I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.

    You don't.  You have to drop both indexes and create a new one.

    BTW... be real careful... if the index is a unique index, it may be the target of an FK.  Also, not all supposed "dupe" or "mergable" indexes actually are. As with all else, "It Depends".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • So we have some indexes that are very similar; some of the tables seem a bit over indexed. The real question here is, how can we merge the indexes that are similar (create one index out the two similar indexes). I can try to do this manually but with so many tables, the process will take a long time. Having a script that peform this task will minimize the  time required... I hope this gives clarity. Thanks so much.

  • You don't actually have to drop both indexes.  You would, for example, often drop one index and modify the other one.

    I've started on code to do this process but have never finished it.  I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you, Scott. This stored procedure generates code for all indexes or similar/duplicate indexes?

  • For all indexes, or the 1 you tell it to generate (either by index number OR by index name).

    As I said, I've been designing code to do my index checking / consolidation work for me, and generate the needed index statement(s), but I haven't completed the design nor the coding yet.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher you ought to share that here and maybe one of us (or more than one of us) can help you complete that code. That might also help this individual and many others streamline this issue going forward.

    As for too many indexes, yeah we ran into that as well on  few tables but we did not have an over abundance of scripts that needed to be fixed and as such we simply dropped one and adjusted the other (if needed).

    That being said I think they recommend like about 7 or fewer indexes upon a single table in general for efficiency purposes but this kind of also depends on how frequently that table gets added to or updated as that is where a lot of indexes tends to slow things down.

  • @dennis Jensen, thank you!

  • ScottPletcher wrote:

    You don't actually have to drop both indexes.  You would, for example, often drop one index and modify the other one.

    I've started on code to do this process but have never finished it.  I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.

    Seems contrary to the purpose of combining indexes.  You still end up with 2 indexes instead of 1.

    EDIT, because it came up, there is no way to "modify" an index and that appears to be the point of confusion.  There is a way to CREATE a new index with the "DROP EXISTING" option enabled.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden based on what ScottPletcher stated I do not see how one would end up with 2 Indexes as he did state you Drop one of the Indexes and modify the other which to me means you had 2 Indexes you drop one leaving with 1 Index and then you modify that remaining Index to accomodate for whatever extras are needed. So perhaps you misread what was posted, if not please explain?

  • As for scripts to "find duplicates", they have this new fangled thing called a "search engine". 😀

    Try the following search and see what you come up with.  I haven't looked any of them so I can't make a recommendation.

    Me?  I usually just look for unused indexes.  I also have some proof in a presentation that (for example) having a Non-Clustered index being keyed exactly the same as the Clustered Index isn't necessarily the bad thing everyone makes it out to be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Really Jeff Moden that would be interesting to read (or see) -- your proof in a presentation -- as I find that a rather interesting stance and would greatly like to see that (as I am sure others would be as well). I might be good at SQL but I can guarantee you there are always things that I can learn about it and would love to do just that.

    I am still hoping to see and help with ScottPletcher script that he talked about.

    That being said, I do not see in your response Jeff Moden an answer to the question that I had posed to you about the Index count, but I hope you will expound upon that at some point in time.

  • Are there some people who think that 'duplicate' means 'same or similar'?

    If it's a duplicate, it is exactly the same and can be dropped. Otherwise, it is not a duplicate and I cannot imagine the complexity of a script which somehow works out which similar indexes can (and should) be combined and then scripts out the necessary T-SQL to effect those changes.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 15 posts - 1 through 15 (of 25 total)

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