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.<\/p> \r\n\r\n
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.<\/p>\r\n
-- Exactly duplicated indexes\r\nWITH indexcols\r\n AS (SELECT object_id AS id,\r\n index_id AS indid,\r\n name,\r\n (SELECT CASE keyno\r\n WHEN 0 THEN NULL\r\n ELSE colid\r\n END AS [data()]\r\n FROM sys.sysindexkeys AS k\r\n WHERE k.id = i.object_id\r\n AND k.indid = i.index_id\r\n ORDER BY keyno,\r\n colid\r\n FOR\r\n XML PATH('')\r\n ) AS cols,\r\n (SELECT CASE keyno\r\n WHEN 0 THEN colid\r\n ELSE NULL\r\n END AS [data()]\r\n FROM sys.sysindexkeys AS k\r\n WHERE k.id = i.object_id\r\n AND k.indid = i.index_id\r\n ORDER BY colid\r\n FOR\r\n XML PATH('')\r\n ) AS inc\r\n FROM sys.indexes AS i\r\n )\r\n SELECT DB_NAME() AS 'DBName',\r\n OBJECT_SCHEMA_NAME(c1.id) + '.'\r\n + OBJECT_NAME(c1.id) AS 'TableName',\r\n c1.name + CASE c1.indid\r\n WHEN 1 THEN ' (clustered index)'\r\n ELSE ' (nonclustered index)'\r\n END AS 'IndexName',\r\n c2.name + CASE c2.indid\r\n WHEN 1 THEN ' (clustered index)'\r\n ELSE ' (nonclustered index)'\r\n END AS 'ExactDuplicatedIndexName'\r\n FROM indexcols AS c1 \r\n INNER JOIN indexcols AS c2\r\n ON\r\n c1.id = c2.id\r\n AND c1.indid < c2.indid\r\n AND c1.cols = c2.cols\r\n AND c1.inc = c2.inc;<\/pre>\r\nNote: 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.<\/p>\r\n\r\n\r\n
For more information, see http:\/\/sqlserverpedia.com\/blog\/sql-server-bloggers\/how-to-find-duplicate-indexes\/<\/a> and http:\/\/www.sqlskills.com\/BLOGS\/KIMBERLY\/post\/RemovingDuplicateIndexes.aspx<\/a>.<\/p>","tsql":"-- Exactly duplicated indexes\r\nWITH indexcols\r\n AS (SELECT object_id AS id,\r\n index_id AS indid,\r\n name,\r\n (SELECT CASE keyno\r\n WHEN 0 THEN NULL\r\n ELSE colid\r\n END AS [data()]\r\n FROM sys.sysindexkeys AS k\r\n WHERE k.id = i.object_id\r\n AND k.indid = i.index_id\r\n ORDER BY keyno,\r\n colid\r\n FOR\r\n XML PATH('')\r\n ) AS cols,\r\n (SELECT CASE keyno\r\n WHEN 0 THEN colid\r\n ELSE NULL\r\n END AS [data()]\r\n FROM sys.sysindexkeys AS k\r\n WHERE k.id = i.object_id\r\n AND k.indid = i.index_id\r\n ORDER BY colid\r\n FOR\r\n XML PATH('')\r\n ) AS inc\r\n FROM sys.indexes AS i\r\n )\r\n SELECT COUNT(*) AS cnt\r\n FROM indexcols AS c1 \r\n INNER JOIN indexcols AS c2\r\n ON\r\n c1.id = c2.id\r\n AND c1.indid < c2.indid\r\n AND c1.cols = c2.cols\r\n AND c1.inc = c2.inc;\r\n","instances":true,"frequency":86400,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Possible duplicate indexes","alertdescription":"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.<\/p> \r\n\r\n
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.<\/p>\r\n
-- Exactly duplicated indexes\r\nWITH indexcols\r\n AS (SELECT object_id AS id,\r\n index_id AS indid,\r\n name,\r\n (SELECT CASE keyno\r\n WHEN 0 THEN NULL\r\n ELSE colid\r\n END AS [data()]\r\n FROM sys.sysindexkeys AS k\r\n WHERE k.id = i.object_id\r\n AND k.indid = i.index_id\r\n ORDER BY keyno,\r\n colid\r\n FOR\r\n XML PATH('')\r\n ) AS cols,\r\n (SELECT CASE keyno\r\n WHEN 0 THEN colid\r\n ELSE NULL\r\n END AS [data()]\r\n FROM sys.sysindexkeys AS k\r\n WHERE k.id = i.object_id\r\n AND k.indid = i.index_id\r\n ORDER BY colid\r\n FOR\r\n XML PATH('')\r\n ) AS inc\r\n FROM sys.indexes AS i\r\n )\r\n SELECT DB_NAME() AS 'DBName',\r\n OBJECT_SCHEMA_NAME(c1.id) + '.'\r\n + OBJECT_NAME(c1.id) AS 'TableName',\r\n c1.name + CASE c1.indid\r\n WHEN 1 THEN ' (clustered index)'\r\n ELSE ' (nonclustered index)'\r\n END AS 'IndexName',\r\n c2.name + CASE c2.indid\r\n WHEN 1 THEN ' (clustered index)'\r\n ELSE ' (nonclustered index)'\r\n END AS 'ExactDuplicatedIndexName'\r\n FROM indexcols AS c1 \r\n INNER JOIN indexcols AS c2\r\n ON\r\n c1.id = c2.id\r\n AND c1.indid < c2.indid\r\n AND c1.cols = c2.cols\r\n AND c1.inc = c2.inc;<\/pre>\r\nNote: 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.<\/p>\r\n\r\n\r\n
For more information, see http:\/\/sqlserverpedia.com\/blog\/sql-server-bloggers\/how-to-find-duplicate-indexes\/<\/a> and http:\/\/www.sqlskills.com\/BLOGS\/KIMBERLY\/post\/RemovingDuplicateIndexes.aspx<\/a>.<\/p>\r\nSupported SQL Server versions:<\/strong> SQL Server 2005 or later<\/p>","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":50},"_thresholds_medium":{"selected":true,"value":10},"_thresholds_low":{"selected":true,"value":0},"thresholds_comment":"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.","targetentitytype":0}