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_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，那就說明該索引一 直在更新，
但是從來不被使用，僅僅創建和修改，沒有為查詢提供任何幫助，就可以考慮刪除了
查看本欄目
