(OLD): Removing duplicate indexes

IMPORTANT UPDATE:

PLEASE REVIEW THIS POST FOR THE UPDATED SCRIPTS: USE THESE: Updates to SQLskills Index Procedures

****************************************************

SQL Server has a horrible habit of letting bad habits move forward (for the sake of backward compatibility [e.g. autoshrink]). And, I do understand *some* of the reasons for allowing some of these “features/options” to move forward. However, there are a few that frustrate me and I’ve talked about this one quite often.

SQL Server lets you create completely redundant and totally duplicate indexes.

Yes, you can even create the same index 999 times! Try it:

<br />SET NOCOUNT ON<br />GO</p><p>CREATE TABLE TestTable<br />(<br />col1 int identity<br />);<br />GO</p><p>DECLARE @IndexID smallint,<br />@ExecStr nvarchar(500);</p><p>SELECT @IndexID = 1;</p><p>WHILE @IndexID &lt;= 999<br />BEGIN<br />SELECT @ExecStr = 'CREATE INDEX [Test' + right('00' + convert(varchar(5), @IndexID), 3) + '] ON TestTable (col1)';</p><p>EXEC(@ExecStr);</p><p>SELECT @IndexID = @IndexID + 1;<br />END<br />GO</p><p>SELECT count(*)<br />FROM sys.indexes<br />WHERE object_id = object_id('TestTable');<br />GO</p><p>--DROP TABLE TestTable<br />GO<br />

For somewhat obvious reasons having 999 duplicate indexes does seem completely useless… so, why does SQL Server allow it at all? And, what’s the backward compatibility argument? Well, I’ve written about that before on our SQL Server Magazine Q&A blog (Kimberly & Paul – Questions Answered): Why SQL Server Lets You Create Redundant Indexes.

Having said that – and, for those of you that are only checking YOUR code, you don’t use index hints and/or you don’t care about backward compat – then, you can use my proc to help you find duplicate indexes (and drop them)! It’s all based on my “sp_helpindex rewrites” stored procedures but I had to do some “tweaking” to get the procedures to understand that the order of the columns in the INCLUDE list are irrelevant (in terms of order). So, you can’t use the sps as written. They give you the exact (and correct) structure – and, that’s exactly how SQL Server stores them. For example, the following two indexes have a different structure. However, they are completely redundant and you do NOT need both.

<br />CREATE INDEX Test1 ON dbo.Member (LastName)<br />INCLUDE (FirstName, MiddleInitial);</p><p>CREATE INDEX Test2 ON Member (LastName)<br />INCLUDE (MiddleInitial, FirstName);<br />GO<br />

Here’s what you need:

  1. First, my modified sp_ that exposes all of the columns in all levels of the index. This one does NOT replace the one needed by sp_SQLskills_SQL2008_helpindex. You need both. This version is used to reorder the INCLUDEd columns so that the “finddupes” proc can remove indexes that are different solely because of the order of the columns in the INCLUDE clause (which is irrelevant for navigation/index usage).

    Install this one first:
    20110715_sp_SQLskills_ExposeColsInIndexLevels_INCLUDE_UNORDERED.sql (6.95 kb)

  2. Second, my modifed sp_helpindex that is used SOLELY by finddupes. Basically, it’s just modified to use the UNORDERED version of “exposing the columns” in the nonclustered indexes. Again, this does NOT replace the other sp – you need both.

    Install this one second:
    20110715_sp_SQLskills_SQL2008_finddupes_helpindex.sql (10.95 kb)

  3. Finally, here’s the “finddupes” procedure

    Install this one last:
    20110720_sp_SQLskills_SQL2008_finddupes.sql (5.30 kb)

  4. NOTES: At some point in the not-too-distant future I’ll modify this for the following:

    1) One set of code that will work for 2005/2008
    2) I’ll combine the two versions of the “expose columns in index levels” so that there’s only one.
    3) I’ll make this work for indexed views

Finally, how can you use this:

To find the duplicates for just one table – you can use one-part or two-part naming:

<br />USE [AdventureWorks2008];<br />GO</p><p>EXECUTE sp_SQLskills_SQL2008_finddupes @ObjName='Production.Document';<br />GO<br />

