Sql-server – SQL Server: Index utilization statistics

indexesperformancesql server

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:

alt text

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?

Best Answer

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

Related Topic