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()