sql-server - 如何判断索引是否被使用过

Question

这个问题已经有答案了:

SQL Server - how to determine if indexes aren't being used? (4 个回答)

已关闭 7 年前。

我有一些旧表，我认为这些表的索引几乎毫无值(value)。在丢弃它们之前，我如何轻松确定它们从未被使用过。

Answer 1

根据乔的回答，我想出了这个:

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