欢迎来到代码驿站!

MsSql

当前位置:首页 > 数据库 > MsSql

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

时间:2022-11-25 10:40:58|栏目:MsSql|点击:
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]

上一篇:SQL Server使用T-SQL进阶之公用表表达式(CTE)

栏    目:MsSql

下一篇:SQL Server查询某个字段在哪些表中存在

本文标题:SQL语句实现查询Index使用状况

本文地址:http://www.codeinn.net/misctech/219861.html

推荐教程

广告投放 | 联系我们 | 版权申明

重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

联系QQ:914707363 | 邮箱:codeinn#126.com(#换成@)

Copyright © 2020 代码驿站 版权所有