Identify bad / Unused Indexes

While tuning the performance of a query or Stored Procedure, we might want to first look at the bad and required indexes.

Bad Indexes:- There are indexes that are not being used and are resulting to bad execution plan.
These indexes are noted as Unused Indexes. Below Query helps you to find the unused indexes based on the last Usage Statistics. basically Indexes that are not used for Seek / Scan operations and used under inserts/updates in the Columns are treated as Unused Indexes.

FOR A SINGLE DATABASE

SELECT DB_NAME() AS [Database], o.name AS Table_name, i.name AS index_name, i.type_desc, SUM(s.used_page_count) * 8   AS IndexSizeKB, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement

FROM sys.indexes i JOIN sys.dm_db_partition_stats  AS s ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON  i.object_id = o.object_id

LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id

AND i.index_id = u.index_id AND u.database_id = DB_ID()

WHERE o.type <> 'S' and isnull(u.user_updates,0) > 0and i.type_desc <> 'HEAP' AND U.user_seeks < 100

GROUP BY

o.name , i.name, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, i.name, o.name

ORDER BY

(convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, user_updates desc, o.name, i.name

RUN THIS IF REQUIRED FOR FOR ALL DATABSES

 declare @db_name varchar(100), 

@str varchar(5000) , @str1 varchar(5000)

DECLARE cur_user_db CURSOR FOR 

 SELECT name  

 FROM master..sysdatabases  

 where name not in ('tempdb', 'model', 'master', 'msdb', 'maintenance', 'distribution', 'litespeedlocal', 'litespeedcentral') 

        and databasepropertyex(name,'Updateability') = 'READ_WRITE'  

        and databasepropertyex(name,'Status') = 'ONLINE' 
        and databasepropertyex(name,'Useraccess') = 'MULTI_USER' 

OPEN cur_user_db 

FETCH NEXT FROM cur_user_db into @db_name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

set @str = 'use [' + @db_name + ']' + char(13) + char(10) +

'SELECT DB_NAME() AS [Database], o.name AS Table_name, i.name AS index_name, i.type_desc, SUM(s.used_page_count) * 8   AS IndexSizeKB, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, ''Drop index '' + i.name + '' on '' + o.name as DropIndexStatement

FROM sys.indexes i

JOIN sys.dm_db_partition_stats  AS s ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

JOIN sys.objects o ON  i.object_id = o.object_id

LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id         

AND i.index_id = u.index_id AND u.database_id = DB_ID()

WHERE o.type <> ''S'' and isnull(u.user_updates,0) > 0and i.type_desc <> ''HEAP'' AND U.user_seeks < 100

GROUP BY

o.name , i.name, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, i.name, o.name

ORDER BY

(convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, user_updates desc, o.name, i.name'

exec (@str) 

FETCH NEXT FROM cur_user_db into @db_name 

END 

CLOSE cur_user_db 

DEALLOCATE cur_user_db

The Query also provides the Command to drop the Unused Indexes. So its easy to remove them.

...Happy SQLing
Thanks for Visiting and Sharing your Views