优化索引

SELECT t4.name,t1.[statement],t1.object_id, t2.user_seeks, t2.user_scans, t1.equality_columns, t1.inequality_columns,t1.included_columns, case --when t1.equality_columns is null and charindex(',',t1.inequality_columns)=0 and t1.included_columns is null -- then 'create UNIQUE NONCLUSTERED INDEX IX_' + replace((replace((replace(t1.[statement],'[','_')),']','_')),'.','_') +'_'+ replace((replace((replace(isnull(t1.equality_columns,'1'),'[','_')),']','_')),'.','_') +'_' -- +replace((replace((replace(isnull(t1.inequality_columns,'_2'),'[','_')),']','_')),'.','_') + ' ON '+ t1.[statement] + ' (' + t1.inequality_columns + ' ASC )' when --t1.equality_columns is null and charindex(',',t1.inequality_columns)>0 and t1.included_columns is null then 'create NONCLUSTERED INDEX IX_' + replace((replace((replace((replace(t1.[statement],'[','_')),']','_')),'.','_')),',','_') +'_' +replace(replace(replace(replace(replace(isnull(t1.equality_columns,'2'),' [',''),'[',''),'.',''),',',''),']','') +replace((replace((replace((replace(isnull(t1.inequality_columns,'2'),'[','')),']','')),'.','')),',','_') + ' ON '+ t1.[statement] + ' (' + case when t1.equality_columns is null then ' ' when charindex(',',t1.equality_columns)=0 then t1.equality_columns +' ASC ' when charindex(',',t1.equality_columns)>0 then replace(t1.equality_columns,',',' ASC,') + ' ASC ' end + case when t1.equality_columns is not null and charindex(',',t1.inequality_columns)=0 then ' ,'+t1.inequality_columns + ' ASC )' when t1.equality_columns is null and charindex(',',t1.inequality_columns)=0 then ' '+t1.inequality_columns + ' ASC )' when t1.inequality_columns is null then ' )' when charindex(',',t1.inequality_columns) > 0 then ' ,'+ replace(t1.inequality_columns,',',' ASC,') + ' ASC )' when t1.equality_columns is null and charindex(',',t1.inequality_columns) > 0 then ' '+ replace(t1.inequality_columns,',',' ASC,') + ' ASC )' end when t1.included_columns is not null then 'create NONCLUSTERED INDEX IX_' + replace((replace((replace((replace(t1.[statement],'[','_')),']','_')),'.','_')),',','_') +'_' +replace(replace(replace(replace(replace(isnull(t1.equality_columns,'2'),' [',''),'[',''),'.',''),',',''),']','') +replace((replace((replace((replace(replace(isnull(t1.inequality_columns,'2'),' [',''),'[','')),']','')),'.','')),',','_') + ' ON '+ t1.[statement] + ' (' + case when t1.equality_columns is null then ' ' when charindex(',',t1.equality_columns) = 0 then t1.equality_columns +' ASC ' when charindex(',',t1.equality_columns) > 0 then replace(t1.equality_columns,',',' ASC,') + ' ASC ' end + case when t1.equality_columns is not null and charindex(',',t1.inequality_columns)=0 then ' ,'+t1.inequality_columns + ' ASC )' when t1.equality_columns is null and charindex(',',t1.inequality_columns)=0 then ' '+t1.inequality_columns + ' ASC )' when t1.inequality_columns is null then ' )' when t1.equality_columns is not null and charindex(',',t1.inequality_columns) > 0 then ' ,'+ replace(t1.inequality_columns,',',' ASC,') + ' ASC )' when t1.equality_columns is null and charindex(',',t1.inequality_columns) > 0 then ' '+ replace(t1.inequality_columns,',',' ASC,') + ' ASC )' end + ' INCLUDE ( ' + t1.included_columns + ' )' end as '建立索引的语句' FROM sys.dm_db_missing_index_groups AS t3 join sys.dm_db_missing_index_details AS t1 on t1.index_handle = t3.index_handle join sys.dm_db_missing_index_group_stats AS t2 on t2.group_handle = t3.index_group_handle join sys.databases AS t4 on t1.database_id = t4.database_id WHERE t1.database_id = DB_ID() --AND object_id = OBJECT_ID('interface.商户设备表') order by t2.user_seeks desc