Possible duplicate indexes

When a table has multiple indexes defined on the same columns, it produces duplicate indexes that waste space and have a negative impact on performance.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

When a table has multiple indexes defined on the same columns, it produces duplicate indexes that waste space and have a negative impact on performance. This metric measures the number of possible duplicate indexes per database. Use it if you want to monitor when a duplicate index is created or to find whether there is a duplicated index in your database.

Further analysis is necessary to identify how many of the indexes found by this metric are really duplicated, and you can use the query below to find exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. You can consider dropping the indexes that are definitely duplicates.

[sql]– Exactly duplicated 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,
(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 DB_NAME() AS ‘DBName’,
OBJECT_SCHEMA_NAME(c1.id) + ‘.’
+ OBJECT_NAME(c1.id) AS ‘TableName’,
c1.name + CASE c1.indid
WHEN 1 THEN ‘ (clustered index)’
ELSE ‘ (nonclustered index)’
END AS ‘IndexName’,
c2.name + CASE c2.indid
WHEN 1 THEN ‘ (clustered index)’
ELSE ‘ (nonclustered index)’
END AS ‘ExactDuplicatedIndexName’
FROM indexcols AS c1
INNER JOIN indexcols AS c2
ON
c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols = c2.cols
AND c1.inc = c2.inc;[/sql]

Note: Be very careful before dropping an index. Check that the index is really not used (sys.dm_db_index_usage_stats can help with this), and that applications are not using the index on hints. Even if there is a duplicate based on the key columns, there are occasionally valid reasons for having a duplicate, for example, a clustered index and a non-clustered index can use the same key columns.

For more information, see http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-find-duplicate-indexes/ and http://www.sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx.

Possible duplicate indexes, 4.3 out of 5 based on 4 ratings

Metric definition

Name

Possible duplicate indexes

Description

This metric measures the number of possible duplicate indexes per database. Use it if you want to monitor when a duplicate index is created or to find whether there is a duplicate index in your database.

When a table has multiple indexes defined on the same columns, it produces duplicate indexes that waste space and have a negative impact on performance. Further analysis is necessary to identify how many of the indexes found by this metric are really duplicated, and you can use the query below to find exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. You can consider dropping the indexes that are definitely duplicates.

-- Exactly duplicated 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,
                (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 DB_NAME() AS 'DBName',
      OBJECT_SCHEMA_NAME(c1.id) + '.'
        + OBJECT_NAME(c1.id) AS 'TableName',
      c1.name + CASE c1.indid
                  WHEN 1 THEN ' (clustered index)'
                  ELSE ' (nonclustered index)'
                END AS 'IndexName',
      c2.name + CASE c2.indid
                  WHEN 1 THEN ' (clustered index)'
                  ELSE ' (nonclustered index)'
                END AS 'ExactDuplicatedIndexName'
    FROM indexcols AS c1 
    INNER JOIN indexcols AS c2
    ON
      c1.id = c2.id
      AND c1.indid < c2.indid
      AND c1.cols = c2.cols
      AND c1.inc = c2.inc;

Note: Be very careful before dropping an index. Check that the index is really not used (sys.dm_db_index_usage_stats can help with this), and that applications are not using the index on hints. Even if there is a duplicate based on the key columns, there are occasionally valid reasons for having a duplicate, for example, a clustered index and a non-clustered index can use the same key columns.

For more information, see http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-find-duplicate-indexes/ and http://www.sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

1

Collection frequency

86400

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Possible duplicate indexes

Description

This alert is raised when the number of possible duplicate indexes per database is above the specified threshold. When a table has multiple indexes defined on the same columns, it produces duplicate indexes that waste space and have a negative impact on performance.

Further analysis is necessary to identify how many of the indexes found by this metric are really duplicated, and you can use the query below to find exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. You can consider dropping the indexes that are definitely duplicates.

-- Exactly duplicated 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,
                (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 DB_NAME() AS 'DBName',
      OBJECT_SCHEMA_NAME(c1.id) + '.'
        + OBJECT_NAME(c1.id) AS 'TableName',
      c1.name + CASE c1.indid
                  WHEN 1 THEN ' (clustered index)'
                  ELSE ' (nonclustered index)'
                END AS 'IndexName',
      c2.name + CASE c2.indid
                  WHEN 1 THEN ' (clustered index)'
                  ELSE ' (nonclustered index)'
                END AS 'ExactDuplicatedIndexName'
    FROM indexcols AS c1 
    INNER JOIN indexcols AS c2
    ON
      c1.id = c2.id
      AND c1.indid < c2.indid
      AND c1.cols = c2.cols
      AND c1.inc = c2.inc;

Note: Be very careful before dropping an index. Check that the index is really not used (sys.dm_db_index_usage_stats can help with this), and that applications are not using the index on hints. Even if there is a duplicate based on the key columns, there are occasionally valid reasons for having a duplicate, for example, a clustered index and a non-clustered index can use the same key columns.

For more information, see http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-find-duplicate-indexes/ and http://www.sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx.

Supported SQL Server versions: SQL Server 2005 or later

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:50
Medium:10
Low:0

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled