Wednesday, April 13, 2016

Missing indexes -- revised script

set concat_null_yields_null off
select
      'create index IX_' +
            replace(replace(replace (equality_columns, '[', ''),']',''),', ','_') +
            ' on ' +
            sch.name collate SQL_Latin1_General_CP1_CI_AS + '.' + obj.name +
            ' (' +
            equality_columns +
            case when inequality_columns is null
                                    then ''
                                    else ',' +  inequality_columns end +
            ')' +
            case when included_columns is not null then
                  ' include (' +
                  isnull(included_columns,'') +
                  ') ' else '' end +
            ' -- ' + convert (varchar, avg_user_impact) + '% anticipated impact'
      from sys.dm_db_missing_index_details mid join
                  sys.dm_db_missing_index_groups mig
                        on mid.index_handle = mig.index_handle          join
                  sys.dm_db_missing_index_group_stats migs
                        on migs.group_handle = mig.index_group_handle join
                        sys.objects obj on obj.object_id = mid.object_id join
                        sys.schemas sch on obj.schema_id = sch.schema_id
                       
       where db_name(database_id) = 'WSS_COntent_COmDoc_Intranet'
                                 
                             and            avg_user_impact > 20
                             and equality_columns is not null

      order by obj.name, equality_columns

--       avg_user_impact desc

No comments:

Post a Comment