/*Script to find Missing Indexes for all databases in SQL Server*/
/*
This script is for SQL Server 2005 and up. 
The script will return all the missing indexes for a SQL Server instance, rating their impact 
and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) 
and DMO (Database Management Objects) which this script requires to function. 
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) 
operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. 
INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. 
The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to 
the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.
*/
SELECT [EstIndexUses]
	,[EstIndexImpact%]
	,[EstAvgQueryCost]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[CreateIndex]
	,[EqualityColumns]
	,[InequalityColumns]
	,[IncludedColumns]
	,[UniqueCompiles]
	,[LastUserSeek]
FROM (
	SELECT migs.user_seeks AS [EstIndexUses]
		,migs.avg_user_impact AS [EstIndexImpact%]
		,migs.avg_total_user_cost AS [EstAvgQueryCost]
		,db_name(mid.database_id) AS [DbName]
		,OBJECT_SCHEMA_NAME(mid.OBJECT_ID, mid.database_id) AS [SchemaName]
		,OBJECT_NAME(mid.OBJECT_ID, mid.database_id) AS [TableName]
		,'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN '_'
			ELSE ''
			END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN ','
			ELSE ''
			END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [CreateIndex]
		,mid.equality_columns AS EqualityColumns
		,mid.inequality_columns AS InequalityColumns
		,mid.included_columns AS IncludedColumns
		,migs.unique_compiles AS UniqueCompiles
		,migs.last_user_seek AS LastUserSeek
	FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
	INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
	) AS a
WHERE 1 = 1
--AND [EstIndexUses] > 1000
--AND [EstIndexImpact%] > 10
--AND [EstAvgQueryCost] > 1
--AND DbName IN ('DatabaseName')
ORDER BY [EstIndexUses] DESC
	,[EstAvgQueryCost] DESC
	,[EstIndexImpact%] DESC
OPTION (RECOMPILE);