OR… to find all duplicates in the entire database:

<br />USE [AdventureWorks2008];<br />GO</p><p>EXECUTE sp_SQLskills_SQL2008_finddupes;<br />GO<br />

OR… if you’re really motivated, you can run it for all databases – but, before you do – see Aaron Bertrand’s comment to this post for his replacement for sp_msforeachdb and use that instead!!

<br />EXECUTE sp_msforeachdb 'USE ?; exec sp_SQLskills_SQL2008_finddupes';<br />GO<br />

Have fun and let me know how many duplicates you find?
I found one in AdventureWorks2008 and a few in Northwind. I suspect you’ll find a few! This isn’t something that will find tons of indexes (to drop) but if there are even a couple of dupes (especially on large tables) you’ll have savings in database modification statements, logging, caching, maintenance, storage (and therefore backups, etc.) and this is why dupes are HORRIBLE! I do wish that SQL Server had an option to prevent their being created! Maybe someday?!

Enjoy,

kt

28 thoughts on “(OLD): Removing duplicate indexes

  1. Sorry but I’ve been kind of an sp_msforeachdb watchdog lately. Two quick points:

    (1) you should enclose [] around ?, e.g.

    EXEC sp_msforeachdb ‘USE [?]; …’;

    This will prevent the script from breaking on those pesky SharePoint databases, databases someone named with spaces or starting with a number, that kind of thing. I think the only failing point is if the database has [ or ] in the name, I forget which, but I hope you don’t come across any of those. :-)

    (2) sp_msforeachdb uses a cursor type that is very sensitive (to what, I’m not sure – locks on the database, changes to sys.databases (or the underlying structure that feeds it), who knows) – and there are no warnings or errors when it skips databases. There is little rhyme or reason to when the symptom manifests except that I tend to see it most often on either very busy systems or systems with a lot of databases.

    I wrote a replacement here which, in addition to not seeing it exhibit this random skipping symptom, also provides more flexibility by allowing several parameters to limit the scope to certain databases: http://mssqltips.com/tip.asp?tip=2201

    It’s not exactly the thing you want to include in every code sample of every blog post, but I’ve learned to be very cautious about recommending sp_msforeachdb – and I’m becoming more and more reluctant about its use every time I hear about another user who sees the behavior I’m talking about (and who knows how many people *aren’t* noticing that their sp_msforeachdb script is not always backing up all of their databases, for example).

    It would be great to file a bug against this, but since it is undocumented and unsupported, I don’t have any hopes that anyone is going to do anything about it, so I will try to be persistent about recommending my version (or something like it) in its place. I had a fruitless request here that went nowhere:

    http://connect.microsoft.com/SQLServer/feedback/details/264677/sp-msforeachdb-provide-supported-documented-version

    Cheers,
    Aaron

  2. Hi,

    VERY minor point, but your final "finddupes" script appears to suffer from the classic copy and paste error, meaning it’s trying to mark the wrong SP as a system object…

    Would make the sp_msforeachdb approach tricky :-)

  3. Isn’t using "sp_" as your stored proc prefix frowned upon and not recommended by Microsoft? ….

  4. Regarding your point that SQL Server should disallow this are duplicate indexes always totally useless? In SKUs without online index rebuilds just thinking that maybe the temporary creation of a dupe NCI then dropping the original might potentially have less impact than a rebuild on concurrent reading transactions in terms of reducing the length of time a Sch-M lock is held. Quite a speculative thought though. I’ve never tried this.

  5. Regarding the sp_ prefix. For user-defined procedures in user databases: YES, it is frowned upon and not recommended. However, for system procedures that live in master (and are marked as a system object) then there’s a benefit. And, there’s a long history to sp_ procedures as well as how they’ve worked has changed over the versions. In earlier version you didn’t need to mark them as system objects – the name alone (sp_) and its creation in master meant that you could execute it in ANY database without fully qualifying it (exec master.dbo.sp_help isn’t necessary). And, the behavior of an sp_ is different too (in how/where it looks for objects). So, the long story short is that yes – it should be avoided for EVERYTHING except user-defined system procedures that are created in master!

    Great question!

    Cheers,
    kt

  6. Only in SKUs where SQL Server supports online index operations does SQL Server support online index create. So, creating another index to replace an index (so that you can avoid a rebuild) doesn’t actually work! It’s a good thought but requires the same locks (on the base table) as the rebuild.

    Hope this helps!
    kt

  7. I just got this in an email but I had to post it:

    [quote]You rock!

    I just identified 56 duplicates in one database, all in tables that are replicated to another server. That particular publication is known to have performance issues anyway, so clearing these out is sure to be a good start.

    THANK YOU!
    [/quote]

    That’s awesome! And, it will definitely help with things like replication and general performance (from logging as well as caching/lookups). So, it’s all good! I knew there would be a few out there with a bunch!

    Cheers,
    kt

  8. I’m getting the following error when I try to run it on the entire database:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘]’.
    Msg 105, Level 15, State 1, Line 1
    Unclosed quotation mark after the character string ”.

    I thought about troubleshooting it but – well, it’s not complex at all! ;)

  9. hmmm…sorry, it seems to happen only in our monster database, the others are fine. However, it returns a message saying "Database: xxx has NO duplicate indexes." even if I provide a table name – is it doing the whole database? Or is that just the message it’s returning?

    This is totally awesome – I was going to write something like this myself but found this just in time!

  10. This is a very handy tool, but I would caution people to double-check that each index is in fact a duplicate before executing the recommended DROP INDEX results.

    While it did find many duplicates, I have encountered a case where it shows that an index is a duplicate, where I cannot find any other index on the table that is in fact a duplicate? Let me know if you’d like some specific information to help debug this.

  11. Kimberly,

    Question about the tree and leaf storage with regards to unnecessary indexes.

    Before reading your blog, I wrote my own superfluous index finder which only checked to see if the keys in a given index were repeated as the first keys in another index on the same table. Our company recently released our first software version that didn’t support SQL2K, so we haven’t started using Included Columns yet. I’m also not paying attention to which index is clustered, and didn’t even know about the concept of keys you don’t specify which are included in the tree of non-unique indexes until I started reading your blog. So clearly, if an index is found by my script, there is no guarantee that it is unneeded.

    However, in researching some indexes (using sp_helpindex9) that I found which didn’t show up in your duplicate index finder, I had the following question:

    For a given table in our database:
    – The primary key is on a numeric (Identity) column and is clustered.
    – A unique index also exists in that table on the same column, and has no included columns.
    – Therefore, the columns in the tree for both indexes are the same (the single Identity column), although the columns in the leaf are different in that the primary key (clustered) includes all columns in the leaf, whereas the unique index only has the identity column.

    Would there ever be a performance advantage to having the unique, non-clustered index in addition to the clustered primary key on the same column? I guess what I’m asking is, when an index is searched, is only the tree touched, or does the size of the leaf have an impact?

    Thanks, Brad

  12. Ooops! Mistake in my last post. As I was re-reading it I found in the end of the next-to-last paragraph…

    "… primary key (clustered) includes all columns in the [b][u]table[/u][/b], whereas the unique index only has the identity column."

    Is that correctable?

  13. If fixed the code for 2005 by removing column [filter_description] in _helpindex and _finddupes

    I get more results when I order by column names in sp_SQLskills_ExposeColsInIndexLevels_INCLUDE_UNORDERED
    They are not exactly the same, but it’s also about redundantness and keeping nr of indices on a table below MS-access compatibility treshold.

    declare mycursor cursor for
    select column_id, column_name, is_descending_key
    from #nonclus_keys
    where is_included_column = 0
    /*ADDED*/ORDER BY column_name/*ADDED*/
    AND

    declare mycursor cursor for
    select column_id, column_name, is_descending_key from #clus_keys
    where column_id not in (select column_id from #nonclus_keys
    where is_included_column = 0)
    /*ADDED*/ORDER BY #clus_keys.column_name/*ADDED*/

    I still have to execute the _finddupes code in a query window and cant exec the proc (+32 nesting level) though

    There’s duplicates and there’s reduntant indices
    An index with key [A],[B] and includes [C],[D] is redundant if there is also an index with key [A],[B] and includes [C],[D],[E]

    Besides duplicate and redundant indices there’s also duplicate and redundant statistics

    And you can even compare statistics with indices

    USE [GPS]
    GO
    /****** Object: StoredProcedure [tools].[clean_statistics] Script Date: 03/22/2013 16:54:43 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO –tools.clean_statistics_V2 1
    ALTER PROCEDURE [tools].[clean_statistics_V2] ( @debug BIT = 0 )
    AS
    BEGIN
    DECLARE @t TABLE
    (
    RowID INT NOT NULL
    IDENTITY(1, 1)
    PRIMARY KEY ,
    tebewerken VARCHAR(500) ,
    waarde VARCHAR(500)
    )
    IF @debug > 0
    SELECT l1.tablename ,
    l1.indexname ,
    l1.col1 + ISNULL(‘;’ + l1.col2, ”) + ISNULL(‘;’ + l1.col3, ”) + ISNULL(‘;’ + l1.col4, ”) + ISNULL(‘;’ + l1.col5, ”) + ISNULL(‘;’
    + l1.col6, ”)
    + ISNULL(‘;’ + l1.col7, ”) + ISNULL(‘;’ + l1.col8, ”) + ISNULL(‘;’ + l1.col9, ”) + ISNULL(‘;’ + l1.col10, ”) + ISNULL(‘;’ + l1.col11, ”)
    + ISNULL(‘;’ + l1.col12, ”) + ISNULL(‘;’ + l1.col13, ”) + ISNULL(‘;’ + l1.col14, ”) + ISNULL(‘;’ + l1.col15, ”) + ISNULL(‘;’ + l1.col16,
    ”) l1 ,
    l2.indexname ,
    l2.col1 + ISNULL(‘;’ + l2.col2, ”) + ISNULL(‘;’ + l2.col3, ”) + ISNULL(‘;’ + l2.col4, ”) + ISNULL(‘;’ + l2.col5, ”) + ISNULL(‘;’
    + l2.col6, ”)
    + ISNULL(‘;’ + l2.col7, ”) + ISNULL(‘;’ + l2.col8, ”) + ISNULL(‘;’ + l2.col9, ”) + ISNULL(‘;’ + l2.col10, ”) + ISNULL(‘;’ + l2.col11, ”)
    + ISNULL(‘;’ + l2.col12, ”) + ISNULL(‘;’ + l2.col13, ”) + ISNULL(‘;’ + l2.col14, ”) + ISNULL(‘;’ + l2.col15, ”) + ISNULL(‘;’ + l2.col16,
    ”) l2
    FROM tools.sysvw_statistics_list l1
    INNER JOIN tools.sysvw_statistics_list l2
    ON l1.tablename = l2.tablename
    AND l1.indexname l2.indexname
    AND l1.[columncount] < l2.columncount
    AND l1.col1 = l2.col1
    AND (
    l1.col2 IS NULL
    OR l2.col2 IS NULL
    OR l1.col2 = l2.col2
    )
    AND (
    l1.col3 IS NULL
    OR l2.col3 IS NULL
    OR l1.col3 = l2.col3
    )
    AND (
    l1.col4 IS NULL
    OR l2.col4 IS NULL
    OR l1.col4 = l2.col4
    )
    AND (
    l1.col5 IS NULL
    OR l2.col5 IS NULL
    OR l1.col5 = l2.col5
    )
    AND (
    l1.col6 IS NULL
    OR l2.col6 IS NULL
    OR l1.col6 = l2.col6
    )
    AND (
    l1.col7 IS NULL
    OR l2.col7 IS NULL
    OR l1.col7 = l2.col7
    )
    AND (
    l1.col8 IS NULL
    OR l2.col8 IS NULL
    OR l1.col8 = l2.col8
    )
    AND (
    l1.col9 IS NULL
    OR l2.col9 IS NULL
    OR l1.col9 = l2.col9
    )
    AND (
    l1.col10 IS NULL
    OR l2.col10 IS NULL
    OR l1.col10 = l2.col10
    )
    AND (
    l1.col11 IS NULL
    OR l2.col11 IS NULL
    OR l1.col11 = l2.col11
    )
    AND (
    l1.col12 IS NULL
    OR l2.col12 IS NULL
    OR l1.col12 = l2.col12
    )
    AND (
    l1.col13 IS NULL
    OR l2.col13 IS NULL
    OR l1.col13 = l2.col13
    )
    AND (
    l1.col14 IS NULL
    OR l2.col14 IS NULL
    OR l1.col14 = l2.col14
    )
    AND (
    l1.col15 IS NULL
    OR l2.col15 IS NULL
    OR l1.col15 = l2.col15
    )
    AND (
    l1.col16 IS NULL
    OR l2.col16 IS NULL
    OR l1.col16 = l2.col16
    )
    ORDER BY l1.tablename ,
    l1.indexname

    INSERT INTO @t
    (
    tebewerken ,
    waarde
    )
    /*SELECT*/ SELECT l1.tablename ,
    l1.indexname
    FROM tools.sysvw_statistics_list l1
    INNER JOIN tools.sysvw_statistics_list l2
    ON l1.tablename = l2.tablename
    AND l1.indexname l2.indexname
    AND l1.[columncount] < l2.columncount
    AND l1.col1 = l2.col1
    AND (
    l1.col2 IS NULL
    OR l2.col2 IS NULL
    OR l1.col2 = l2.col2
    )
    AND (
    l1.col3 IS NULL
    OR l2.col3 IS NULL
    OR l1.col3 = l2.col3
    )
    AND (
    l1.col4 IS NULL
    OR l2.col4 IS NULL
    OR l1.col4 = l2.col4
    )
    AND (
    l1.col5 IS NULL
    OR l2.col5 IS NULL
    OR l1.col5 = l2.col5
    )
    AND (
    l1.col6 IS NULL
    OR l2.col6 IS NULL
    OR l1.col6 = l2.col6
    )
    AND (
    l1.col7 IS NULL
    OR l2.col7 IS NULL
    OR l1.col7 = l2.col7
    )
    AND (
    l1.col8 IS NULL
    OR l2.col8 IS NULL
    OR l1.col8 = l2.col8
    )
    AND (
    l1.col9 IS NULL
    OR l2.col9 IS NULL
    OR l1.col9 = l2.col9
    )
    AND (
    l1.col10 IS NULL
    OR l2.col10 IS NULL
    OR l1.col10 = l2.col10
    )
    AND (
    l1.col11 IS NULL
    OR l2.col11 IS NULL
    OR l1.col11 = l2.col11
    )
    AND (
    l1.col12 IS NULL
    OR l2.col12 IS NULL
    OR l1.col12 = l2.col12
    )
    AND (
    l1.col13 IS NULL
    OR l2.col13 IS NULL
    OR l1.col13 = l2.col13
    )
    AND (
    l1.col14 IS NULL
    OR l2.col14 IS NULL
    OR l1.col14 = l2.col14
    )
    AND (
    l1.col15 IS NULL
    OR l2.col15 IS NULL
    OR l1.col15 = l2.col15
    )
    AND (
    l1.col16 IS NULL
    OR l2.col16 IS NULL
    OR l1.col16 = l2.col16
    )
    ORDER BY l1.tablename ,
    l1.indexname
    DECLARE @rowcount INT ,
    @counter INT
    SET @rowcount = ( SELECT COUNT(*)
    FROM @t )
    SET @counter = 1
    WHILE @counter 0 )
    AND ( INDEXPROPERTY(tbl.id, idx.name, ‘IsStatistics’) = 1 )
    AND (
    NOT (
    idx.name LIKE ‘SSMA_PK%’
    OR idx.name LIKE ‘PK%’
    )
    )
    AND ( INDEXPROPERTY(tbl.id, idx.name, ‘IsHypothetical’) = 0 )

    go

    ALTER VIEW [tools].[sysvw_index_list]
    AS
    SELECT tbl.name AS TableName ,
    idx.name AS IndexName ,
    INDEX_COL(tbl.name, idx.indid, 1) AS col1 ,
    INDEX_COL(tbl.name, idx.indid, 2) AS col2 ,
    INDEX_COL(tbl.name, idx.indid, 3) AS col3 ,
    INDEX_COL(tbl.name, idx.indid, 4) AS col4 ,
    INDEX_COL(tbl.name, idx.indid, 5) AS col5 ,
    INDEX_COL(tbl.name, idx.indid, 6) AS col6 ,
    INDEX_COL(tbl.name, idx.indid, 7) AS col7 ,
    INDEX_COL(tbl.name, idx.indid, 8) AS col8 ,
    INDEX_COL(tbl.name, idx.indid, 9) AS col9 ,
    INDEX_COL(tbl.name, idx.indid, 10) AS col10 ,
    INDEX_COL(tbl.name, idx.indid, 11) AS col11 ,
    INDEX_COL(tbl.name, idx.indid, 12) AS col12 ,
    INDEX_COL(tbl.name, idx.indid, 13) AS col13 ,
    INDEX_COL(tbl.name, idx.indid, 14) AS col14 ,
    INDEX_COL(tbl.name, idx.indid, 15) AS col15 ,
    INDEX_COL(tbl.name, idx.indid, 16) AS col16 ,
    idx.dpages ,
    idx.used ,
    idx.rowcnt ,
    CASE WHEN INDEX_COL(tbl.name, idx.indid, 2) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 3) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 4) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 5) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 6) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 7) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 8) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 9) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 10) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 11) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 12) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 13) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 14) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 15) IS NULL THEN 0
    ELSE 1
    END + CASE WHEN INDEX_COL(tbl.name, idx.indid, 16) IS NULL THEN 0
    ELSE 1
    END AS columncount
    FROM sys.sysindexes AS idx
    INNER JOIN sys.sysobjects AS tbl
    ON idx.id = tbl.id
    WHERE ( idx.indid > 0 )
    AND ( INDEXPROPERTY(tbl.id, idx.name, ‘IsStatistics’) = 0 )
    AND (
    NOT (
    idx.name LIKE ‘SSMA_PK%’
    OR idx.name LIKE ‘PK%’
    )
    )
    AND ( INDEXPROPERTY(tbl.id, idx.name, ‘IsHypothetical’) = 0 )

  14. Hi Kimberly, I noticed that this script suggests to drop a duplicate index even if the duplicate found is a primary key. The drop statement in that case results in the error – Msg 3723, Level 16, State 4, Line 1
    An explicit DROP INDEX is not allowed on index ‘dbo.TOrderItems.PK_TOrderItems’. It is being used for PRIMARY KEY constraint enforcement.

    The other index was the index that I actually dropped as this was not being used for pk enforcement.

    It would also be useful, if you do write an update to these scripts, to output the name of the other index (or multiple indexes in the case where more than 1 dup was found).

    Thanks
    Mike

  15. Kimberly,

    I know it’s been a long time since you posted this, but I’ve got an issue with a “somewhat” duplicate keys, and after a lot of searching, cannot find an answer. The situation is, I’ve inherited a database that’s in fairly good shape… most of the tables have primary clustered identity as keys (which is what we want for these tables. However a hand full of the tables have the clustered index separate from the primary key unique index. Is there any way to “move” the primary key be or use the clustered index?

    Thanks,

    Steve

    1. You can change the definition of a clustering key but not a primary key. Unfortunately you’ll have to drop / recreate. And, that also means that you’ll have to drop all of the foreign keys. It’s a mess actually. This is part of the reason that it’s so important to have well-defined these early on. Sorry I don’t have better news for you. :-(

      hth,
      k

  16. Hi Kim,
    I am a Junior DBA. My DBs seem to have multiple duplicate indexes on them too. I came across your blog while looking for the solution. The scrips only lists 2005/2008 as the applicable version of SQL. I just wanted to know if it is applicable for SQL Server 2012 and 2014 also. It does seem to be applicable but I wanted to clarify it with you.

    Thank you
    Prashoon

  17. Hi Kim,

    I am new to DBA stuffs. As I executed the sproc, I found that it tends to show duplication of index if the Table has a composite primary key. Please let me know if I am wrong. It is just my observation. May be I am missing something.

    John

    1. Hey there John – Can you give me more information about the specific indexes that you think are duplicate? The number of columns shouldn’t matter but maybe there’s something I missed?

      Thanks!
      k

Leave a Reply to Kimberly Tripp Cancel reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.