待建立的索引及要删除的索引

 

 

1，查询待建立的索引。sys.dm_db_missing_index_groups,sql如下：

SELECT * FROM (
	SELECT TOP 50
	ROUND(s.avg_total_user_cost * (s.avg_user_impact/100) *
	(s.user_seeks + s.user_scans),0) AS [improvement_measure]
	, s.avg_user_impact
	, d.statement AS TableName
	, d.equality_columns
	, d.inequality_columns
	, d.included_columns,
	  'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
		  + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
		  + ' ON ' + d.statement
		  + ' (' + ISNULL (d.equality_columns,'')
			+ CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
			+ ISNULL (d.inequality_columns, '')
		  + ')'
		  + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
	FROM sys.dm_db_missing_index_groups g
	INNER JOIN sys.dm_db_missing_index_group_stats s
	ON s.group_handle = g.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details d
	ON d.index_handle = g.index_handle
	ORDER BY improvement_measure DESC)M
WHERE m.improvement_measure > 10000 --大于10000者需要重建

2，注：1)，avg_user_impact，用户查询可能获得的平均百分比收益，因是数字，故要除以100，以取得可获取的提高性能的倍数。2)，此方法有局限性，指定的建立索引列的并不都是正确的，要手工判定。

3，查询可删除的索引，很少使用。

Select Top 30 database_id, t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) ,t.name,ix.name,sc.name
    from sys.dm_db_index_usage_stats ius
    JOIN sys.tables t ON ius.object_id = t.object_id
    JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
    JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
    JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
    where user_updates > 10 * (user_seeks+user_scans)
    and ius.index_id > 1
    AND CHARINDEX('merge',t.name) <1
    order by user_updates / (user_seeks+user_scans+1) DESC