SQL Server: Index utilization statistics?

Question

Is there a way in SQL Server to get a report of index usage?

i know starting with SQL Server 2005, you can get reports of top resource-using queries, based on what's in the Plan Cache:

i'm curious to know if there are any indexes that are no longer used, or hardly used, expecially multi-key indexes. It's conceivable that the query Plan Cache also contains the indexes that will be used by a plan, so perhaps index using is also in there?

Answer 1

i finally managed to find a search phrase in Google that got me an answer for SQL Server 2005 and newer:

How to get index usage information in SQL Server (mssqltips.com):

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Which gives results like:

OBJECT_NAME          INDEX_NAME                 USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
Properties           IX_Properties_PropertyName 0          455477     0            0
Locations_Depricated NULL                       0          71255      0            0
Users                PK__Users__UserIDInteger   137772     58637      47134        72
CurrencyTypes        PK_CurrencyTypes           3397       55554      0            0
ExchangeRates        IX_ExchangeRates           35736      46621      0            0
CurrencyCategories   IX_CurrencyCategories_1    0          25734      0            0
CurrencyCategories   IX_CurrencyCategories      0          22287      19888        0

Or, hotlinking the image from mssqltips:

Answer 2

I use the following script which lists your under utilised non-clustered indexes:

SELECT objectname=OBJECT_NAME(s.OBJECT_ID) , indexname=i.name , i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, p.rows FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND p.rows > 10000 ORDER BY reads, rows DESC

This script excludes non-clustered indexes which are also used for a Primary or Unique constraints (and ignores indexes less than 10000 rows).

Please note, the counts provided by the underlying DMV are reset to zero when SQL services are stopped or the database is taken offline. So it's best to run this script when SQL has been running for a while and the counts have built up.

If the reads are 0 then the index is probably safe to drop (unless it's required for infrequently used application logic).