x

Sql Server Find & remove duplicate indexes

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?
more ▼

asked Nov 28, 2011 at 03:47 AM in Default

yaqubonnet gravatar image

yaqubonnet
247 16 17 20

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

There are lots of resources available about indexing strategies.

I would suggest reading Gail Shaw's series on this at http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/, where she also has a link to Kimberly L. Tripp's duplicate indexes post http://www.sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx

I wouldn't be automatically deleting duplicate indexes, without first fully understanding if the indexes are truly duplicate.
more ▼

answered Nov 28, 2011 at 03:58 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Nov 28, 2011 at 03:57 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1841
x84
x14

asked: Nov 28, 2011 at 03:47 AM

Seen: 2038 times

Last Updated: Nov 29, 2011 at 03:44 AM