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.

Space

----Log File(s) Used Size (KB)---
select instance_name
,cntr_value 'Log File(s) Used Size (KB)'
from sys.dm_os_performance_counters
where counter_name = 'Log File(s) Used Size (KB)'
and instance_name like '%%'

---Check total log space taken up by all DB--
select sum(cntr_value)
from sys.dm_os_performance_counters
where counter_name = 'Log File(s) Used Size (KB)'
and instance_name like '%%'


----Data File(s) Used Size (KB)---
select instance_name
,cntr_value 'Data File(s) Size (KB) '
from sys.dm_os_performance_counters
where counter_name = 'Data File(s) Size (KB) '
and instance_name like '%%'
order by 'Data File(s) Size (KB) '

---Check total data space taken up by all DB---
select sum(cntr_value)
from sys.dm_os_performance_counters
where counter_name = 'Data File(s) Size (KB) '
and instance_name like '%%'


SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info

--TempDB analysis for space--
USE tempdb
GO
EXEC sp_spaceused

--The following should give you some clues as to which table(s) consume most of the space in the data file(s) -- this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:
USE tempdb
GO

SELECT name
FROM tempdb..sysobjects

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

--If you can't shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:

DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')

--Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with 'SPID (Server Process ID) : '). Use that in the following:

DBCC INPUTBUFFER()

--This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:

KILL

-------DATABASE : Tablename ROWCOUNTS----------------------------
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC



--The first thing you can do is simply compare the difference between the timestamp BEFORE your query, and the timestamp AFTER. For example:

DECLARE @a DATETIME, @b DATETIME
SET @a = CURRENT_TIMESTAMP

DECLARE @i INT
SET @i = 0
WHILE @i < 10000
BEGIN
SET @i = @i + 1
END
SET @b = CURRENT_TIMESTAMP
SELECT DATEDIFF(MS, @a, @b)


--You can achieve similar results by running SQL Profiler, setting appropriate filters, and watching the Duration column as your query runs.

--Finally, you can alter the above code slightly so that you see all of the durations on the messages tab of Query Analyzer:

SET STATISTICS TIME ON
-- query here
SET STATISTICS TIME OFF


/*To find the number of unallocated pages in kb,we can use the sys.dm_db_file_space_usage DMV as follows: */
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*8) AS [free space in KB]
FROM sys.dm_db_file_space_usage

select a.session_id
, b.login_name
FROM sys.dm_exec_connections a ,sys.dm_exec_sessions b
WHERE a.session_id=b.session_id