SELECT 'Identify the missing indexes (TOP 10), across ALL databases.'
/* Purpose: Identify the missing (or incomplete indexes) (TOP 10), for ALL databases.				*/
/* Notes : 1. Could combine above with number of reads/writes a DB has since reboot, but this takes */
/*		   into account how often index could have been used, and estimates a 'realcost'			*/
SELECT  TOP 10 
		[Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
		, avg_user_impact -- Query cost would reduce by this amount, on average.
		, TableName = statement
		, [EqualityUsage] = equality_columns 
		, [InequalityUsage] = inequality_columns
		, [Include Cloumns] = included_columns
FROM		sys.dm_db_missing_index_groups g 
INNER JOIN	sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
INNER JOIN	sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

SELECT 'Identify which indexes are not being used, across ALL databases.'
/* Purpose: Identify which indexes are not being used, for a given database.							*/
/* Notes: 1. These will have a deterimental impact on any updates/deletions.							*/
/*		  Remove if possible (can see the updates in user_updates and system_updates fields)			*/
/*		  2. Systems means DBCC commands, DDL commands, or update statistics - so can typically ignore.	*/
/*		  3. The template below uses the sp_MSForEachDB, this is because joining on sys.databases		*/
/*			gives incorrect results (due to sys.indexes taking into account the current database only).	*/ 	

-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in following step.
SELECT TOP 1
		DatabaseName = DB_NAME()
		,TableName = OBJECT_NAME(s.[object_id])
		,IndexName = i.name
		,user_updates	
		,system_updates	
		-- Useful fields below:
		--, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
	AND	user_seeks = 0
	AND user_scans = 0 
	AND user_lookups = 0
	-- Below may not be needed, they tend to reflect creation of stats, backups etc...
--	AND	system_seeks = 0
--	AND system_scans = 0
--	AND system_lookups = 0
	AND s.[object_id] = -999  -- Dummy value, just to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB	'USE [?]; 
-- Table already exists.
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10	
		DatabaseName = DB_NAME()
		,TableName = OBJECT_NAME(s.[object_id])
		,IndexName = i.name
		,user_updates	
		,system_updates	
		-- Useful fields below:
		--, *
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
	AND	user_seeks = 0
	AND user_scans = 0 
	AND user_lookups = 0
    AND i.name IS NOT NULL	-- I.e. Ignore HEAP indexes.
	-- Below may not be needed, they tend to reflect creation of stats, backups etc...
--	AND	system_seeks = 0
--	AND system_scans = 0
--	AND system_lookups = 0
ORDER BY user_updates DESC
;
'

-- Select records.
SELECT TOP 10 *  FROM #TempUnusedIndexes ORDER BY [user_updates]  DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes