查询Index使用状况SQL

SELECT

      sch.name + '.' + t.name AS [Table Name],

      i.name AS[Index Name],

      i.type_desc, 

      ISNULL(user_updates,0) AS [Total Writes],

      ISNULL(user_seeks +user_scans + user_lookups,0) AS [Total Reads],

      s.last_user_seek,

      s.last_user_scan ,

      s.last_user_lookup,

      ISNULL(user_updates,0) - ISNULL((user_seeks+ user_scans +user_lookups),0)AS [Difference], 

      p.reserved_page_count * 8.0 / 1024 as SpaceInMB

FROM sys.indexes AS i WITH (NOLOCK) 

      LEFT OUTERJOIN sys.dm_db_index_usage_statsAS s      WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,'IsUserTable') = 1 

      INNER JOIN        sys.tables                          AS t  WITH (NOLOCK) ON i.object_id = t.object_id 

      INNER JOIN        sys.schemas                         AS sch      WITH (NOLOCK) ON t.schema_id = sch.schema_id 

      LEFT OUTERJOIN sys.dm_db_partition_stats AS p  WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id

WHERE (1=1)

      --AND ISNULL(user_updates,0) >=ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexesincluding those that have not been used 

      --AND ISNULL(user_updates,0) -ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows thoseindexes which have been used 

      --AND i.index_id > 1                -- Only non-first indexes (I.E.non-primary key)

      --AND i.is_primary_key<>1          -- Only those that are not defined asa Primary Key)

      --AND i.is_unique_constraint<>1-- Only those that are not classed as "UniqueConstraints". 

ORDER BY [Table Name],[index name]