Sql-server – Ok to drop indexes on FK’s if they have no stats in the DMVS

dmvforeign keyindexoptimizationsql-server-2012

I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.

Are you 100% safe to drop these indexes ? or could they be used by the optimizer for say inserts or Deletes and would not register any stats in the DMV ?? if so, how can one tell they are 100% safe to remove?

I have had the server running for 2 months so am certain I have covered our monthly workload cycle. 

    SELECT o.name
    , indexname=i.name
    , i.index_id   
    , reads=user_seeks + user_scans + user_lookups   
    , writes =  user_updates   
    , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
    , CASE
        WHEN s.user_updates < 1 THEN 100
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END AS reads_per_write
    , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
    FROM sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_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 (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 2000
    ORDER BY  name, reads

Best Answer

As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the case of SQL Server 2012, where rebuild currently clears the stats - I suspect that will be fixed - thanks @MartinSmith).

You should expect that if a DML activity used the index in a read capacity for some reason (e.g. to perform an update on some other table), this would register as a read activity, not a write. All of the writes you see are index maintenance.

As an aside, here is a slightly more efficient way to write this query. I removed the twice-referenced correlated subquery and dropped the unnecessary joins to sys.objects and sys.schemas. I also fixed a few minor syntax things, such as prefixing all columns with aliases where appropriate, placing reserved words in square brackets, and removing AS 'column alias' syntax. I found it confusing that half way through the SELECT list you changed from alias = expression syntax to expression AS alias syntax - you should pick one and be consistent IMHO. I changed these both for majority within the query and also according to my personal preference. :-)

SELECT name = OBJECT_NAME(s.[object_id])
    , indexname = i.name
    , i.index_id   
    , reads = s.user_seeks + s.user_scans + s.user_lookups   
    , writes = s.user_updates   
    , g.[rows]
    , reads_per_write = CASE
        WHEN s.user_updates < 1 THEN 100.0
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END
    , [drop statement] = 'DROP INDEX ' + QUOTENAME(i.name) 
      + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id])) 
      + '.'    + QUOTENAME(OBJECT_NAME(s.[object_id]))
    FROM sys.dm_db_index_usage_stats AS s  
    INNER JOIN sys.indexes AS i 
      ON i.index_id = s.index_id 
      AND s.object_id = i.object_id   
    INNER JOIN 
    (
      SELECT [object_id], index_id, [rows] = SUM([rows]) 
        FROM sys.partitions GROUP BY [object_id], index_id
        HAVING SUM([rows]) > 2000
    ) AS g
      ON i.[object_id] = g.[object_id] 
      AND i.index_id = g.index_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
    ORDER BY name, reads;
Related Question