-------------------------------------------------------------------------------------------------
-- Author:		 Leonardo Pasta
-- Name:		 sp_DBA_MissingIndexes
-- Created on:   06/04/2008
-- Description:  Return the indexes the optimizer believes could help it solve the current 
--               queries faster. It considers only queries executed since startup so its better 
--               to run it when the instance has being running for a considerable amount 
--               of time (e.g. 1 month).
-- Sintax:       sp_DBA_MissingIndexes	@CutOff (default=50) - Number of rows to be displayed.
--------------------------------------------------------------------------------------------------
USE master
GO
IF OBJECT_ID('sp_DBA_MissingIndexes') IS NOT NULL
  DROP PROCEDURE sp_DBA_MissingIndexes
GO
CREATE PROCEDURE sp_DBA_MissingIndexes (@CutOff int = 50) AS
	SELECT TOP(@CutOff)
	  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
	  + ' 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

     ,CONVERT (decimal (28,1),    migs.avg_total_user_cost * migs.avg_user_impact 
                               * (migs.user_seeks + migs.user_scans)
	  ) AS improvement_measure
	 ,migs.unique_compiles
     ,migs.user_seeks
     , migs.user_scans
     , avg_user_impact
     , avg_system_impact
     , last_user_seek
     , last_user_scan
	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_user_impact > 10
	  AND mid.database_id = DB_ID()
	ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
GO
EXEC sp_MS_marksystemobject sp_DBA_MissingIndexes
GO
GRANT EXEC ON sp_DBA_MissingIndexes TO PUBLIC
GO