15,442 Metrics install automatically if you have Redgate Monitor installed.
If you are using Redgate’s SQL Server monitoring tool, Redgate 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.
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
