Did you know that SQL Server provides several views which provide information on recommended indexes?

Using the following SQL, you can identify where SQL recommends indexes, ordered by potential performance boost from adding the suggested index.

Obviously you may wish to review these manually, rather than just blindly creating, but it gives you a pointer in the right direction!

It is also worth noting that this view is cleared when SQL Service is restarted, therefore it’s a good idea to restart the service, let the system run for a couple of hours or so, then grab the results and analyse further.

The code below works for all versions of SQL server after 2008, plus also Azure SQL Databases.

SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,  'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle)   + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'   + ' 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 + ')', '') AS create_index_statement
	,  migs.*
	,mid.database_id
	,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Example results: