Missing index script
This script on missing indexes that SQL had found. SQL will track queries and provide suggestions on indexes it feels will provide improvements. You should not take this information and create all the indexes. Performance tuning can be an art and takes practice. This information should be reviewed and as a DBA implemented a little at a time.
The columns to focus on are AvgUserImpact, UniqueCompiles, UserSeeks and UserScans. Don't focus on only one column like AvgUserImpact. Just because SQL says adding this index will make a 99% improvement, we need to look at how often the index would be used. Better to implement an index with a lower AvgUserImpact that will be used more often, then one that is higher and used once or twice. Before adding an index review existing indexes on the table to see if adding a field might get the improvement you need. Remember, adding indexes adds overhead so we must be selective.
-- Missing Index Script
-- Original Author: David Waller
-- Date: 4/2020
SELECT db.[name] AS [DatabaseName]
,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
,id.[statement] AS [FullyQualifiedObjectName]
,id.[equality_columns] AS [EqualityColumns]
,id.[inequality_columns] AS [InEqualityColumns]
,id.[included_columns] AS [IncludedColumns]
,gs.[unique_compiles] AS [UniqueCompiles]
,gs.[user_seeks] AS [UserSeeks]
,gs.[user_scans] AS [UserScans]
,gs.[last_user_seek] AS [LastUserSeekTime]
,gs.[last_user_scan] AS [LastUserScanTime]
,gs.[avg_total_user_cost] AS [AvgTotalUserCost] -- Average cost of the user queries that could be reduced by the index in the group.
,gs.[avg_user_impact] AS [AvgUserImpact] -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ','
ELSE ''
END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE db.[database_id] = DB_ID()
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE); 
