http:\/\/blogs.msdn.com\/b\/sqlcat\/archive\/2006\/02\/13\/531339.aspx<\/a>.<\/p>\r\nIf you want to see which specific indexes haven't been used for the last month, run this query:<\/p>\r\n
SELECT db_name() as dbname,\r\n o.name as tablename,\r\n i.name as indexname,\r\n i.index_id,\r\n user_seeks + user_scans + user_lookups as total_reads,\r\n user_updates as total_writes,\r\n (SELECT SUM(p.rows)\r\n FROM sys.partitions p\r\n WHERE p.index_id = s.index_id\r\n AND s.object_id = p.object_id) as number_of_rows, \r\n s.last_user_lookup,\r\n s.last_user_scan,\r\n s.last_user_seek\r\n FROM sys.indexes i\r\n INNER JOIN sys.objects o\r\n ON i.object_id = o.object_id\r\n LEFT OUTER JOIN sys.dm_db_index_usage_stats s\r\n ON i.index_id = s.index_id\r\n AND s.object_id = i.object_id\r\n WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1\r\n AND ISNULL(s.database_id, DB_ID()) = DB_ID()\r\n AND ( \r\n isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND\r\n isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND\r\n isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())\r\n )\r\n ORDER BY total_reads DESC;<\/pre>\r\nNote:<\/strong> If you identify an index that hasn't been used in the last month, check whether it really is unused or whether an application is not using the index on hints. The DMV counter:[sql]sys.dm_db_index_usage_stats[\/sql] can help you with this, but also bear in mind that these DMV counters are initialized to empty whenever the SQL Server MSSQLSERVER service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.","tsql":"\t\r\nSELECT COUNT(*) AS cnt\r\nFROM sys.indexes i\r\n LEFT OUTER JOIN sys.dm_db_index_usage_stats s\r\n ON i.index_id = s.index_id\r\n AND s.object_id = i.object_id\r\nWHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1\r\n AND ISNULL(s.database_id, DB_ID()) = DB_ID()\r\n AND ( ISNULL(s.last_user_seek, '19000101') < DATEDIFF(month,\r\n -1, GETDATE())\r\n AND ISNULL(s.last_user_scan, '19000101') < DATEDIFF(month,\r\n -1, GETDATE())\r\n AND ISNULL(s.last_user_lookup, '19000101') < DATEDIFF(month,\r\n -1, GETDATE())\r\n );","instances":true,"frequency":86400,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Number of unused indexes is too high","alertdescription":"
This alert is raised if the number of indexes that haven't been used for the last month exceeds the set thresholds. If you want to see which specific indexes haven't been used, run this query:<\/p>\r\n
SELECT db_name() as dbname,\r\n o.name as tablename,\r\n i.name as indexname,\r\n i.index_id,\r\n user_seeks + user_scans + user_lookups as total_reads,\r\n user_updates as total_writes,\r\n (SELECT SUM(p.rows)\r\n FROM sys.partitions p\r\n WHERE p.index_id = s.index_id\r\n AND s.object_id = p.object_id) as number_of_rows, \r\n s.last_user_lookup,\r\n s.last_user_scan,\r\n s.last_user_seek\r\n FROM sys.indexes i\r\n INNER JOIN sys.objects o\r\n ON i.object_id = o.object_id\r\n LEFT OUTER JOIN sys.dm_db_index_usage_stats s\r\n ON i.index_id = s.index_id\r\n AND s.object_id = i.object_id\r\n WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1\r\n AND ISNULL(s.database_id, DB_ID()) = DB_ID()\r\n AND ( \r\n isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND\r\n isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND\r\n isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())\r\n )\r\n ORDER BY total_reads DESC;<\/pre>\r\nNote:<\/strong> If you identify an index that hasn't been used in the last month, check whether it really is unused or whether an application is not using the index on hints. The DMV counter:[sql]sys.dm_db_index_usage_stats[\/sql] can help you with this, but also bear in mind that these DMV counters are initialized to empty whenever the SQL Server MSSQLSERVER service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.\r\n","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. ","alertdetailquery":"SELECT\r\n CAST(\r\n STRING_AGG (detail, ', ') WITHIN GROUP (\r\n ORDER BY\r\n detail\r\n ) AS NVARCHAR (MAX)\r\n )\r\nFROM\r\n (\r\n SELECT\r\n TOP 20 OBJECT_SCHEMA_NAME (i.object_id) + '.' + OBJECT_NAME (i.object_id) + '.' + ISNULL (i.name, '') AS detail\r\n FROM\r\n sys.indexes i\r\n LEFT OUTER JOIN sys.dm_db_index_usage_stats s ON i.index_id = s.index_id\r\n AND s.object_id = i.object_id\r\n WHERE\r\n OBJECTPROPERTY (i.object_id, 'IsUserTable') = 1\r\n AND ISNULL (s.database_id, DB_ID ()) = DB_ID ()\r\n AND (\r\n ISNULL (s.last_user_seek, '19000101') < DATEDIFF (month, -1, GETDATE ())\r\n AND ISNULL (s.last_user_scan, '19000101') < DATEDIFF (month, -1, GETDATE ())\r\n AND ISNULL (s.last_user_lookup, '19000101') < DATEDIFF (month, -1, GETDATE ())\r\n )\r\n ORDER BY\r\n detail\r\n ) AS unused;","targetentitytype":0}