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
