这篇文章将为大家详细讲解有关SQL语句中怎么查询Index使用状况，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
成都创新互联服务项目包括伊吾网站建设、伊吾网站制作、伊吾网页制作以及伊吾网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，伊吾网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到伊吾省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
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 SpaceInMBFROM 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_idWHERE (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语句中怎么查询Index使用状况就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
