Find unused indexes in SQL Server 2008 R2

SELECT Top 1000

O.name as table name

, I.name as index name

, i.index_id as index ID

, dm_ius.user_seeks as Search times

, Dm_ius.user_scans as scan times

, dm_ius.user_lookups as lookup times

, dm_ius.user_updates as update times

, the number of rows p.tablerows as table

, ' DROP INDEX ' + QuoteName (i.name)

+ ' on ' + QuoteName (s.name) + '. ' + QuoteName (object_name Dm_ius. object_id)) as ' DELETE statement '

From Sys.dm_db_index_usage_stats Dm_iusatch

INNER JOIN sys.indexes i on i.index_id = dm_ius.index_id and Dm_ius. object_id = i.object_id

INNER JOIN sys.objects o on Dm_ius. object_id = o.object_id

INNER JOIN sys.schemas s on o.schema_id = s.schema_id

INNER JOIN (SELECT SUM (p.rows) tablerows, p.index_id, p.object_id

From Sys.partitions p GROUP by p.index_id, p.object_id) p

On p.index_id = dm_ius.index_id and Dm_ius. object_id = p.object_id

WHERE objectproperty (Dm_ius. object_id, ' isusertable ') = 1

and dm_ius.database_id = db_id ()

and I.type_desc = ' nonclustered '

and I.is_primary_key = 0

and I.is_unique_constraint = 0

and O.name= ' tblorders '--based on actual modification of the table name

Order BY (Dm_ius.user_seeks + Dm_ius.user_scans + dm_ius.user_lookups) ASC

--Result Processing:

User_updates is very large, and finding User_seeks and User_scans is little or 0, that means the index has been updated,

But never used, just create and modify, do not provide any help for the query, you can consider deleting the

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/