Number of unused indexes

| 3 Comments | 11,659 views
Categories:

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)

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:

sys.dm_db_index_usage_stats

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

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

Enter the T-SQL query that will collect data:
SELECT COUNT(*) AS cnt FROM sys.indexes i 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(i.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()) ); [/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:
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 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: 0.0/5 (0 votes cast)
  • Svetlana Golovko

    You have to be really careful before you decide to remove the indexes and know your application very well. I think that some important points are missing in this post.

    Index usage statistics reset every time SQL Server restarted, so unless you save output of this query somewhere before server restart (or on regular basis) you shouldn’t just rely on this.

    You can not just remove indexes that has not been in use last month. What if there is process that runs once a quarter and is very resource intensive. It may require this index.

    Also, there could be indexes tied to the unique constraints and primary keys. I would add this as an additional filter to this query:
    AND s.index_id > 1 and i.is_unique=0 AND i.is_primary_key = 0 AND i.is_unique_constraint=0

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • If you do drop an index, be sure to save its definition in case you need to rebuild it. Also, for the next several months, or year, watch for problems related to this dropped index.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Wilfred van Dijk

    I suggest to disable the index first before dropping the index. In this case it’s easy to re-activate the index. Since this script is not analyzing the usage of the index (constraints?) this metric is not reliable in this way

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