Script to Merger duplicate indexes

  • Phil Parkin - I have been going with the idea (aka assumption) that similar is what is being referred to here as otherwise there would be no need to merge the two Indexes for as you pointed out they would be exactly the same. As for the script Scott Pletcher mentioned I was equally intrigued to see what they had thus far created (and still hopeful that they will share it) as well as the aim for their final script.

  • Jeff Moden wrote:

    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.

    No, you do a CREATE INDEX ... WITH ( .., DROP_EXISTING = ON, ... ) to modify the remaining index, not create a new one.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    Jeff Moden wrote:

    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.

    No, you do a CREATE INDEX ... WITH ( .., DROP_EXISTING = ON, ... ) to modify the remaining index, not create a new one.

    Heh... ok... You win.  Technically,  though, you're not modifying the index, though.  You are still dropping it and replacing it.

    --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)

  • Dennis Jensen wrote:

    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?

    There is no "modify" for indexes.  As he pointed out, there is a "CREATE WITH DROP_EXISTING = ON".

    The reason for me being a bit pedantic here is for newbies (based on a lot of the hooie I've seen people post on LinkedIn).  If a newbie does manage to get an interview and they say "well, just modify the index", it could cost them because, like I said, there's no "modify" when it comes to the columns being used.

    --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)

  • Dennis Jensen wrote:

    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.

    You can make your own proof.  Create a very wide Clustered Table with a few columns and a single wide CHAR() to simulate a whole bunch of other columns.   You need something wide enough to get the number of rows per page  down to, say, 1 to 3.

    Then, write a query that will use the Clustered Index to find the beginning of a range scan and aggregate just a couple of columns to make a report.  Measure the run time for the report.

    Then, make an NCI that includes the same key as the CI and INCLUDEs that include the columns that you need (aka, a "Covering Index".)  Measure the report performance with that in place and see why, even though the key of the NCI is identical to the CI, why you wouldn't want to get rid of the NCI.  The identically keyed NCI will blow the doors off the CI.

    I'd forgotten that the presentation the proof is in is most about how to keep in-row LOBs from fragmenting the heck out of your CIs but, if you're really interested, here's the link to the presentation.

    https://www.youtube.com/watch?v=IiI4n5aFg5U

    At Timestamp 38:08, I show the results of a run to do some aggregation from a wide CI (just 1 row per page in most cases) and it continues thru a demo of what an NCI that has the exact same key as the CI (and a couple of includes) does.  Of course, you already know what's going to happen because this is a "Covering Index" that just happens to have the same key as the CI.  That part of the demo ends at 49:21.

    And, please forgive the "slow speak" in that 'tube.  It was the first time I presented this and I had also pulled an all-night at work the night before.  I didn't have much left in me.

    --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)

  • Okay Jeff Moden that is a great point.

    Although being knowledgeable about Indexes I might off-handedly state that I would simply modify the Index even though that technically means I delete it and add it back. However, I am sure the rest of my quite extensive knowledge on SQL would cover for that as a non-precise methodology but more of a conceptual methodology. Of course, if they then asked me what I meant by modifying it would say delete it and add it back.

    Also thank you greatly for that information and presentation, I am sure everyone will greatly appreciate it as well.

  • n/a

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I hope your applications do not use table hints to force the use of a given index.

    Also doublecheck the indexes are enabled !

    ( maybe disabled for 99% of the time and rebuilt to serve a specific operation and afterward get disabled again )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I found this which seem to be the solution i'm looking for:

    finding and eliminating duplicate and overlapping indexes.

    https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1

  • Great EM2 so that simply shows you how to find duplicates and overlapping indexes which was not your question. Further their final solution was simply deleting the offending Indexes that you have visually examine which were the solutions provided to you here.

    However in some cases dropping an overlap is not as straight forward since you often need to drop both Indexes and add a new one that merges the non-overlapping elements if possible which was also outlined within the solutions provided here.

    Lastly if all you needed was a method to find them, which again was not what you asked, then you should have requested that as most of us here already know to to find such things. I am only stating this so that perhaps the next time you pose a question you pose the question you are actually looking for an answer to.

  • @dennis-jensen I whole-heartedly agree to all you have stated. Thank you!

Viewing 11 posts - 16 through 25 (of 25 total)

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