I am using SQL Server 2008 and have a database with more than 150 tables that have duplicate indexes. I found some sql scripts which will list the duplicate indexes, but I'm not sure if I should trust them. Supposedly, they are saying that I have over 400 duplicate indexes; I'm not sure if that is correct, and therefore don't want to use them to automatically remove dupes. How can I definitively locate duplicate indexes and remove them?
You can create your own script to do this and you will learn a lot about indexes in SQL Server if you do. However there are already scripts that do this, and more. I use the code created by Jason Strate (MVP). a lot. I'd recommend his blog series on indexing where he takes you through te development of his script and how it came about.
http://www.jasonstrate.com/2009/04/analyze-this-your-indexes-analysis-part-1/. If you want to write your own after you have seen this then you should have all the information you need to get started from that blog.
There are lots of resources available about indexing strategies. I would suggest reading Gail Shaw's series on this at , where she also has a link to Kimberly L. Tripp's duplicate indexes post I wouldn't be automatically deleting duplicate indexes, without first fully understanding if the indexes are truly duplicate.