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()
|
 Updating...
Andy Hughes, Jun 27, 2012, 8:47 AM
|