Database Maintenance Script
Automatic/background indexing
It's considered bad practice to create indexes automatically as they should theoretically be individually verified by a DBA first. That said there is nothing technically stopping you from generating and immediately applying the suggested indexes from within the same script, which the script below will attempt to do. As such this version of the script is compatible with maintenance plans and the like and could even be run on a schedule.
As this version of the script creates the indexes without waiting for any further confirmation the MinimumImpactThreshold parameter becomes more important. It is used to avoid creating indexes that carry little benefit and therefore may do more harm than good (indexes create a larger memory footprint and can slow down inserts). Any calculated impact over 1000 is considered a worthwhile index and is the default value for the parameter, but set the value higher to further mitigate the small risk of creating an unhelpful index.
DECLARE @MinimumImpactThreshold int = 1000 DECLARE @CreateStatement varchar(max) DECLARE db_cursor CURSOR FOR SELECT 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + '_' + CONVERT(varchar(255), NEWID()) + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (dm_mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') FROM sys.dm_db_missing_index_groups dm_mig INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle WHERE dm_mid.database_ID = DB_ID() AND dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) > @MinimumImpactThreshold OPEN db_cursor FETCH NEXT FROM db_cursor INTO @CreateStatement WHILE @@FETCH_STATUS = 0 BEGIN print(@CreateStatement) EXEC (@CreateStatement) PRINT('Index created successfully.') FETCH NEXT FROM db_cursor INTO @CreateStatement END CLOSE db_cursor DEALLOCATE db_cursor