内容简介:这篇文章主要介绍了SQL语句实现查询Index使用状况,本文直接给出SQL脚本代码,需要的朋友可以参考下
这篇文章主要介绍了SQL语句实现查询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]
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 敏捷教练市场发展状况
- 检查Unix系统的负载状况
- 使用jconsole监控JVM运行状况
- Nginx http运行状况健康检查配置
- 报告:后疫情时期产业区块链发展状况
- 网络安全状况月度报告-2019年3月
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。