Finding and Eliminating Duplicate or Overlapping Indexes

Question

Post reply

Finding and Eliminating Duplicate or Overlapping Indexes

Ed Pollack

Hall of Fame

Points: 3199
More actions
June 15, 2014 at 6:54 am

#396913

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

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

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

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 1

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

grzegorz.mozejko Old Hand Points: 308 More actions · Answer 2

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

)

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 3

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 Old Hand Points: 308 More actions · Answer 4

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 '_'

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 5

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),'_','[_]')

grzegorz.mozejko Old Hand Points: 308 More actions · Answer 6

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

eliminates both problems I found

thx

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 7

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.

grzegorz.mozejko Old Hand Points: 308 More actions · Answer 8

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

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 9

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.

erb2000 SSCommitted Points: 1697 More actions · Answer 10

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

Lempster SSCoach Points: 15651 More actions · Answer 11

erb2000 (6/16/2014)
"Finding and Eliminating..." - Nothing said about eliminating the duplicates!

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

jeffem Ten Centuries Points: 1252 More actions · Answer 12

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

Mike Byrd Default port Points: 1458 More actions · Answer 13

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

Mike Byrd

erb2000 SSCommitted Points: 1697 More actions · Answer 14

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.