sqlserver2008 R2中查找未使用过的索引

概述 sqlserver2008 R2中查找未使用过的索引：  SELECT TOP 1000 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_upda  sqlserver2008 R2中查找未使用过的索引： 

SELECT top 1000
o.@R_502_6889@ AS 表名
,i.@R_502_6889@ 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 ' + QUOTE@R_502_6889@(i.@R_502_6889@)
+ ' ON ' + QUOTE@R_502_6889@(s.@R_502_6889@) + '.' + QUOTE@R_502_6889@(OBJECT_@R_502_6889@(dm_ius.OBJECT_ID)) AS '删除语句'
FROM sys.dm_db_index_usage_stats dm_ius
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.@R_502_6889@='tblorders'   --根据实际修改表名
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

 

--结果处理：

user_updates很大，而发现user_seeks和user_scans很少或者就是0，那就说明该索引一直在更新， 但是从来不被使用，仅仅创建和修改，没有为查询提供任何帮助，就可以考虑删除了

总结

以上是内存溢出为你收集整理的sqlserver2008 R2中查找未使用过的索引全部内容，希望文章能够帮你解决sqlserver2008 R2中查找未使用过的索引所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。