/* During runtime SQL Server collects informations about missing indexes and provides this through the DMV sys.dm_db_missing_index_XXX. * With this Transact-SQL script you can list the missing indexes ordered descanding by the user impact and the CREATE statement to add this index. * Works with SQL Server 2005 and higher versions in all editions. Requires VIEW SERVER STATE permissions. */ SELECT MID.[statement] AS ObjectName , MID.equality_columns AS EqualityColumns , MID.inequality_columns AS InequalityColms , MID.included_columns AS IncludedColumns , MIGS.last_user_seek AS LastUserSeek , MIGS.avg_total_user_cost * MIGS.avg_user_impact * (MIGS.user_seeks + MIGS.user_scans) AS Impact , N'CREATE NONCLUSTERED INDEX DBAIX_' + N' ON ' + MID.[statement] + N' (' + MID.equality_columns + ISNULL(', ' + MID.inequality_columns, N'') + N') ' + ISNULL(N'INCLUDE (' + MID.included_columns + N')WITH(ONLINE=ON,FILLFACTOR=99);', ';') AS CreateStatement FROM sys.dm_db_missing_index_group_stats AS MIGS INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MIGS.group_handle = MIG.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS MID ON MIG.index_handle = MID.index_handle WHERE database_id = DB_ID() AND MIGS.last_user_seek >= DATEDIFF(month, GetDate(), -1) ORDER BY [statement],EqualityColumns, Impact DESC;
