/*
avg_total_user_cost:减少的用户查询的平均成本
avg_user_impact:户查询可能获得的平均百分比收益
user_scans:扫描次数
user_seeks:查找次数。
equality_columns: 哪个字段缺失了索引(where 后面的筛选字段)
included_columns:查询的涵盖列(简单来讲就是 select 后面的字段)。
*/
SELECT avg_user_impact,
avg_total_user_cost,
avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,
last_user_seek ,
last_user_scan ,
[statement] AS [Object] ,
'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'
+ CONVERT(VARCHAR(32), D.index_handle) + '_'
+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
+ included_columns
+ ')', '') AS Create_Index_Syntax
FROM sys.dm_db_missing_index_groups AS G
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
ORDER BY PossibleImprovement DESC
avg_total_user_cost:减少的用户查询的平均成本
avg_user_impact:户查询可能获得的平均百分比收益
user_scans:扫描次数
user_seeks:查找次数。
equality_columns: 哪个字段缺失了索引(where 后面的筛选字段)
included_columns:查询的涵盖列(简单来讲就是 select 后面的字段)。
*/
SELECT avg_user_impact,
avg_total_user_cost,
avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,
last_user_seek ,
last_user_scan ,
[statement] AS [Object] ,
'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'
+ CONVERT(VARCHAR(32), D.index_handle) + '_'
+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
+ included_columns
+ ')', '') AS Create_Index_Syntax
FROM sys.dm_db_missing_index_groups AS G
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
ORDER BY PossibleImprovement DESC