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

Click to Download Scripts

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

Click to Download Scripts

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 (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Query to Find Duplicate Indexes – Script to Find Redundant Indexes

  1. 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.

    • 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.

  2. 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.

  3. 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

  4. 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

  5. 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

  6. Pingback: SQL SERVER – SSMS: Index Usage Statistics, Physical Statistics & User Statistics | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s