MSSQL: Does last_user_update mean that the index hurt performance?

Question

I've written a query to look for indexes that are not used in the production environment in order to drop them, however I'm curious whether the last_user_update column is an indication that the index was solely a maintenance overhead, in which case it would be good to drop it, or whether the index helped the performance of the insert/update/delete statement, in which case I wouldn't want to drop it. Below is the query I'd like to use:

DECLARE @DBInfo TABLE  
( database_id int, object_id int, tablename nvarchar(200), indexname nvarchar(200), lastactivity datetime)  

DECLARE @command VARCHAR(5000)  

SELECT @command = 'Use [' + '?' + '] SELECT  
database_id, o.object_id, o.name, i.name as indexname, max(lastactivity) as lastactivity
from (
    select database_id, object_id, index_id, max(last_user_seek) as lastactivity from sys.dm_db_index_usage_stats
    WHERE object_id > 1000
    GROUP BY database_id, object_id, index_id
    UNION ALL
    select database_id, object_id, index_id, max(last_user_scan) as lastactivity from sys.dm_db_index_usage_stats
    WHERE object_id > 1000
    GROUP BY database_id, object_id, index_id
    UNION ALL
    select database_id, object_id, index_id, max(last_user_lookup) as lastactivity from sys.dm_db_index_usage_stats
    WHERE object_id > 1000
    GROUP BY database_id, object_id, index_id
    /*UNION ALL
    select database_id, object_id, index_id, max(last_user_update) as lastactivity from sys.dm_db_index_usage_stats
    WHERE object_id > 1000
    GROUP BY database_id, object_id, index_id*/
) a
inner join sys.objects o on a.object_id = o.object_id
inner join sys.indexes i on i.object_id = a.object_id AND i.index_id = a.index_id
where database_id = db_id()
GROUP BY database_id, o.object_id, o.name, i.name
order by lastactivity'  
INSERT INTO @DBInfo  
   (database_id, object_id, tablename, indexname, lastactivity)  
EXEC sp_MSForEachDB @command  

SELECT db_name(database_id) as dbname, tablename, indexname, lastactivity FROM @DBInfo
where lastactivity < dateadd(day, -15, getdate()) or lastactivity is null
order by db_name(database_id), tablename, indexname

Answer 1

The column does not have any real meaning in checking for unused indexes.

The index may have no recent updates but can be still very useful (eg to avoid a table scan or cover a popular query). This query tells you how an index is used:

SELECT
    o.name AS [object_name], 
    i.name AS index_name, 
    i.type_desc, 
    u.user_seeks, u.user_scans, 
    u.user_lookups, u.user_updates,
    o.type
FROM
    sys.indexes i
    JOIN
    sys.objects o ON i.[object_id] = o.[object_id]
    LEFT JOIN 
    sys.dm_db_index_usage_stats u ON i.[object_id] = u.[object_id] AND 
                                    i.index_id = u.index_id AND 
                                    u.database_id = DB_ID()
WHERE
    o.type IN ('U', 'V') AND
    i.name IS NOT NULL
ORDER BY 
    o.name, i.NAME;

Edit, after comment:

I prefer the user_seeks, user_scans, user_lookups and user_updates columns that tell me how the index is used. FYI, I used the query above and included "system_updates" columns but I got zero for all values (SQL 2005, 50Gb or so database, where we've used this to good effect before)

From sys.dm_db_index_usage_stats

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

So, last sentence implies to look at usage as well as updates

Answer 2

If you're interested in the dynamic properties of indices - whether they're being used or not - you might be better off inquiring in the DMV - the Dynamic Management Views. They should give you nice information about what indices are being used and how often.

Also, there's one DMV that actually uses internal statistics of the query optimizer to suggest a) potentially missing indices that could help boost your performace, and b) another one to show potentially unused indices.

Mind you - those are dynamic management views - they get reset with every server reboot, so they're not collecting data over eternal time periods - only since your last reset.

Marc

Find missing indices:

SELECT  
    object_name(object_id), d.*, s.*
FROM
    sys.dm_db_missing_index_details d 
INNER JOIN 
    sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN 
    sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE   
    database_id = db_id()
ORDER BY  
    object_id

Find unused indices:

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