SQL Server - how to determine if indexes aren't being used?

Question

I have a high-demand transactional database that I think is over-indexed. Originally, it didn't have any indexes at all, so adding some for common processes made a huge difference. However, over time, we've created indexes to speed up individual queries, and some of the most popular tables have 10-15 different indexes on them, and in some cases, the indexes are only slightly different from each other, or are the same columns in a different order.

Is there a straightforward way to watch database activity and tell if any indexes are not hit anymore, or what their usage percentage is? I'm concerned that indexes were created to speed up either a single daily/weekly query, or even a query that's not being run anymore, but the index still has to be kept up to date every time the data changes.

In the case of the high-traffic tables, that's a dozen times/second, and I want to eliminate indexes that are weighing down data updates while providing only marginal improvement.

Answer 1

Look at the number of user seeks/scans/lookups and last user seek/scan/lookup in sys.dm_db_index_usage_stats. These stats are reset at server start up, so you'd have to check after the server was up and running a relevant load for enought time.

Answer 2

The Database Tuning Wizard will be helpful here. Use the Profiler to record a standard set of queries over the course of a couple of hours, and use that trace file in the Tuning Wizard to identify possible redundant indexes.

Answer 3

If you're in SQL Server 2005/2008, you should use the Database Tuning advisor.

You can specify it to run for a given length of time, and it will collect statistics on what is being used and what isn't. At the end of the run, it will come up with some very useful observations on what to do to optimize your indexing strategy.

Answer 4

This script will look at the DMV's (dynamic management views) and find those indices that haven't been used.

DECLARE  @dbid INT

SELECT @dbid = DB_ID(DB_NAME())

SELECT   
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
FROM     
    SYS.INDEXES I
JOIN 
    SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE    
    OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
    AND I.INDEX_ID NOT IN 
       (SELECT S.INDEX_ID
        FROM SYS.DM_DB_INDEX_USAGE_STATS S
        WHERE S.OBJECT_ID = I.OBJECT_ID
        AND I.INDEX_ID = S.INDEX_ID
        AND DATABASE_ID = @dbid)
ORDER BY 
    OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

Mind you - the DMV are dynamic - e.g. they get reset to "nothing" every time you restart your SQL Server services. Don't check those if your server has been up for only a few minutes! Almost all indices will show up in your result set......

But if you can monitor the result set of this query over time, you should definitely get a feel for which indices aren't being used ever. Very handy indeed !