Identifying unused non clustered index

Having an Index on a table will indeed be good for the performance of queries. At the same time having too many indexes on a table will hurt the query performance, increases storage requirement and add additional overhead for maintenance.

This article covers how you can identify unused non-clustered indexes in the database.

SQL Server logs the number of times an index was scanned by the user query and time when it was last scanned. Similarly, seek, lookup and updates operations are logged and they are visible through the dynamic management view (DMV). sys.dm_db_index_usage_stats is the view that shows the index usage information and it will be useful for us to determine the index usage.

The following script will list all the non-clustered indexes from the database except the index with the primary key. It shows record count, record size for each index. Provides the index usage count and the last time index accessed and SQL Server start time.

It is important to know the start time of the SQL server as the information provided by the view is cleaned whenever the server restarted. Refer to BOL for more information about the view and limitations.

The information returned from the usage count is aggregated from all the four operations (scan, seek, lookup and updates)

The data returned from the Last_User_Used_date is the maximum of all the four operations (scan, seek, lookup and updates)

declare @start_time as datetime 
select 
  @start_time = sqlserver_start_time 
from 
  sys.dm_os_sys_info;
with nonclustered_index_size as (
  select 
    t.object_id, 
    i.index_id, 
    t.name as table_name, 
    schema_name(t.schema_id) as table_schema, 
    i.name as index_name, 
    i.type_desc as index_type, 
    pa.data_compression_desc, 
    au.type_desc as storage_type, 
    pa.rows, 
    au.total_pages 
  from 
    sys.tables t 
    join sys.indexes i on i.object_id = t.object_id 
    and i.index_id >= 2 
    and i.is_primary_key = 0 
    join sys.partitions pa on t.object_id = pa.object_id 
    and i.index_id = pa.index_id 
    join sys.allocation_units au 
    on au.container_id = case when au.type in (1, 3)
       then pa.hobt_id else pa.partition_id end
), 
index_usage_stats as (
  select 
    object_id, 
    index_id, 
    Last_User_Used_date, 
    usage_count 
  from 
    sys.dm_db_index_usage_stats cross apply (
      select 
        max(n) 
      from 
        (
          values 
            (last_user_lookup), 
            (last_user_scan), 
            (last_user_seek), 
            (last_user_update)
        ) as m(n)
    ) as a(Last_User_Used_date) cross apply (
      select 
        sum(n) 
      from 
        (
          values 
            (user_seeks), 
            (user_scans), 
            (user_lookups), 
            (user_updates)
        ) as m(n)
    ) as b(usage_count) 
  where 
    database_id = db_id()
) 
select 
  table_schema, 
  table_name, 
  index_name, 
  index_type, 
  data_compression_desc, 
  storage_type, 
  rows, 
  total_pages, 
  total_pages * 8 as total_kb, 
  usage_count, 
  Last_User_Used_date, 
  @start_time as server_start_time 
from 
  nonclustered_index_size s 
  left join index_usage_stats u on s.object_id = u.object_id 
  and s.index_id = u.index_id 
order by 
  s.table_name, 
  s.index_name