Potty Chairs and Duplicate Indexes

This post is my contribution to T-SQL Tuesday #10, which is being hosted by Michael J. Swart (blog | twitter)

Each year my Boy Scout troop has a rummage sale as a fundraiser.  All the scouts and their families gather up their old clothes, books, toys, electronics, and any other treasures they no longer need and donate them to the troop, which sells them.  It's basically a giant garage sale, except it's done in the gym of the school we meet at.  Since everything sold comes from someone associated with the troop, many years ago it was decided to create an award for the person that donates the weirdest piece of crap most unique item each year.  This award comes in the form of a travelling trophy which started out as a child's potty chair that was painted gold.  Each year, the winner is instructed to embellish the trophy with something new.  Here's how it looks now!

As you can see, it's grown from a simple potty chair mounted on a board into a lamp with a whole bunch of accessories, including a barbecue grill. I'm surprised nobody's added a TV or fridge yet – then there'd really be no reason to leave!

So what does this have to do with indexes?  The bowels of this post contain something that I have embellished myself.  Last month Tom LaRock (blog | twitter) posted a script for helping to detect duplicate indexes.  He couldn't claim credit for it as he said it was passed on to him a while ago and he's unsure of the original source.  Similarly, I won't claim credit for this other than to say I made a few changes I thought would be handy.  If you feel this script is lacking functionality that you think would be helpful, I invite you to change it and please share with the community when you're done!

Why Duplicate Indexes Are Bad

Aside from serving no purpose, duplicate indexes can cause problems to occur which could require your attention, such as:

• Wasted disk space – just like any other type of data, storing something more than once means it will take up space more than once as well.
• Slower backups, restores, and integrity checks – more data means more time to read/write/check it all
• Slower data modification operations – inserting, updating, and deleting rows will take longer when there's more indexes that need updating
• Increased chances of deadlock and blocking – More objects requiring locks for an operation means there's a greater chance that another process has already acquired at least some of said locks
• Increased transaction log growth and/or usage – The more writing/locking that's happening due to an increased number of objects means that more will be written to the log

And I'm sure there's plenty more potential issues than those I just listed. The simplest way to avoid this is to maintain only as many indexes as necessary. This script can help you find if any duplicates exist.

What's New In This Version

• I removed the columns that returned individual component columns of the indexes.  Since there was already a column that showed them all concatenated together, I couldn't think of any reason for listing them individually other than it might help when programmatically dropping indexes, which shouldn't be done anyway.  Indexes should only be dropped after careful review.
• Added a column for displaying the size of the index.  Duplicate indexes are bad no matter what their size is, however this information may be helpful.
• Added columns showing whether the index is filtered or has included columns.

Disclaimer

I have tested this script on SQL Server 2005 and later, and it will not run on earlier versions.  You should not trust any script you find on the internet (including this one!)  Make sure you completely understand what a script is doing before running it.  Always test on a testing or development server before using it in any production environment.

The Script

What you've really been waiting for.  You can see the entire script below, or just download the .zip of it here.  Enjoy!