索引优化

CREATE proc [dbo].[sp_index] @table varchar(200)=''asdeclare @database varchar(200)=DB_NAME()declare @dbid int= db_id()declare @sql nvarchar(max)=''set @sql='select top 50 objectname=object_name(s.object_id), s.object_id, 索引名=i.name, i.index_id, user_seeks as 搜索数, user_scans 扫描数, user_lookups 书签查找数, user_updates 更新数,s.last_user_seek , s.last_user_scan , s.last_user_lookup from sys.dm_db_index_usage_stats s,sys.indexes iwhere database_id = '+cast(@dbid as varchar)+' and objectproperty(s.object_id,''IsUserTable'') = 1 and i.object_id = s.object_idand i.index_id = s.index_id'if len(@table)>0 set @sql += ' AND object_name(s.object_id)='''+@table +''''set @sql += ' order by (user_seeks + user_scans + user_lookups + user_updates) asc 'set @sql += ' SELECT TOP 10 [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),user_seeks , user_scans, avg_user_impact, TableName = statement, [相等谓词列] = equality_columns, [非相等谓词列] = inequality_columns, [包含列] = included_columns,last_user_seek , last_user_scan  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_handleINNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle'if len(@table)>0 set @sql += ' WHERE statement = ''['+@database+'].[dbo].['+@table+']'''elseset @sql += ' WHERE statement like ''[[]'+@database+'].[[]dbo].[[]%]''' set @sql += ' ORDER BY [Total Cost] DESC 'exec(@sql)GO