The index to be built and the index to delete

1. Query the index you want to build. Sys.dm_db_missing_index_groups,sql as follows:

SELECT * FROM (select TOP 50ROUND (s.avg_total_user_cost * (s.avg_user_impact/100) * (S.user_seeks + S.user_scans), 0) as [IM Provement_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_hand Le) + ' _ ' + Left (ParseName (d.statement, 1), +) + ' + ' + ' + ' + d.statement + ' (' + ISNULL (d.equality_columns, ') + Case when D.equality_columns was not null and d.inequality_columns was not null then ', ' ELSE ' ' end+ ISNULL (d.inequality_c Olumns, ') + ') ' + ISNULL (' INCLUDE (' + d.included_columns + ') ', ') as Create_index_statementfrom sys.dm_db_missing _index_groups Ginner Join sys.dm_db_missing_index_group_stats SON s.group_handle = G.index_group_handleinner Join Sys.dm_db_missing_index_details DON d.index_handle = G.index_handleorder by improvement_measure DESC) MWHERE M.improvement_measure > 10000--More than 10000 need rebuilding

2, note: 1), avg_user_impact, the average percentage gain that the user queries may receive, because it is a number, it is divided by 100 to obtain a multiple of the improved performance that can be obtained. 2), this method has limitations, the specified index column is not all correct, to be determined by hand.

3, the query can be deleted index, rarely used.

Select Top 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_i D = ixc.index_id JOIN sys.columns sc on t.object_id = sc.object_id and ixc.column_id = sc.column_id where User_updat Es > * (user_seeks+user_scans) and ius.index_id > 1 and CHARINDEX (' merge ', t.name) <1 ORDER by user_up Dates/(user_seeks+user_scans+1) DESC

The index to be built and the index to delete