摘要:Create missing index
以SQL server的missing index dynamic view，組出Create index語法
SELECT top 100 '
use ' + db_name(database_id) +'
create index idx_' + OBJECT_NAME(ddmid.object_id,database_id) + '_' + CONVERT(varchar(10),ddmid.index_handle) + '
on ' + OBJECT_SCHEMA_NAME(ddmid.object_id,database_id) + '.' + OBJECT_NAME(ddmid.object_id,database_id) +
'(' + ISNULL(equality_columns,inequality_columns) + ')' + isnull('
include (' + included_columns + ')
','') StatementForMissingIndex
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
FROM sys.dm_db_missing_index_group_stats ddmigs
join sys.dm_db_missing_index_groups ddmig on ddmigs.group_handle=ddmig.index_group_handle
join sys.dm_db_missing_index_details ddmid on ddmig.index_handle=ddmid.index_handle
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
