--==========================================
--查看索引的使用情况
--索引在重建或删除新建时sys.dm_db_index_usage_stats中相关的数据会被清除
--索引在重整是不会清除sys.dm_db_index_usage_stats的数据
SELECT DB_NAME(ixu.database_id) DataBase_Name ,
OBJECT_NAME(ixu.object_id) Table_Name ,
ix.name Index_Name ,
( SELECT MAX(s.rows)
FROM sysindexes s
WHERE s.id = ixu.object_id
) AS Table_Rows ,
STATS_DATE(ixu.object_id, ixu.index_id) AS statistic_time ,
ixu.user_updates ,
ixu.last_user_update ,
ixu.last_user_seek ,
ixu.user_seeks ,
ixu.last_user_scan ,
ixu.user_scans ,
ixu.last_user_lookup ,
ixu.user_lookups ,
ixu.user_updates / ( ISNULL(ixu.user_seeks, 0) + ISNULL(ixu.user_scans,
0) + 1 ) AS UseRate
FROM sys.dm_db_index_usage_stats ixu
INNER JOIN sys.indexes ix
ON ixu.object_id = ix.object_id
AND ixu.index_id = ix.index_id
INNER JOIN sys.objects ob
ON ixu.object_id = ob.object_id
WHERE ob.type = 'U'
AND ob.is_ms_shipped = 0
AND ixu.database_id = DB_ID()
--AND ix.object_id=OBJECT_ID('TableName')
