Possible duplicate indexes

| 1 Comment | 8,734 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 4.3/5 (4 votes cast)

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.

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

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

Metric definition

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

Enter the T-SQL query that will collect data:
-- 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 COUNT(*) AS cnt 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; [/code]
Select instances to collect from:
Select all
Choose databases to collect from:
All user databases
Set collection frequency:
Collect data every day
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
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 thresholds
Default threshold values:
Set the thresholds as follows:
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:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 4.3/5 (4 votes cast)
  • Simon L Richardson

    Hi; great metric thanks very much. I however have noticed it is reporting as possible duplicates for filtered indexes. Am I perhaps misunderstand the use of filtered indexes as I have one that is used when looking at (bit field) Jobs Incomplete and also reporting on completed ones so have the same index twice one filtered when true and a copy but filtered as false?

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)