SQL SERVER – Query to Find Duplicate Indexes – Script to Find Redundant Indexes

I was recently delivering session on Performance Tuning subject. I was asking if there is any harm having duplicate indexes. Of course, duplicate indexes are nothing but overhead on the database system. Database system has to maintain two sets of indexes when it has to do update, delete, insert on the table which has duplicate indexes. There is also a possibility that indexes are overlapped.

For example,

Index1 have Col1, Col2, Col3 but Index2 have Col1,Col2,Col3,Col4,Col5. Here Index1 and Index2 are overlapping and there is no need of Index1, which should be removed.

Following is the script which does the same task. You can run the script, get duplicate indexes and overlapping indexes. Carefully review each of them first and test this by deploying on your development server (not on production). If this script has any issue, do write back and I will do necessary modifications.

;WITH MyDuplicate AS (SELECT
Sch.[name] AS SchemaName,
Obj.[name] AS TableName,
Idx.[name] AS IndexName,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
FROM sys.indexes Idx
INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id] INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id] WHERE index_id > 0)
SELECT    MD1.SchemaName, MD1.TableName, MD1.IndexName,
MD2.IndexName AS OverLappingIndex,
MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
FROM MyDuplicate MD1
INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
AND MD1.indexname <> MD2.indexname
AND MD1.Col1 = MD2.Col1
AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
AND (
MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
AND (
MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
AND (
MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
AND (
MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
AND (
MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
AND (
MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
AND (
MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
AND (
MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
AND (
MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
AND (
MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
AND (
MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
AND (
MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
AND (
MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
AND (
MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
ORDER BY MD1.SchemaName,MD1.TableName,MD1.IndexName

Update: Additionally, please read the comment left by Paul Randal, why there is more modifications required to this query.

This is version one query. Please provide all your suggestions and based on the same, I will have final query posted on blog.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31
Next Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31

Related Posts

15 Comments. Leave new

  • Hey Pinal – You don’t consider INCLUDEd columns, ASC/DESC, or nonclustered indexes that have the same keys as clustereds but are far narrower. Dangerous query to have people run. Left-based subsets the same doesn’t mean you can drop the wider one – they may satisfy vastly different queries and dropping the narrow one could seriously affect performance.

    Reply
    • Yes Sir,

      I totally agree. Collecting input to modify this query to reflect the all the suggestions. Your suggestion is very valid and based your your suggestion I will further update the same.

      Reply
  • Clustered and Non-Clustered indexes are not seperated by this script, also PrimaryKeys and other unique indexes could have same overlapping columns. And included columns are not considered as well.

    Reply
  • Paul,

    I’m interested in more information on this comment: “Left-based subsets the same doesn’t mean you can drop the wider one “. Using the example of two indexes (colA,colB,colC) and (colA,colB), assuming that everything else in the index is equal (includes, filters, etc) and that ColC is not very wide.

    Can we say the index 2 is redundant because index 1 includes the same cols as index 2?

    I’d like to know your ‘rule-of-thumb’ approach to this scenario as it’s fairly common.

    Thanks,
    Dave

    Reply
  • @Dave Most likely, if colC is not very wide.

    Reply
  • Hi Pinal,

    Nice article. I tried to run this on my DEV instance and found this interesting thing. I am having same named table with same named primary key column in different Schema. This script display indexes on these columns as duplicates. I believe its not correct.

    Please share you thoughts

    Results:
    SchemaName | TableName | IndexName | OverLappingIndex | Col1
    =========================================================
    E2S | queue | PK_queue | PK_email | Id
    Email | Queue | PK_email | PK_queue | Id

    Reply
  • Pinal,

    Kim Tripp has expended on index duplicate searching & given her own script at: and https://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/

    Dave

    Reply
  • Thanks for posting. I hadn’t really thought to check for duplicate/overlapping indexes before. Was interesting to look into on our dbs.

    Jason

    Reply
  • Girijesh Pandey
    October 2, 2012 11:58 pm

    Hi Pinal,

    Nice explanation!

    We Can also get the exact duplicate index using below query.

    with indexcols as
    (
    select object_id as id, index_id as indid, name,
    (select case keyno when 0 then NULL else colid end as [data()]
    from sys.sysindexkeys as k
    where k.id = i.object_id
    and k.indid = i.index_id
    order by keyno, colid
    for xml path(”)) as cols,
    (select case keyno when 0 then colid else NULL end as [data()]
    from sys.sysindexkeys as k
    where k.id = i.object_id
    and k.indid = i.index_id
    order by colid
    for xml path(”)) as inc
    from sys.indexes as i
    )
    select
    object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’,
    c1.name as ‘index’,
    c2.name as ‘exactduplicate’
    from indexcols as c1
    join indexcols as c2
    on c1.id = c2.id
    and c1.indid < c2.indid
    and c1.cols = c2.cols
    and c1.inc = c2.inc;

    — Script to get Overlapping Indexes
    with indexcols as
    (
    select object_id as id, index_id as indid, name,
    (select case keyno when 0 then NULL else colid end as [data()]
    from sys.sysindexkeys as k
    where k.id = i.object_id
    and k.indid = i.index_id
    order by keyno, colid
    for xml path('')) as cols
    from sys.indexes as i
    )
    select
    object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
    c1.name as 'index',
    c2.name as 'partialduplicate'
    from indexcols as c1
    join indexcols as c2
    on c1.id = c2.id
    and c1.indid < c2.indid
    and (c1.cols like c2.cols + '%'
    or c2.cols like c1.cols + '%') ;

    Hope, it would be really help you to get exact/Overlapping duplicate indexes in any Database.
    Note:- Please double check these scripts on Development server before executing it on Production server.

    Feel free to redirect me in case of any query/questions.

    Regards,
    Girijesh

    Reply
  • Sir, I went to the script download page but was not able to find the script for checking duplicate indexes?

    Reply
  • If Have dupilcated records then one will not be in use, then it should be shown in unused indexes. But this is not happening.

    Reply
  • Does the duplicate with “included” columns script exists for download? The reference by Paul Randall, that is noted comment in the main article? Thanks, saves a lot of time and money

    Reply
  • Hi, Dave! Thanks for sharing!
    Suddenly, this script includes statistics in the results.

    Reply

Leave a Reply

Menu