sql server 2008 R2中查找未使用过的索引 - MSSQL教程

o.name AS 表名

, i.name AS 索引名

, i.index_id AS 索引id

, dm_ius.user_seeks AS 搜索次数

, dm_ius.user_scans AS 扫描次数

, dm_ius.user_lookups AS 查找次数

, dm_ius.user_updates AS 更新次数

, p.TableRows as 表行数

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

+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS '删除语句'

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'   --根据实际修改表名

ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

--结果 处理：

user_updates很大，而发现user_seeks和user_scans很少或者就是0，那就说明该索引一 直在更新，

但是从来不被使用，仅仅创建和修改，没有为查询提供任何帮助，就可以考虑删除了

查看本栏目更多精彩内容：http://www.bianceng.cn/database/SQLServer/

（编辑：应用网_镇江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!