Get Unused Indexes in a Database

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