DMV Queries - Get Unused Indexes in a Database

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        27 Jun 2012

Description

This function retrieves a list of indexes that have not been used since the last SQL Server Instance/Service restart.
This status is determined by the following criteria:
  • If an index has no entry in the DMV sys.dm_db_index_usage_stats view indicating it has not been accessed/updated
  • If an index has an entry in the DMV sys.dm_db_index_usage_stats view but only the user_updates value is non-zero, with the user_seeks, user_scans and  user_lookups values all being zero  i.e. the index has been updated but not used
Alternatively, there is another function that returns usage data for all indexes in a database, including heaps (tables with no indexes)

Code

Function:

DROP FUNCTION dbo.udf_GetUnusedIndexes
GO

CREATE FUNCTION dbo.udf_GetUnusedIndexes ()
RETURNS @udf_UnusedIndexes TABLE
  
(
      
DatabaseName            SYSNAME NULL,
      
TableName               SYSNAME NULL,
      
IndexName               SYSNAME NULL,
      
IndexUserUpdates        INT
  
)
AS
/***************************************************************
Purpose:   To retrieve all indexes in the current database that are not used.

           The following DMVs are used:
               sys.indexes                 - to retrieve the full list of indexes for the database
               sys.dm_db_index_usage_stats - to examine the usage stats for indexes that
                                             have been used/updated
                    
          The criteria used to determine the 'unused' status are:
          
               1.  Index has no entry in the sys.dm_db_index_usage_stats view - not been used or updated
               2.  Index has an entry in the sys.dm_db_index_usage_stats view - but has a value for user_updates but zero values
                   for user scans/seeks/lookups.
          
          
Author:        ChillyDBA
History:   27 Jun 2012 - Initial Issue

****************************************************************/

BEGIN

   INSERT
@udf_UnusedIndexes
      
(
          
DatabaseName,
          
TableName,
          
IndexName,
          
IndexUserUpdates
      
)

  
SELECT
      
DB_NAME()                       AS DatabaseName,
      
OBJECT_NAME(i.OBJECT_ID)        AS TableName ,
      
i.name                          AS IndexName,
      
s.user_updates                  AS IndexUserUpdates
        
      
-- these columns were included in thee original query, but are obsolete as
       -- the join predicates ensure they will always be null
       --s.user_seeks,
       --s.user_scans,
       --s.user_lookups
      
  
FROM sys.indexes i  
  
LEFT JOIN sys.dm_db_index_usage_stats s
      
ON s.OBJECT_ID = i.OBJECT_ID
      
AND i.index_id = s.index_id
      
AND s.database_id = DB_ID()
  
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexable') = 1

  
-- index_usage_stats has no reference to this index (not being used)
  
AND s.index_id IS NULL

  
-- index is being updated, but not used by seeks/scans/lookups
  
OR (
          
s.user_updates > 0
          
AND s.user_seeks = 0
          
AND s.user_scans = 0
          
AND s.user_lookups = 0
      
)
  
ORDER BY OBJECT_NAME(i.OBJECT_ID) ASC  
  
   RETURN
END


Test Code:

SELECT * FROM dbo.udf_GetUnusedIndexes()


 
ċ
udf_GetUnusedIndexes.sql
(2k)
Andy Hughes,
Jun 27, 2012, 8:47 AM
Comments