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?

Dupe: http://serverfault.com/questions/19672/method-for-finding-unnecessary-indexes-of-an-operational-sql-server-database — Evan Anderson, Nov 16 '09 at 16:33
Logically different question. His question is about finding unnecessary indexes. My question is about getting index usage. While this question can be used to solve his problem, it can also be used to answer other questions (what indexes are most used? What indexes should i not get rid of? What indexes can i combine? What indexes should i move to another filegroup?) — Ian Boyd, Nov 18 '09 at 15:05

score 1 · Accepted Answer · edited Mar 08 '17 at 17:58

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: alt text

score 0 · Answer 2 · answered Nov 16 '09 at 17:24

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

SQL Server: Index utilization statistics?

2 Answers2