Number of unused indexes

Use this metric if you want to monitor the number of indexes per database that haven't been used for the last month. Indexes that aren't in use should be removed because they can degrade INSERT, UPDATE and DELETE performance, and they use storage space.

Install metric...

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.

Use this metric if you want to monitor the number of indexes per database that haven’t been used for the last month. Indexes that aren’t in use should be removed because they can degrade INSERT, UPDATE and DELETE performance, and they use storage space.

For more information, see http://blogs.msdn.com/b/sqlcat/archive/2006/02/13/531339.aspx.

If you want to see which specific indexes haven’t been used for the last month, run this query:

[sql]
SELECT db_name() as dbname,
o.name as tablename,
i.name as indexname,
i.index_id,
user_seeks + user_scans + user_lookups as total_reads,
user_updates as total_writes,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.index_id = s.index_id
AND s.object_id = p.object_id) as number_of_rows,
s.last_user_lookup,
s.last_user_scan,
s.last_user_seek
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
ON i.index_id = s.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(o.object_id, ‘IsUserTable’) = 1
AND ISNULL(s.database_id, DB_ID()) = DB_ID()
AND (
isnull(s.last_user_seek, ‘19000101’) < datediff(month, -1, getdate()) AND
isnull(s.last_user_scan, ‘19000101’) < datediff(month, -1, getdate()) AND
isnull(s.last_user_lookup, ‘19000101’) < datediff(month, -1, getdate())
)
ORDER BY total_reads DESC;[/sql]

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

Metric definition

Name

Number of unused indexes

Description

Use this metric if you want to monitor the number of indexes per database that haven't been used for the last month. Indexes that aren't in use should be removed because they can degrade INSERT, UPDATE and DELETE performance, and they use storage space.

For more information, see http://blogs.msdn.com/b/sqlcat/archive/2006/02/13/531339.aspx.

If you want to see which specific indexes haven't been used for the last month, run this query:

SELECT db_name() as dbname,
       o.name as tablename,
       i.name as indexname,
       i.index_id,
       user_seeks + user_scans + user_lookups as total_reads,
       user_updates as total_writes,
       (SELECT SUM(p.rows)
          FROM sys.partitions p
         WHERE p.index_id = s.index_id
           AND s.object_id = p.object_id) as number_of_rows, 
       s.last_user_lookup,
       s.last_user_scan,
       s.last_user_seek
  FROM sys.indexes i
 INNER JOIN sys.objects o
    ON i.object_id = o.object_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON i.index_id = s.index_id
   AND s.object_id = i.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND ISNULL(s.database_id, DB_ID()) = DB_ID()
   AND (   
        isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
       )
 ORDER BY total_reads DESC;

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

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

Number of unused indexes is too high

Description

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:

SELECT db_name() as dbname,
       o.name as tablename,
       i.name as indexname,
       i.index_id,
       user_seeks + user_scans + user_lookups as total_reads,
       user_updates as total_writes,
       (SELECT SUM(p.rows)
          FROM sys.partitions p
         WHERE p.index_id = s.index_id
           AND s.object_id = p.object_id) as number_of_rows, 
       s.last_user_lookup,
       s.last_user_scan,
       s.last_user_seek
  FROM sys.indexes i
 INNER JOIN sys.objects o
    ON i.object_id = o.object_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON i.index_id = s.index_id
   AND s.object_id = i.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND ISNULL(s.database_id, DB_ID()) = DB_ID()
   AND (   
        isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
       )
 ORDER BY total_reads DESC;

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

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