Find and drop unused indexes across multiple servers

So we have a system with about 300+ dbs across 20 servers and all of the databases are identical. Each db represents a client.

/*
This query is used to find index, seeks, scans, lookups and updates across all tables in all  dbs on all servers.
criteria for unused indexes is seeks+scans+lookups<10
the index should not be unique or clustered
and table type is not a heap.

Run the below mentioned query across all servers using registered servers. It uses EXEC sp_msforeachdb to run query on each db
*/
CREATE TABLE #UnusedIndexes
(dbName sysname,
tablename sysname,
indexname sysname,
seeks BIGINT,
scans BIGINT,
lookups BIGINT,

updates BIGINT,
lastseek DATETIME,
lastscan DATETIME,
TotalSeeksScansLookups BIGINT,
SizeInKB BIGINT)


EXEC sp_msforeachdb N' USE [?]

INSERT INTO #UnusedIndexes
SELECT  DB_NAME(),o.name ,
i.name,
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates,
u.last_user_seek,
u.last_user_scan,
u.user_seeks+
u.user_scans+
u.user_lookups TotalSeeksScansLookups,
reserved_page_count * 8
FROM    sys.indexes i
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()
LEFT JOIN sys.dm_db_partition_stats ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id

WHERE   o.type <> ''S''
--and isnull(u.user_updates, 0) > 0
and i.type_desc <> ''HEAP''
AND ISNULL(u.user_seeks,0)+
ISNULL(u.user_scans,0)+
ISNULL(u.user_lookups,0) < 10
AND is_unique = 0
AND i.type_desc = ''NONCLUSTERED''
--  AND o.name = '''' AND i.name=''<indexname>''
AND DB_NAME() LIKE ''%_<type>''
;

'

SELECT * FROM #UnusedIndexes

DROP TABLE #UnusedIndexes

Once you get the output, put it in excel or a sql table for further queries

----determine the total number of databases in the system with similar schemas (in ours its  --301 dbs)
SELECT *
FROM sys.databases WHERE name LIKE '%%_<type>' AND
name NOT IN ('master','tempdb','model','msdb','MMDB','pubs','Northwind','mmdb','MMShare')--301  dbs

/*
Here, I have imported the data to a table called UnusedIndexes and ran the count query below:
--wherever the noofoccurences match the total number of stage dbs that is 301 for this run
AND the user seeks, scans and lookups are 0 or very minimal/negligible as compared to updated
means none of the stage database is using this INDEX
Hence the index can be deleted across all  dbs.

Incase of user seeks,scans,lookups and updates being null, does not give us much information..so we will have to go through each of the index usage and figure out whether to keep or delete it.
*/
SELECT tablename,indexname,COUNT(indexname) AS noofoccurences,SUM(totalSeeksScansLookups) as totalSeeksScansLookups,
SUM(updates) AS Totalupdates,sum(sizeinKB)/1000/1000 AS sizesavingsacrossAllinGB
from [dbo].[UnusedIndexes]
GROUP BY tablename, indexname
HAVING COUNT(indexname) >300
ORDER BY sum(sizeinKB) desc
/* As you begin deleting the indexes you might see a significant decrease in db size. This will also help the CPU since those many lesser indexes need to be updated and maintained.

After the above query run keep changing the --HAVING COUNT(indexname) >275 (and see how many more indexes it gives)


Sometimes an index is picked up by optimizer by mistake and hence shows up as used in a few databases. If that is the behaviour you see, those indexes can be dropped as well, unless you put that index in specifically for a large database or for a particular query.
*/

Be very careful dropping any index. Double check to see its not unique or clustered. We saw savings of approx total 50GB across all servers.