SQL Server Utilities - Cleanup Indexes not in use

Among the many tasks that are under the DBA’s responsibilities is keeping the database performing well and in good health. And when mentioning performance the topic of indexes is Inevitable. The task of index maintenance typically includes the process of handling indexes fragmentation accomplished by the REBUILD and REORGANIZE commands and wile this is a good practice there are a few more tasks to be done and those include removing indexes that are not in use, removing redundant (duplicate) indexes and spotting indexes that are missing and the system would benefit from.

In this short blog post I share the script I use to find indexes that are not in use when maintaining or tuning a system.

One important point to be aware of and take into consideration is that the indexes metadata information is collected since service startup so you should allow for enough uptime before concluding that an index is not in use and that of course is system dependent. For example, on a busy system a day of workload during a business day would do but on other systems where there are specific weekly or monthly report you would probably be safer to wait and check for missing indexes after we have passed the beginning of the month.

Another important point to be aware of is that an index may not be in use by the system but it is hard coded in the form of an Index Hint within the body of a stored procedure and in such a case when the procedure gets executed that execution will fail.

Another point to mention is the @index_access_count parameter used in the script which simply defines the threshold for the missing indexes to be found. The value I assign to this parameter is typically 0 but in some cases I would use 10 or so. Giving an uptime of months an index that have been used only 10 times may be a candidate to be removed and this of course is for you to decide based on your knowledge of the system and the data.

Here is a link to the project at github

Get the current service uptime

SELECT DATEDIFF(DAY, create_date, CURRENT_TIMESTAMP)dif_dd, create_date FROM sys.databases WHERE database_id = 2;

Verify an index does not appear in a stored procedure as an index hint

SELECT OBJECT_NAME(object_id),  * FROM sys.sql_modules WHERE definition LIKE'%inex_name_goes_here%';

Get all missing indexes

USE AdventureWorks2017;
--SELECT DATEDIFF(DAY, create_date, CURRENT_TIMESTAMP)dif_dd, create_date FROM sys.databases WHERE database_id = 2

DECLARE @index_access_count int = 10; 

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT 
	'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + ';'
	,@@SERVERNAME AS [server]
	,DB_NAME(database_id) AS [database]
	,SCHEMA_NAME(t.schema_id) [schema]
	,OBJECT_NAME(i.object_id) AS [object]
	,i.name AS [index]
	,isize.size_mb
	,s.user_updates
	,s.user_seeks
	,s.user_scans
	,s.user_lookups
	,CASE WHEN i.[type] = 0 THEN 'Heap' WHEN i.[type]= 1 THEN 'Clustered' WHEN i.type = 2 THEN 'Nonclustered' WHEN i.[type] = 3 THEN 'XML' ELSE 'NA' END AS index_type
	,i.is_primary_key
	,CASE WHEN i.is_unique = 1 THEN 'UI' WHEN i.is_unique_constraint = 1 THEN '	UC' ELSE 'NA' END AS [is_unique]
FROM sys.indexes i  
INNER JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND i.index_id = s.index_id 
INNER JOIN sys.tables t ON t.object_id = i.object_id
CROSS APPLY (SELECT  p.index_id
                    ,SUM(p.rows) AS rows
                    ,SUM(total_pages) / 128 AS size_mb
              FROM sys.partitions p
              INNER JOIN sys.allocation_units au ON au.container_id = p.partition_id
              WHERE p.object_id = i.object_id AND p.index_id = i.index_id
              GROUP BY p.index_id
			) isize
WHERE OBJECTPROPERTY(i.object_id, 'IsIndexable') = 1 AND OBJECTPROPERTY(i.object_id, 'IsSystemTable') = 0 AND s.index_id > 0

AND (s.index_id IS NULL OR (ISNULL(s.user_updates, 0) >= 0 AND ISNULL(s.user_seeks, 0) <= @index_access_count AND ISNULL(s.user_scans, 0) <= @index_access_count AND ISNULL(s.user_lookups, 0) <= @index_access_count ) )

AND database_id = DB_ID()
AND i.type <> 1 
AND i.is_primary_key <> 1
AND i.is_unique = 0
ORDER BY OBJECT_NAME(i.object_id) DESC, s.user_updates DESC;

--SELECT OBJECT_NAME(object_id),  * FROM sys.sql_modules WHERE definition LIKE'%%'