【SqlServer】统计索引使用情况解决DB的CPU高和IO高的问题

zhangyucha0 发布于2019-07-01 16:08 / 1728人阅读

摘要：但是堆不会在索引重建或重新组织期间被重新生成，所以会脱离控制的增长，占用的数据页比必要的多很多。使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询，因而使用的操作更少，从而提高性能。

查看索引情况

sp_helpindex 表名;

显示索引使用情况

user_seeks和user_scans字段都为0的，考虑是否为垃圾索引

另外last_user_seek,last_user_scan如果是一个很早的时间，则考虑是否应用变化
导致该索引不被使用了

SELECT i.name indexname,user_seeks,user_scans,last_user_seek,last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON
s.object_id = i.object_id AND
s.index_id = i.index_id
WHERE database_id = db_id("ClntMgr") AND s.object_id = object_id("IDVerifyTbl");

返回指定数据库、表、索引的碎片

对于索引类型为HEAP，一般情况下碎片比例会较大
原因：

1.没有聚集索引的表称为堆，意思是其中存储的数据没有特定的顺序。

2.在索引重建或者重新组织时，聚集索引依照聚集键和它重排序的数据页进行排序。

3.但是堆不会在索引重建或重新组织期间被重新生成，所以会脱离控制的增长，占用的数据页比必要的多很多。

SELECT OBJECT_NAME(f.object_id) 表名,
i.name 索引名,f.index_type_desc 索引类型, f.avg_fragmentation_in_percent 碎片比例
FROM sys.dm_db_index_physical_stats(DB_ID("库名"),OBJECT_ID("表名"),NULL,NULL,"limited") f
INNER JOIN sys.indexes i ON
i.[object_id] = f.object_id AND
i.index_id  = f.index_id
ORDER BY f.avg_fragmentation_in_percent DESC;

在线重新生成表的所有索引

ALTER INDEX ALL ON 库名.dbo.表名 REBUILD WITH (ONLINE = ON);

重新组织表的所有索引

ALTER INDEX all ON 库名.dbo.表名 REORGANIZE;

查看表、索引占用磁盘空间情况

SELECT name "表名",
convert (char(11), row_Count) as "数据条数",
(reservedpages * 8) "已用空间(KB)",
(pages * 8) "数据占用空间(KB)",
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 "索引占用空间(KB)",
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 "未用空间(KB)",
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + " GB") as "已用空间(GB)"
from(
SELECT name,SUM (reserved_page_count) as reservedpages ,
SUM (used_page_count) as usedpages ,
SUM (
    CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE lob_used_page_count + row_overflow_used_page_count
    END
    ) as pages,
SUM (
    CASE
        WHEN (index_id < 2) THEN row_count
        ELSE 0
    END
    )  as row_Count
FROM sys.dm_db_partition_stats
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
where type="U"
group by sys.objects.name
union
SELECT sys.objects.name,
sum(reserved_page_count) as reservedpages,
sum(used_page_count) as usedpages,
0 as pages,
0 as row_count
from sys.objects inner join sys.internal_tables on 
 sys.objects.object_id = sys.internal_tables.parent_id 
inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id
where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216) 
group by sys.objects.name) t
order by "已用空间(KB)" desc

查看表缺失的索引信息

SELECT DatabaseName = DB_NAME(database_id),[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Number Indexes Missing] DESC;

确定开销最高的缺失索引

column_usage的取值有如下几种情况:

1.EqualityUsage代表在该列上做了相等运算;

2.InequalityUsage代表在该列上做了不等运算;

3.Include Cloumns代表包含性列

此查询的结果（按"总开销"排序）显示最重要缺失索引的成本以及有关数据库/架构/表和缺失索引中所需列的信息。特别是，此脚本可确定哪些列在相等和不相等 SQL 语句中使用。另外，它还报告应将哪些其他列用作缺失索引中的包含性列。

使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询，因而使用的 I/O 操作更少，从而提高性能。

SELECT TOP 100
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

云服务器超融合服务器使用情况统计使用情况数据统计使用情况统计数据手机使用情况统计数据

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/32345.html

记一次MongoDB高负载的性能优化

摘要：年月日本文是关于记录某次游戏服务端的性能优化此处涉及的技术包括引擎随着游戏导入人数逐渐增加单个集合的文档数已经超过经常有玩家反馈说卡特别是在服务器迁移后从核降到核卡顿更严重了遂开始排查问题确认服务器压力首先使用命令查看总体情况此时占用不高 Last-Modified: 2019年6月13日11:08:19 本文是关于记录某次游戏服务端的性能优化, 此处涉及的技术包括: MongoDB...

huhud 2019-07-01 13:57 评论0 收藏0
记一次MongoDB高负载的性能优化

摘要：年月日本文是关于记录某次游戏服务端的性能优化此处涉及的技术包括引擎随着游戏导入人数逐渐增加单个集合的文档数已经超过经常有玩家反馈说卡特别是在服务器迁移后从核降到核卡顿更严重了遂开始排查问题确认服务器压力首先使用命令查看总体情况此时占用不高 Last-Modified: 2019年6月13日11:08:19 本文是关于记录某次游戏服务端的性能优化, 此处涉及的技术包括: MongoDB...

vibiu 2019-06-26 18:05 评论0 收藏0
数据库面试题

摘要：不过这里的时间指的是系统版本号死锁数据库的解释现象两个或两个以上事务在同一资源相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。并发控制解决问题我在读数据，你在删数据的情况锁分类读锁共享锁，不阻塞写锁排他锁，排除其他写锁和读锁。数据库面试题 showImg(https://s2.ax1x.com/2019/01/11/FXc580.md.jpg); DBS DBMS DB区...

tabalt 2019-08-16 14:21 评论0 收藏0
性能优化

摘要：如果有人负责把控从相对长远些的角度设计系统的迭代，这种情况本是可以避免的优化办法只有一个就是保留主链路，旁支链路异步化。这会导致一次被动读磁盘，性能损耗会很大。在并发量一定的情况下如何对系统响应时间进行详细分析分析步骤1.1 在关键点位添加日志信息 -> 缩小目标范围 a) 主要函数耗时 b) 访问外部系统耗时：DB、MQ、Cache、FileSystem、RPC、HTTP等 c)...

ideaa 2019-08-06 13:41 评论0 收藏0
性能优化

摘要：如果有人负责把控从相对长远些的角度设计系统的迭代，这种情况本是可以避免的优化办法只有一个就是保留主链路，旁支链路异步化。这会导致一次被动读磁盘，性能损耗会很大。在并发量一定的情况下如何对系统响应时间进行详细分析分析步骤1.1 在关键点位添加日志信息 -> 缩小目标范围 a) 主要函数耗时 b) 访问外部系统耗时：DB、MQ、Cache、FileSystem、RPC、HTTP等 c)...

legendmohe 2019-08-08 10:51 评论0 收藏0