Finding and Eliminating Duplicate or Overlapping Indexes

  • Comments posted to this topic are about the item Finding and Eliminating Duplicate or Overlapping Indexes

  • Thanks for the Article.

    I voted it with 5 stars.

    But there's only a hole:

    Your query doesn't consider the order on columns.

    Example:

    create index idx_MyTab on MyTab(col1,col2)

    and

    create index idx_MyTab_1 on MyTab(col1 DESC,col2)

    So, in the case above, it should be listed with a "warning".

  • very usefull, thx

    a small correction, when name of first index column is begining part of second index column (add comma):

    AND ( DUPE1.key_column_list+',' LIKE LEFT(DUPE2.key_column_list,

    LEN(DUPE1.key_column_list)+1)

    OR DUPE2.key_column_list+',' LIKE LEFT(DUPE1.key_column_list,

    LEN(DUPE2.key_column_list)+1)

    OR DUPE2.key_column_list=DUPE1.key_column_list

    )

  • Replace:

    STUFF((SELECT ', ' + COLUMN_DATA_KEY_COLS.name

    with:

    STUFF((SELECT ', ' + COLUMN_DATA_KEY_COLS.name + ' ' +SUBSTRING('+-',INDEX_COLUMN_DATA_KEY_COLS.is_descending_key+1,1)

  • grzegorz.mozejko (6/16/2014)


    very usefull, thx

    a small correction, when name of first index column is begining part of second index column (add comma):

    AND ( DUPE1.key_column_list+',' LIKE LEFT(DUPE2.key_column_list,

    LEN(DUPE1.key_column_list)+1)

    OR DUPE2.key_column_list+',' LIKE LEFT(DUPE1.key_column_list,

    LEN(DUPE2.key_column_list)+1)

    OR DUPE2.key_column_list=DUPE1.key_column_list

    )

    better

    AND ( DUPE1.key_column_list+',' LIKE LEFT(DUPE2.key_column_list+',',

    LEN(DUPE1.key_column_list)+1)

    OR DUPE2.key_column_list+',' LIKE LEFT(DUPE1.key_column_list+',',

    LEN(DUPE2.key_column_list)+1)

    )

    I'm looking now for solution for column names with '_'

  • grzegorz.mozejko (6/16/2014)


    grzegorz.mozejko (6/16/2014)


    very usefull, thx

    a small correction, when name of first index column is begining part of second index column (add comma):

    AND ( DUPE1.key_column_list+',' LIKE LEFT(DUPE2.key_column_list,

    LEN(DUPE1.key_column_list)+1)

    OR DUPE2.key_column_list+',' LIKE LEFT(DUPE1.key_column_list,

    LEN(DUPE2.key_column_list)+1)

    OR DUPE2.key_column_list=DUPE1.key_column_list

    )

    better

    AND ( DUPE1.key_column_list+',' LIKE LEFT(DUPE2.key_column_list+',',

    LEN(DUPE1.key_column_list)+1)

    OR DUPE2.key_column_list+',' LIKE LEFT(DUPE1.key_column_list+',',

    LEN(DUPE2.key_column_list)+1)

    )

    I'm looking now for solution for column names with '_'

    Replace(LEFT(DUPE2.key_column_list+',',LEN(DUPE1.key_column_list)+1),'_','[_]')

  • Your correction: SUBSTRING('+-',INDEX_COLUMN_DATA_KEY_COLS.is_descending_key+1,1)

    eliminates both problems I found

    thx

  • Both are good points! In my effort to keep this "simple" I left out a few use-cases, which you both have covered. I'll modify the article shortly to take them into account (once I've tested and verified there's nothing else missing here).

    There's definitely no harm in completeness!

    EDIT: New version published. While I can't include every single possible use-case for index properties, these additions provide good examples of how to customize these scripts & ideas to specific situations.

  • Is it a good idea to add column "is_disabled" from sys.indexes to row set ?

  • The is_disabled flag could be worked in, but I would venture that a disabled index and an enabled index that are identical should be flagged as dupes.

    For just display purposes at the end, sure---no harm in adding it, and it would help troubleshoot the oddball case where we would rather drop a duplicate that is disabled, rather than its enabled counterpart.

  • "Finding and Eliminating..." - Nothing said about eliminating the duplicates!

  • erb2000 (6/16/2014)


    "Finding and Eliminating..." - Nothing said about eliminating the duplicates!

    Just above the Conclusion section there are two DROP INDEX statements....

  • Very useful, and thanks!

    I think I can even learn from this to simplify a script I wrote to script out all existing indexes on a table (which I use for many things, such as dropping indexes before moving large datasets in/out, making smaller backups, and the like). I was originally using my script to try to identify redundant and missing indexes. Your logic helped ease that for me. 🙂

  • Hmmm, you finally put in writing (and script) what I have been doing for years. Yeah! Great work!

    Mike Byrd

  • Lempster (6/16/2014)


    erb2000 (6/16/2014)


    "Finding and Eliminating..." - Nothing said about eliminating the duplicates!

    Just above the Conclusion section there are two DROP INDEX statements....

    This is a great article and very useful. My point is that it doesn't say much about eliminating the indexes. I ran the script on one of our databases and it returned 600 rows. That's 300 duplicate indexes. I'm looking for a way to drop them automatically.

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

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