how to tell if an index is ever used

Question

I have some old tables with, what I think, are nearly worthless indexes. How can I easily be sure that are not ever being used before I drop them.

score 25 · Answer 1 · answered Feb 28 '11 at 19:08

Based on Joe's answer I came up with this:

SELECT
      row_number() over(order by user_seeks,user_lookups,user_scans),
      [Database] = d.name,
      [Schema]= s.name,
      [Table]= o.name,
      [Index]= x.name,
      [Scans] = user_scans,
      [Seeks] = user_seeks,
      [Lookups] = user_lookups,
      [Last Scan] = last_user_scan,
      [System Scans] = system_scans
FROM  sys.dm_db_index_usage_stats u
INNER JOIN sys.sysdatabases d on u.database_id = d.dbid
INNER JOIN sys.sysindexes x on u.object_id = x.id  and u.index_id = x.indid
INNER JOIN sys.objects o on u.object_id = o.object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
where  x.name is not null
order by 1 desc

This is super slick, and years late -- but there may be an issue here joining the wrong database with the Tables. At least in my testing. — TravisWhidden, Oct 04 '18 at 17:27
sys.objects is database specific so you can have the same object_id in more than one database which is why it can give the incorrect database when running this query — Croberts, Feb 07 '20 at 11:33

Joe Stefanelli · Accepted Answer · 2019-12-05T16:53:59.490

7

Take a look at the sys.dm_db_index_usage_stats DMV. Just be careful to keep in mind that these counters are reset every time SQL Server is restarted.

edited Dec 05 '19 at 16:53

answered Feb 28 '11 at 17:38

Joe Stefanelli

132,803
19
237
235

9

Also keep in mind that indexes may be used rarely, but you may have the accounting department on the line when they run their yearly nonsense. – Sam Feb 28 '11 at 17:44

score 2 · Answer 3 · edited Jan 21 '14 at 07:06

Check if the user seeks of the index are increasing with every select on the table u r using unless the server is restarted this will give you a clear idea which index is being used or not.

SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('database_name') AND sis.OBJECT_ID = OBJECT_ID('table_name');
GO

how to tell if an index is ever used

3 Answers3