当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQL语句实现查询Index使用状况

SQL语句实现查询Index使用状况

2017年12月12日  | 移动技术网IT编程  | 我要评论

第一放映厅,国际妈咪奶粉代购,c语言开发

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]

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网