1 ;WITH T AS (
2 SELECT
3 DB_NAME(a.database_id) DBNAME,
4 SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11) TableName,
5 avg_user_impact,
6 avg_total_user_cost,
7 'use ['+DB_NAME(a.database_id)+'];if not exists(select 1 from sysindexes where name=''ix_' + SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11) +
8 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(equality_columns,'[','_'),']',''),',',''),' ',''), '') +
9 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(inequality_columns,'[','_'),']',''),',',''),' ',''), '') + ''')CREATE INDEX [ix_' + SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11) +
10 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(equality_columns,'[','_'),']',''),',',''),' ',''), '') +
11 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(inequality_columns,'[','_'),']',''),',',''),' ',''), '') + '] ON ' + SUBSTRING([statement],LEN(DB_NAME(a.database_id))+4,LEN([statement])-LEN(DB_NAME(a.database_id))-3) +
12 ' (' + ISNULL(equality_columns, ' ') + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ',' ELSE '' end + ISNULL(inequality_columns, ' ') + ')' +
13 ISNULL(' INCLUDE (' + included_columns + ')', '') +';'
14 AS MissIndex
15 ,'use ['+DB_NAME(a.database_id)+'];if exists(select 1 from sysindexes where name=''ix_' + SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11) +
16 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(equality_columns,'[','_'),']',''),',',''),' ',''), '') +
17 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(inequality_columns,'[','_'),']',''),',',''),' ',''), '') + ''')drop index [ix_' + SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11) +
18 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(equality_columns,'[','_'),']',''),',',''),' ',''), '') +
19 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(inequality_columns,'[','_'),']',''),',',''),' ',''), '') + '] on ['+SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11)+'];' DropIndex
20 ,'ix_' + SUBSTRING([statement],LEN(DB_NAME(a.database_id))+11,LEN([statement])-LEN(DB_NAME(a.database_id))-11) +
21 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(equality_columns,'[','_'),']',''),',',''),' ',''), '') +
22 ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(inequality_columns,'[','_'),']',''),',',''),' ',''), '') MissIndexName
23 FROM sys.dm_db_missing_index_details a INNER JOIN
24 sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
25 INNER JOIN sys.dm_db_missing_index_group_stats c ON
26 b.index_group_handle = c.group_handle
27 WHERE avg_user_impact >= 60 --在执行计划中的占比
28 )
29 SELECT
30 t1.DBNAME [数据库名称]
31 ,t1.TableName [表名]
32 ,t1.MissIndex [缺失的索引的创建]
33 ,t1.avg_user_impact AS [实现此缺失索引组后，用户查询可能获得的平均百分比收益。 该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。]
34 ,t1.avg_total_user_cost AS [实现此缺失索引组后，减少的用户查询的平均成本]
35 ,t1.DropIndex [缺失索引的删除]
36 ,t1.MissIndexName [缺失索引名称]
37 FROM T t1
38 WHERE 1=1
39 AND NOT EXISTS(SELECT 1 FROM T t2 WHERE t1.MissIndexName!=t2.MissIndexName and SUBSTRING(t2.MissIndexName,1,LEN(t1.MissIndexName))=t1.MissIndexName)
40 AND t1.MissIndexName NOT IN (SELECT name from sys.indexes WHERE object_id IN (SELECT object_id FROM sys.tables WHERE type='U') AND type=2)
41 and t1.DBNAME in (Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid))
42 ORDER BY t1.DBNAME ASC,t1.avg_user_impact DESC,t1.TableName ASC
43 go
